Postgresql流水帳(第四天): DDL 限制約束

標籤：

CREATE TABLE products (

product_no integer,

name text,

price numeric CONSTRAINT positive_price CHECK (price > 0)

);

CHECK 返回bool 值。

外鍵參照完整性、參考完整性

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 integrity?between two related tables.

CREATE TABLE orders (

order_id integer PRIMARY KEY,

product_no integer REFERENCES products (product_no),

quantity integer

);

下面是一個裝逼的例子：

CREATE TABLE t1 (

a integer PRIMARY KEY,

b integer,

c integer,

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

);

A table can have more than one foreign key constraint. This is used to implement many-to-many relationships between tables. Say you have tables about products and orders, but now you want to allow one order to contain possibly many products (which the structure above did not allow). You could use this table structure:

一個表裡可以包含多個外鍵，這通常用來實現表之間的多對多關係。例如：一個order 可以包含多個products, 一類product 可以屬於多個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)

);

?

當兩個表發生了關係後，這兩個表就會受到一些限制，具體要看他們是如何談妥的， 例如：

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)

);

當 items 表有 某product 時，products 表裡的這個product 是不允許被刪除的。

當 orders 表的某個 order 被刪除時， items 表裡含該 order_id 的記錄會被自動刪除。

Restricting and cascading deletes are the two most common options.?RESTRICT?prevents deletion of a referenced row.?NO ACTION?means that if any referencing rows still exist when the constraint is checked, an error is raised; this is the default behavior if you do not specify anything. (The essential difference between these two choices is that?NO ACTION?allows the check to be deferred until later in the transaction, whereas?RESTRICT?does not.)?CASCADE?specifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well. There are two other options:?SET NULL?and?SET DEFAULT. These cause the referencing column(s) in the referencing row(s) to be set to nulls or their default values, respectively, when the referenced row is deleted. Note that these do not excuse you from observing any constraints. For example, if an action specifies?SET DEFAULT?but the default value would not satisfy the foreign key constraint, the operation will fail.

Analogous to?ON DELETE?there is also?ON UPDATE?which is invoked when 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 be copied into the referencing row(s).

Normally, a referencing row need not satisfy the foreign key constraint if any of its referencing columns are null. If?MATCH FULL?is added to the foreign key declaration, a referencing row escapes satisfying the constraint only if all its referencing columns are null (so a mix of null and non-null values is guaranteed to fail a?MATCH FULL?constraint). If you don‘t want referencing rows to be able to avoid satisfying the foreign key constraint, declare the referencing column(s) as?NOT NULL.

A foreign key must reference columns that either are a primary key or form a unique constraint. This means that the referenced columns always have an index (the one underlying the primary key or unique constraint); so checks on whether a referencing row has a match will be efficient. Since a?DELETE?of a row from the referenced table or an?UPDATE?of a referenced column will require a scan of the referencing table for rows matching the old value, it is often a good idea to index the referencing columns too. Because this is not always needed, and there are many choices available on how to index, declaration of a foreign key constraint does not automatically create an index on the referencing columns.

More information about updating and deleting data is in?Chapter 6. Also see the description of foreign key constraint syntax in the reference documentation for?CREATE TABLE.

?

Postgresql流水帳(第四天): DDL 限制約束