Database definition-Constraints

Constraints

 

Data type is a method that limits the types of data that can be stored in the table. However, for many applications, the constraints they provide are too rough. For example, a field containing the product price may only accept positive numbers. But none of the standard data types only accept positive numbers. Another problem is that you may need to constrain field data based on the data of other fields or rows. For example, in a table containing product information, each product number should have only one row.

For these problems, SQL allows you to define constraints in fields and tables. Constraints give you all control over the data. If a user attempts to store data in a field that violates the constraints, an error is thrown. This condition also applies when the value comes from the default value.

 

1. Check Constraints

 

Check constraints are the most common constraints. It allows you to declare that the value in a field must satisfy a Boolean expression. For example, to force a positive product price, you can use:

CREATE TABLE products (    product_no integer,    name text,    price numeric CHECK (price > 0));

As you can see, the constraint is defined after the data type, just like the default value. Default values and constraints can be arranged in any order. A check constraint consists of a keywordCheckFollowed by an expression placed in the circular arc. Check that the constraint expression should contain restricted fields. Otherwise, this constraint is meaningless.

You can also give this constraint an independent name. In this way, the error information is clearer, and you can query this constraint when you want to modify it. Syntax:

CREATE TABLE products (    product_no integer,    name text,    price numeric CONSTRAINT positive_price CHECK (price > 0));

Therefore, to declare a naming constraint, use the keywordConstraintIt is followed by an identifier and then followed by the constraint definition. (If you do not need this method to declare constraints, the system selects a name for you .)

A check constraint can also reference several fields. Suppose you store a normal price and a discount price, and you want to ensure that the discount price is lower than the normal price.

CREATE TABLE products (    product_no integer,    name text,    price numeric CHECK (price > 0),    discounted_price numeric CHECK (discounted_price > 0),    CHECK (price > discounted_price));

The first two constraints should look familiar. Third, a new syntax is used. It is not attached to a field. It appears in a comma-separated field list in the form of an independent row. Field definitions and constraints can be listed in a mixed order.

