This would cause a database dump and reload to fail. While a CHECK constraint that violates this rule may appear to work in simple tests, it cannot guarantee that the database will not reach a state in which the constraint condition is false (due to subsequent changes of the other row(s) involved). Note: PostgreSQL does not support CHECK constraints that reference table data other than the new or updated row being checked. To ensure that a column does not contain null values, the not-null constraint described in the next section can be used. Since most expressions will evaluate to the null value if any operand is null, they will not prevent null values in the constrained columns. It should be noted that a check constraint is satisfied if the check expression evaluates to true or the null value. Names can be assigned to table constraints in the same way as column constraints: CREATE TABLE products (ĬONSTRAINT valid_discount CHECK (price > discounted_price) ( PostgreSQL doesn't enforce that rule, but you should follow it if you want your table definitions to work with other database systems.) The above example could also be written as: CREATE TABLE products (ĬHECK (discounted_price > 0 AND price > discounted_price) Column constraints can also be written as table constraints, while the reverse is not necessarily possible, since a column constraint is supposed to refer to only the column it is attached to. We say that the first two constraints are column constraints, whereas the third one is a table constraint because it is written separately from any one column definition. Column definitions and these constraint definitions can be listed in mixed order. It is not attached to a particular column, instead it appears as a separate item in the comma-separated column list. The first two constraints should look familiar. Say you store a regular price and a discounted price, and you want to ensure that the discounted price is lower than the regular price: CREATE TABLE products (ĭiscounted_price numeric CHECK (discounted_price > 0), (If you don't specify a constraint name in this way, the system chooses a name for you.)Ī check constraint can also refer to several columns. So, to specify a named constraint, use the key word CONSTRAINT followed by an identifier followed by the constraint definition. Price numeric CONSTRAINT positive_price CHECK (price > 0) This clarifies error messages and allows you to refer to the constraint when you need to change it. You can also give the constraint a separate name. The check constraint expression should involve the column thus constrained, otherwise the constraint would not make too much sense. A check constraint consists of the key word CHECK followed by an expression in parentheses. Default values and constraints can be listed in any order. For instance, to require positive product prices, you could use: CREATE TABLE products (Īs you see, the constraint definition comes after the data type, just like default value definitions. It allows you to specify that the value in a certain column must satisfy a Boolean (truth-value) expression. When set to a positive value, ANALYZE will assume that the column contains exactly the specified number of distinct nonnull values.A check constraint is the most generic constraint type. n_distinct affects the statistics for the table itself, while n_distinct_inherited affects the statistics gathered for the table plus its inheritance children. Currently, the only defined per-attribute options are n_distinct and n_distinct_inherited, which override the number-of-distinct-values estimates made by subsequent ANALYZE operations. This form sets or resets per-attribute options. SET STATISTICS acquires a SHARE UPDATE EXCLUSIVE lock. For more information on the use of statistics by the PostgreSQL query planner, refer to Section 14.2. The target can be set in the range 0 to 10000 alternatively, set it to -1 to revert to using the system default statistics target ( default_statistics_target). This form sets the per-column statistics-gathering target for subsequent ANALYZE operations. sequence_option is an option supported by ALTER SEQUENCE such as INCREMENT BY. These forms alter the sequence that underlies an existing identity column. If DROP IDENTITY IF EXISTS is specified and the column is not an identity column, no error is thrown. Like SET DEFAULT, these forms only affect the behavior of subsequent INSERT and UPDATE commands they do not cause rows already in the table to change. These forms change whether a column is an identity column or change the generation attribute of an existing identity column. RENAME CONSTRAINT constraint_name TO new_constraint_nameĪLTER TABLE ALL IN TABLESPACE name ]ĪTTACH PARTITION partition_name AS IDENTITY
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |