Postgre the constraint detailed

Data types are a way to limit what data we can store in the table. However, for many applications, this restriction is too coarse. For example, a field containing the price of a product should only accept positive numbers. However, none of the standard data types accept positive numbers only. Another problem is that you may need to constrain field data based on data from other fields or other rows. For example, in a table containing product information, each product number should have only one row.

For these issues, SQL allows you to define constraints on fields and tables. Constraints allow you to exert arbitrary control over your data. If the user attempts to store data that violates the constraint in the field, an error is thrown. This also applies to cases where the value is from the default value.

1. Check constraints

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

CREATE TABLE Products (    product_no integer,    name text,    CHECK (Price > 0));

As you can see, the constraint is defined after the data type, as if the default value is defined. Default values and constraints can be arranged in any order. A check constraint consists of a keyword check followed by an expression placed in parentheses. The check constraint expression should contain a constrained field, otherwise the constraint is meaningless.

You can also take a separate name for the constraint. This makes the error message clearer and references the name when you need to modify it. The syntax is:

CREATE TABLE Products (    product_no integer,    name text,    CONSTRAINT positive_price CHECK (Price > 0));

Therefore, to declare a naming constraint, use the keyword CONSTRAINT followed by an identifier (as the name), and then define it with the constraint. If you do not use this method to declare the constraint, then the system will automatically choose a name for you.

A check constraint can also refer to multiple fields. Suppose you store a normal price and a discounted price, and you want to guarantee 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 look familiar. The third one uses a new syntax. Instead of attaching to a field, it appears as a separate line in a comma-delimited list of fields. field definitions and constraint definitions can be listed in any order.

We call the first two constraints "field constraints", while the third constraint is "table constraint" (and the field definition is written separately). A field constraint can also be written as a table constraint, which in turn is probably not possible because the system assumes that the field constraint refers only to the field to which it is subordinate. PostgreSQL does not enforce this rule, but if you want your table definition to be compatible with other database systems, then you'd better follow this rule. The above example can also be written like this:

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 is:

CREATE TABLE Products (    product_no integer,    name text, price    numeric CHECK (Price > 0),    discounted_ Price numeric,    CHECK (Discounted_price > 0 and Price > Discounted_price));

It's just a different style.

As with field constraints, we can also assign names to table constraints in the same way:

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));

It is also important to note that when the constraint expression evaluates to True or NULL, the check constraint is considered to satisfy the condition. Because most expressions are null when they contain NULL operands, these constraints cannot prevent the field value from being null. To ensure that a field value is not NULL, you can use the non-null constraint that is described in the next section.

2. Non-null constraints

A non-null constraint simply declares that a field must not be NULL. Here is an example:

CREATE TABLE Products (not    null, not    null, price    numeric);

A non-null constraint is always written as a field constraint. A non-null constraint is functionally equivalent to creating a check constraint check (column_name is not NULL), but in PostgreSQL, creating a definite non-null constraint is more efficient. The downside is that you can't give it a definite name.

Of course, a field can have more than one constraint. As long as one after another to write it:

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

Their order is irrelevant. The order does not affect the order of constraint checking.

The NOT null constraint has an opposite constraint: anull constraint. It does not mean that the field must be empty, because such fields are useless. It simply defines the default behavior that the field can be empty. There is no NULL constraint defined in the SQL standard, so it should not be used in portable applications. adding this constraint to PostgreSQL is only for compatibility with other database systems. However, some users like it because this constraint allows them to easily switch constraints in the script file. For example, you can start with the following:

CREATE TABLE Products (    product_no integer null,    name text null, price    numeric null);

Then insert the NOT keyword when needed.

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

3. Unique constraints

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

CREATE TABLE Products (    UNIQUE,    name text, price    numeric);

The above is written as a field constraint, and the following is a table constraint:

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

If a unique constraint references a set of fields, the fields are listed by commas:

CREATE TABLE Example (    a integer,    b integer,    c integer,    UNIQUE (A, c));

This declares that a combination of specific field values is unique across the entire table range. However, a single value in these fields may not have to be (and is usually not) unique.

You can also give a unique constraint a name that you define, in the same way as before:

CREATE TABLE Products (    CONSTRAINT must_be_different UNIQUE,    name text, price    numeric);

Adding a unique constraint typically automatically creates a unique Btree index on the column or set of columns used in the constraint. You can force a unique constraint to be created on only some lines by creating a partial index.

In general, if the few fields contained in a unique constraint have multiple identical rows in a table, the unique constraint is violated. However, in this comparison, NULL is considered unequal. This means that, in the case of a multi-field unique constraint, if NULL is present on at least one field, then we can still store the same data row. This behavior follows the SQL standard, but we hear that other SQL databases may not follow this standard. So if you're going to develop portable programs, it's better to be careful.

4. Primary key

Technically, a PRIMARY KEY constraint is only a combination of a unique constraint and a non-null constraint. Therefore, the following two table definitions are equivalent:

CREATE TABLE Products (    product_no integer UNIQUE is NULL,    name text, price    numeric);

CREATE TABLE Products (    PRIMARY KEY,    name text, price    numeric);

A primary key can also constrain more than one field, and its syntax is similar to a unique constraint:

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

A primary key represents a combination of one or more fields that can be used to uniquely identify a row of data in a table. This is a direct result of defining a primary key. NOTE: A unique constraint does not actually provide a unique identity because it does not exclude NULL. This feature is useful for both document purposes and customer applications. For example, a GUI application that can modify row values might need to know the primary key of a table to uniquely identify each row.

Adding a primary key will automatically create a unique btree index in the column or set of columns used by the primary key.

A table can have a maximum of one primary key (but it can have multiple unique and non-null constraints). Relational database theory tells us that each table must have a primary key. PostgreSQL doesn't enforce this rule, but we'd better follow it.

5. Foreign key

A FOREIGN key constraint declares that the numeric value of a field (or a set of fields) must match the value that appears in the other table. We refer to this behavior as referential integrity between the two related tables.

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

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

Suppose you have a table that stores orders for these products. We want to make sure that the order form contains only the products that actually exist. So we define a foreign key constraint referencing the product table in the Order table:

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

Now, we cannot create any orders whose non-empty product_no records do not appear in the Product table.

In this case we call the order form the reference table , and the product table is called the quoted table . Similarly, there are reference fields and referenced fields.

You can also make the above command simple:

CREATE TABLE orders (    order_id integer PRIMARY KEY,    REFERENCESproducts,    quantity integer);

Because if the field list is missing, the primary key of the referenced table is referenced.

A foreign key can also constrain and reference a set of fields. Similarly, it needs to be written in the form of a table constraint. Here is an example of a fabricated syntax:

CREATE TABLE T1 (  a integer PRIMARY key,  b integer,  c integer,  FOREIGN key (b, c) REFERENCES Other_tab Le (C1, C2));

Of course, the number and type of fields being constrained need to be the same as the number and type of referenced fields.

As usual, you can also give a custom name to a FOREIGN key constraint.

A table can contain more than one foreign key constraint. This feature is used to implement a many-to-many relationship between tables. For example, you have a list of products and orders, but now you want to allow an order to contain a variety of products (the structure above is not allowed to do so), you can use such a 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 are not allowed to create orders that are unrelated to any product. But what if an order is created and its referenced product is deleted? SQL also allows you to handle this problem. To put it simply, we have several options:

• Not allowed to delete a referenced product

• Also delete orders

• The other?

To illustrate this, we have developed the following strategy for many-to-many relationships above: if someone wants to delete a product that is still referenced by an order (through order_items), then this is not allowed. If someone deletes an order, 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, Primar Y KEY (Product_no, order_id)); 

Restrictions and cascading deletions are the two most common options. RESTRICT Suppresses the deletion of referenced rows. The no action means that if there are any reference rows at the time of checking the constraint, an error is thrown, and if you do not declare anything, then it is the default behavior. The actual difference between the two choices is thatno ACTION allows the constraint check to be deferred until later in the transaction, and RESTRICT not. CASCADE declares that when a referenced row is deleted, all rows referencing it are automatically deleted. The action on the foreign key field also has two options: set null and set default, which causes the fields referencing them to be set to NULL and the default value when they are deleted by the referenced row. Please note that these options do not allow you to escape being observed and constrained. For example, if an action declares SET default, but the default value does not satisfy the FOREIGN key constraint, then the action fails.

Similar to on DELETE is the on update option, which is called when the referenced field is modified (updated), and the available actions are the same. In this case,CASCADE means that the updated value of the referenced field should be copied to the reference row.

Typically, if an arbitrary reference field of a reference row is null, then the reference row does not have to satisfy the foreign key constraint. If matchfull is added to the foreign key Declaration, the reference row can escape satisfying constraints only if all of the reference fields are null (so a mixture of NULL and non-null values must not satisfy the match full constraint). Declaring the reference behavior is not NULLif you do not want the reference row to be able to avoid the foreign key constraint being met.

A foreign key must either reference a primary key or reference a unique constraint. This means that the referenced row always has an index (a basic primary KEY or a unique constraint), so it is efficient to check whether a reference row has a match. Therefore, it is also a good idea to DELETE a row from the referenced table or a referenced field to UPDATE a column, to scan the reference table once to match the old value from the row, and to create an index to the reference field. Because this is not always needed, and how to build the index there are many other options, the declaration of a FOREIGN key constraint cannot automatically generate an index on the reference field.

6. Exclusion of constraints

An exclusive constraint guarantees that if any two rows are compared in a declared field or expressed in a declared operation, at least one action comparison returns an error or null value. The syntax is:

CREATE TABLE Circles (    C circle,    EXCLUDE USING Gist (c with &&));

Reference http://www.infocool.net/PostgreSQL/index.htm

Postgre the constraint detailed