The first two constraints are field constraints, and the third is table constraints, because they are written separately from any field definition. The field constraint can also be written as a table constraint, but in turn it may not work, because the system assumes that the field constraint only references the field it attaches. (PostgreSQL does not force this rule, but if you want your table definition to be compatible with other database systems, you 'd better follow this rule .) The preceding example can also be written in this way.

CREATE TABLE products (    product_no integer,    name text,    price numeric,    CHECK (price > 0),    discounted_price numeric,    CHECK (discounted_price > 0),    CHECK (price > discounted_price));

Or

CREATE TABLE products (    product_no integer,    name text,    price numeric CHECK (price > 0),    discounted_price numeric,    CHECK (discounted_price > 0 AND price > discounted_price));

This is just a different style.

Like field constraints, we can also assign names to table constraints and use the same method:

CREATE TABLE products (    product_no integer,    name text,    price numeric,    CHECK (price > 0),    discounted_price numeric,    CHECK (discounted_price > 0),    CONSTRAINT valid_discount CHECK (price > discounted_price));

We also need to know that a check constraint meets the condition when the expression calculates the true or null value. Because most expressions obtain null values when any operand is null, these constraints cannot be used to prohibit null values in the number field. To ensure that a field does not contain null values, we can use the non-null constraints described in the next section.

Check constraints can also be used to improve the performance of table partitions.

 

2. Non-empty Constraint

 

A non-null constraint simply declares that a field must not be null. The following is a syntax example:

CREATE TABLE products (    product_no integer NOT NULL,    name text NOT NULL,    price numeric);

A non-empty constraint is always written as a field constraint. A non-null constraint is equivalent to creating a check constraint.Check (Column_nameIs not null)But in PostgreSQL, creating a clear non-empty constraint is more efficient. The disadvantage is that you cannot give a clear name to the non-empty constraint.

Of course, a field can have multiple constraints. You just need to write it one by one:

CREATE TABLE products (    product_no integer NOT NULL,    name text NOT NULL,    price numeric NOT NULL CHECK (price > 0));

Its order does not matter. The Order does not affect the order of constraints.

Not nullThe constraints have the opposite constraints:NullConstraints. This constraint does not mean that the field must be empty, because such a field is useless. It only defines the default behavior that the field can be empty. Not Defined in SQL standardsNullConstraints, so it should not be used in portable applications. (We added this constraint to PostgreSQL to ensure compatibility with other database systems .) However, some users like it because it makes it easy for them to switch the constraint in the script file. For example, you can start from below

CREATE TABLE products (    product_no integer NULL,    name text NULL,    price numeric NULL);

InsertNotKeyword.

 

Tip:In most database designs, the main fields should be marked as non-empty.

 

 

3. unique constraints

 

The unique constraint ensures that the data in a field or a set of fields is unique compared to the data in other rows in the table. Its syntax is

CREATE TABLE products (    product_no integer UNIQUE,    name text,    price numeric);

The above is written as field constraints, the following

CREATE TABLE products (    product_no integer,    name text,    price numeric,    UNIQUE (product_no));

Is a write Table constraint.

If a unique constraint references a set of fields, these fields are separated by commas:

CREATE TABLE example (    a integer,    b integer,    c integer,    UNIQUE (a, c));

In this way, the combination of values of specified fields is declared and unique in the entire table range. However, a value in these fields does not need to be (and may not be) unique.

You can also assign a name defined by yourself to the unique constraint. The method is as follows:

CREATE TABLE products (    product_no integer CONSTRAINT must_be_different UNIQUE,    name text,    price numeric);

Generally, if two or more rows exist in the table, and these rows contain the same fields in the unique constraint, the unique constraint is violated. However, in this comparison, null values are considered unequal. This means that if there is a null value in at least one field under the unique constraint of multiple fields, we can still store the same data row. This behavior follows the SQL standard, but we have heard that other SQL databases may not follow this standard. So if you want to develop a portable program, you 'd better be careful.

 

4. Primary Key

 

Technically speaking, primary key constraints are only a combination of unique constraints and non-empty constraints. Therefore, the following two table definitions accept the same data:

CREATE TABLE products (    product_no integer UNIQUE NOT NULL,    name text,    price numeric);

CREATE TABLE products (    product_no integer PRIMARY KEY,    name text,    price numeric);

The primary key can also constrain more than one field. Its syntax is similar to the unique constraint:

CREATE TABLE example (    a integer,    b integer,    c integer,    PRIMARY KEY (a, c));

A primary key indicates that a field or a combination of several fields can be used as the unique identifier of a Data row in a table. (This is the direct result of defining a primary key. Note that a unique constraint does not actually provide a unique representation because it does not exclude null values .) This function is useful for document purposes and customer applications. For example, a GUI application that can modify row values may need to know the primary key of a table to uniquely identify a row.

A table can have at most one primary key (but it can have multiple unique and non-empty constraints ). The relational database theory tells us that each table must have a primary key. PostgreSQL does not force this rule, but we 'd better follow it.

 

5. Foreign key

 

The value of a field (or a set of fields) declared by the foreign key constraint must match the value of some rows in another table. We call this behavior between two related tables.Reference integrity.

Suppose you have a product table, we may have used it several times:

CREATE TABLE products (    product_no integer PRIMARY KEY,    name text,    price numeric);

Let's assume that you have a table that stores orders for these products. We want to ensure that the order table only contains products that actually exist. Therefore, we define a foreign key constraint in the order table to reference the product table:

CREATE TABLE orders (    order_id integer PRIMARY KEY,    product_no integer REFERENCES products (product_no),    quantity integer);

Now, we cannot create any of itsProduct_noThere are no orders in the product table.

In this case, we call the order tableReferenceTable, while the product table isReferencedTable. Similarly, there are referenced fields and referenced fields.

You can also abbreviated the above command

CREATE TABLE orders (    order_id integer PRIMARY KEY,    product_no integer REFERENCES products,    quantity integer);

If the field list is missing, the primary key of the referenced table will be used as the referenced field.

A foreign key can also constrain and reference a set of fields. Similarly, table constraints must be written. The following is an example of a fabricated Syntax:

CREATE TABLE t1 (  a integer PRIMARY KEY,  b integer,  c integer,  FOREIGN KEY (b, c) REFERENCES other_table (c1, c2));

Of course, the number and type of restricted fields must be the same as the number and type of referenced fields.

As usual, you can also give your own name to the foreign key constraint.

A table can contain more than one foreign key constraint. This feature is used to realize the many-to-many relationship between tables. For example, you have a table about products and orders, but now you want to allow an order to include multiple products (the structure above does not allow this ). You can use the following structure:

CREATE TABLE products (    product_no integer PRIMARY KEY,    name text,    price numeric);CREATE TABLE orders (    order_id integer PRIMARY KEY,    shipping_address text,    ...);CREATE TABLE order_items (    product_no integer REFERENCES products,    order_id integer REFERENCES orders,    quantity integer,    PRIMARY KEY (product_no, order_id));

Note that the primary key and foreign key of the last table overlap.

We know that foreign keys cannot create orders irrelevant to any products. But what if the referenced product is deleted after an order is created? SQL also allows you to handle this problem. To put it simply, we have several options:

• A referenced product cannot be deleted.

• Also delete the order

• Others?

To illustrate this problem, let's develop the following policy for the many-to-many relationship example: if someone wants to delete a product that is still referenced by an order (throughOrder_items), So we do not allow her to do this. If an order is deleted, the order item is also deleted.

CREATE TABLE products (    product_no integer PRIMARY KEY,    name text,    price numeric);CREATE TABLE orders (    order_id integer PRIMARY KEY,    shipping_address text,    ...);CREATE TABLE order_items (    product_no integer REFERENCES products ON DELETE RESTRICT,    order_id integer REFERENCES orders ON DELETE CASCADE,    quantity integer,    PRIMARY KEY (product_no, order_id));

Restriction and cascading deletion are two of the most common options.RestrictDeleting referenced rows is prohibited.No actionThis means that if any reference row exists while checking the constraints, an error is thrown. If you do not declare anything, it is the default action. (The actual difference between the two options is,No actionAllow constraints to be checked later than the transaction, whileRestrictNo .)CascadeWhen you delete a referenced row, the row that references it is automatically deleted. There are two options for the action on the foreign key field:Set nullAndSet Default. In this way, when the referenced rows are deleted, the fields that reference them are set to null or the default values. Note that these options do not allow you to escape observation and constraints. For example, if an action is declaredSet DefaultBut the default value does not meet the foreign key, the action will fail.

SimilarOn DeleteAndOn updateIt is called when the referenced field is modified (updated. The available actions are the same.