PostgreSQL Flow Account (fourth day): DDL restriction constraint

CREATE TABLE Products (

Product_no Integer,

Name text,

Price numeric CONSTRAINT positive_price CHECK (Price > 0)

);

CHECK returns a bool value.

Foreign key referential integrity, referential integrity

A FOREIGN KEY constraint specifies that the values in a column (or a group of columns) must match the values appearing in Some row of another table. We say this maintains the? referential integritybetween, related tables.

CREATE TABLE Orders (

order_id integer PRIMARY KEY,

Product_no integer REFERENCES products (product_no),

Quantity integer

);

Here is an example of a loading force:

CREATE TABLE T1 (

A integer PRIMARY KEY,

b Integer,

C Integer,

FOREIGN KEY (b, c) REFERENCES other_table (c1, C2)

);

A table can has more than one foreign key constraint. This was used to implement many-to-many relationships between tables. Say You has tables about products and orders, but now you want to allow one order to contain possibly many products (whic h The structure above didn't allow). You could use this table structure:

A table can contain multiple foreign keys, which is typically used to implement a many-to-many relationship between tables. For example: an order can contain more than one product, and a class of products can belong to multiple order:

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)

);

?

When the two tables have a relationship, the two tables are subject to some restrictions, depending on how they are negotiated, for example:

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)

);

This product in the Products table is not allowed to be deleted when the items list has a product.

When an order of the Orders table is deleted, the records containing the order_id in the items list are automatically deleted.

restricting and cascading deletes is the most common options.?RESTRICT? Prevents deletion of a referenced row.?NO ACTION? means that if any referencing rows still exist when the constraint was checked, an error was raised; This is the Defaul T behavior if you does not specify anything. (The essential difference between these.NO ACTION? Allows the check to being deferred until later in the transaction, whereas?RESTRICT? does not.)?CASCADE? Specifies that if a referenced row is deleted, row (s) referencing it should be automatically deleted as well. There is the other options:?SET NULL? and?SET DEFAULT. These cause the referencing column (s) in the referencing row (s) to BES set to nulls or their default values, respectively, When the referenced row is deleted. Note that these does not excuse to observing any constraints. For example, what if an action specifies?SET DEFAULT? But the default value would not satisfy the foreign key constraint, the operation would fail.

analogous to? On DELETE ? There is also? On UPDATE ? which is invoked if a referenced column is changed (updated). The possible actions are the same. In this case,? CASCADE ? means that the updated values of the referenced column (s) should is copied into the referencing row (s).

Normally , a referencing row need not satisfy the FOREIGN KEY constraint if any of its referencing columns is null. If? match full ?is added to the foreign key declaration, a referencing row escapes Sati Sfying the constraint only if all its referencing columns be null (so a mix of null and Non-null values was guaranteed to Fail a? match full ?constraint). If you don ' t want referencing rows to being able to avoid satisfying the foreign KEY constraint, declare the referencing Colu MN (s) as? not NULL

a foreign key must reference columns that either is a primary key or form a unique constraint. This means, the referenced columns always has an index (the one underlying the primary key or UNIQUE constraint); So checks on whether a referencing row have a match would be efficient. Since a?DELETE? of a row from the referenced table or an?UPDATE? Of a referenced column would require a scan of the referencing table for rows matching the old value, it's often a go OD idea to index the referencing columns too. Because this isn't always needed, and there was many choices available on what to index, declaration of a foreign key cons Traint does not automatically create a index on the referencing columns.

More information about updating and deleting data are in? Chapter 6. Also See the description of FOREIGN KEY constraint syntax in the reference documentation for? CREATE TABLE.

?

PostgreSQL Flow Account (fourth day): DDL restriction constraint