Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes14kdownloads
ddl-constraints.html607 linesDownload Raw Back to html
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>5.4. Constraints</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="ddl-generated-columns.html" title="5.3. Generated Columns" /><link rel="next" href="ddl-system-columns.html" title="5.5. System Columns" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">5.4. Constraints</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="ddl-generated-columns.html" title="5.3. Generated Columns">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="ddl.html" title="Chapter 5. Data Definition">Up</a></td><th width="60%" align="center">Chapter 5. Data Definition</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="ddl-system-columns.html" title="5.5. System Columns">Next</a></td></tr></table><hr /></div><div class="sect1" id="DDL-CONSTRAINTS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">5.4. Constraints <a href="#DDL-CONSTRAINTS" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS">5.4.1. Check Constraints</a></span></dt><dt><span class="sect2"><a href="ddl-constraints.html#DDL-CONSTRAINTS-NOT-NULL">5.4.2. Not-Null Constraints</a></span></dt><dt><span class="sect2"><a href="ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS">5.4.3. Unique Constraints</a></span></dt><dt><span class="sect2"><a href="ddl-constraints.html#DDL-CONSTRAINTS-PRIMARY-KEYS">5.4.4. Primary Keys</a></span></dt><dt><span class="sect2"><a href="ddl-constraints.html#DDL-CONSTRAINTS-FK">5.4.5. Foreign Keys</a></span></dt><dt><span class="sect2"><a href="ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION">5.4.6. Exclusion Constraints</a></span></dt></dl></div><a id="id-1.5.4.6.2" class="indexterm"></a><p>3   Data types are a way to limit the kind of data that can be stored4   in a table.  For many applications, however, the constraint they5   provide is too coarse.  For example, a column containing a product6   price should probably only accept positive values.  But there is no7   standard data type that accepts only positive numbers.  Another issue is8   that you might want to constrain column data with respect to other9   columns or rows.  For example, in a table containing product10   information, there should be only one row for each product number.11  </p><p>12   To that end, SQL allows you to define constraints on columns and13   tables.  Constraints give you as much control over the data in your14   tables as you wish.  If a user attempts to store data in a column15   that would violate a constraint, an error is raised.  This applies16   even if the value came from the default value definition.17  </p><div class="sect2" id="DDL-CONSTRAINTS-CHECK-CONSTRAINTS"><div class="titlepage"><div><div><h3 class="title">5.4.1. Check Constraints <a href="#DDL-CONSTRAINTS-CHECK-CONSTRAINTS" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.6.5.2" class="indexterm"></a><a id="id-1.5.4.6.5.3" class="indexterm"></a><p>18    A check constraint is the most generic constraint type.  It allows19    you to specify that the value in a certain column must satisfy a20    Boolean (truth-value) expression.  For instance, to require positive21    product prices, you could use:22</p><pre class="programlisting">23CREATE TABLE products (24    product_no integer,25    name text,26    price numeric <span class="emphasis"><strong>CHECK (price &gt; 0)</strong></span>27);28</pre><p>29   </p><p>30    As you see, the constraint definition comes after the data type,31    just like default value definitions.  Default values and32    constraints can be listed in any order.  A check constraint33    consists of the key word <code class="literal">CHECK</code> followed by an34    expression in parentheses.  The check constraint expression should35    involve the column thus constrained, otherwise the constraint36    would not make too much sense.37   </p><a id="id-1.5.4.6.5.6" class="indexterm"></a><p>38    You can also give the constraint a separate name.  This clarifies39    error messages and allows you to refer to the constraint when you40    need to change it.  The syntax is:41</p><pre class="programlisting">42CREATE TABLE products (43    product_no integer,44    name text,45    price numeric <span class="emphasis"><strong>CONSTRAINT positive_price</strong></span> CHECK (price &gt; 0)46);47</pre><p>48    So, to specify a named constraint, use the key word49    <code class="literal">CONSTRAINT</code> followed by an identifier followed50    by the constraint definition.  (If you don't specify a constraint51    name in this way, the system chooses a name for you.)52   </p><p>53    A check constraint can also refer to several columns.  Say you54    store a regular price and a discounted price, and you want to55    ensure that the discounted price is lower than the regular price:56</p><pre class="programlisting">57CREATE TABLE products (58    product_no integer,59    name text,60    price numeric CHECK (price &gt; 0),61    discounted_price numeric CHECK (discounted_price &gt; 0),62    <span class="emphasis"><strong>CHECK (price &gt; discounted_price)</strong></span>63);64</pre><p>65   </p><p>66    The first two constraints should look familiar.  The third one67    uses a new syntax.  It is not attached to a particular column,68    instead it appears as a separate item in the comma-separated69    column list.  Column definitions and these constraint70    definitions can be listed in mixed order.71   </p><p>72    We say that the first two constraints are column constraints, whereas the73    third one is a table constraint because it is written separately74    from any one column definition.  Column constraints can also be75    written as table constraints, while the reverse is not necessarily76    possible, since a column constraint is supposed to refer to only the77    column it is attached to.  (<span class="productname">PostgreSQL</span> doesn't78    enforce that rule, but you should follow it if you want your table79    definitions to work with other database systems.)  The above example could80    also be written as:81</p><pre class="programlisting">82CREATE TABLE products (83    product_no integer,84    name text,85    price numeric,86    CHECK (price &gt; 0),87    discounted_price numeric,88    CHECK (discounted_price &gt; 0),89    CHECK (price &gt; discounted_price)90);91</pre><p>92    or even:93</p><pre class="programlisting">94CREATE TABLE products (95    product_no integer,96    name text,97    price numeric CHECK (price &gt; 0),98    discounted_price numeric,99    CHECK (discounted_price &gt; 0 AND price &gt; discounted_price)100);101</pre><p>102    It's a matter of taste.103   </p><p>104    Names can be assigned to table constraints in the same way as105    column constraints:106</p><pre class="programlisting">107CREATE TABLE products (108    product_no integer,109    name text,110    price numeric,111    CHECK (price &gt; 0),112    discounted_price numeric,113    CHECK (discounted_price &gt; 0),114    <span class="emphasis"><strong>CONSTRAINT valid_discount</strong></span> CHECK (price &gt; discounted_price)115);116</pre><p>117   </p><a id="id-1.5.4.6.5.12" class="indexterm"></a><p>118    It should be noted that a check constraint is satisfied if the119    check expression evaluates to true or the null value.  Since most120    expressions will evaluate to the null value if any operand is null,121    they will not prevent null values in the constrained columns.  To122    ensure that a column does not contain null values, the not-null123    constraint described in the next section can be used.124   </p><div class="note"><h3 class="title">Note</h3><p>125     <span class="productname">PostgreSQL</span> does not support126     <code class="literal">CHECK</code> constraints that reference table data other than127     the new or updated row being checked.  While a <code class="literal">CHECK</code>128     constraint that violates this rule may appear to work in simple129     tests, it cannot guarantee that the database will not reach a state130     in which the constraint condition is false (due to subsequent changes131     of the other row(s) involved).  This would cause a database dump and132     restore to fail.  The restore could fail even when the complete133     database state is consistent with the constraint, due to rows not134     being loaded in an order that will satisfy the constraint.  If135     possible, use <code class="literal">UNIQUE</code>, <code class="literal">EXCLUDE</code>,136     or <code class="literal">FOREIGN KEY</code> constraints to express137     cross-row and cross-table restrictions.138    </p><p>139     If what you desire is a one-time check against other rows at row140     insertion, rather than a continuously-maintained consistency141     guarantee, a custom <a class="link" href="triggers.html" title="Chapter 39. Triggers">trigger</a> can be used142     to implement that.  (This approach avoids the dump/restore problem because143     <span class="application">pg_dump</span> does not reinstall triggers until after144     restoring data, so that the check will not be enforced during a145     dump/restore.)146    </p></div><div class="note"><h3 class="title">Note</h3><p>147     <span class="productname">PostgreSQL</span> assumes that148     <code class="literal">CHECK</code> constraints' conditions are immutable, that149     is, they will always give the same result for the same input row.150     This assumption is what justifies examining <code class="literal">CHECK</code>151     constraints only when rows are inserted or updated, and not at other152     times.  (The warning above about not referencing other table data is153     really a special case of this restriction.)154    </p><p>155     An example of a common way to break this assumption is to reference a156     user-defined function in a <code class="literal">CHECK</code> expression, and157     then change the behavior of that158     function.  <span class="productname">PostgreSQL</span> does not disallow159     that, but it will not notice if there are rows in the table that now160     violate the <code class="literal">CHECK</code> constraint. That would cause a161     subsequent database dump and restore to fail.162     The recommended way to handle such a change is to drop the constraint163     (using <code class="command">ALTER TABLE</code>), adjust the function definition,164     and re-add the constraint, thereby rechecking it against all table rows.165    </p></div></div><div class="sect2" id="DDL-CONSTRAINTS-NOT-NULL"><div class="titlepage"><div><div><h3 class="title">5.4.2. Not-Null Constraints <a href="#DDL-CONSTRAINTS-NOT-NULL" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.6.6.2" class="indexterm"></a><a id="id-1.5.4.6.6.3" class="indexterm"></a><p>166    A not-null constraint simply specifies that a column must not167    assume the null value.  A syntax example:168</p><pre class="programlisting">169CREATE TABLE products (170    product_no integer <span class="emphasis"><strong>NOT NULL</strong></span>,171    name text <span class="emphasis"><strong>NOT NULL</strong></span>,172    price numeric173);174</pre><p>175   </p><p>176    A not-null constraint is always written as a column constraint.  A177    not-null constraint is functionally equivalent to creating a check178    constraint <code class="literal">CHECK (<em class="replaceable"><code>column_name</code></em>179    IS NOT NULL)</code>, but in180    <span class="productname">PostgreSQL</span> creating an explicit181    not-null constraint is more efficient.  The drawback is that you182    cannot give explicit names to not-null constraints created this183    way.184   </p><p>185    Of course, a column can have more than one constraint.  Just write186    the constraints one after another:187</p><pre class="programlisting">188CREATE TABLE products (189    product_no integer NOT NULL,190    name text NOT NULL,191    price numeric NOT NULL CHECK (price &gt; 0)192);193</pre><p>194    The order doesn't matter.  It does not necessarily determine in which195    order the constraints are checked.196   </p><p>197    The <code class="literal">NOT NULL</code> constraint has an inverse: the198    <code class="literal">NULL</code> constraint.  This does not mean that the199    column must be null, which would surely be useless.  Instead, this200    simply selects the default behavior that the column might be null.201    The <code class="literal">NULL</code> constraint is not present in the SQL202    standard and should not be used in portable applications.  (It was203    only added to <span class="productname">PostgreSQL</span> to be204    compatible with some other database systems.)  Some users, however,205    like it because it makes it easy to toggle the constraint in a206    script file.  For example, you could start with:207</p><pre class="programlisting">208CREATE TABLE products (209    product_no integer NULL,210    name text NULL,211    price numeric NULL212);213</pre><p>214    and then insert the <code class="literal">NOT</code> key word where desired.215   </p><div class="tip"><h3 class="title">Tip</h3><p>216     In most database designs the majority of columns should be marked217     not null.218    </p></div></div><div class="sect2" id="DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS"><div class="titlepage"><div><div><h3 class="title">5.4.3. Unique Constraints <a href="#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.6.7.2" class="indexterm"></a><a id="id-1.5.4.6.7.3" class="indexterm"></a><p>219    Unique constraints ensure that the data contained in a column, or a220    group of columns, is unique among all the rows in the221    table.  The syntax is:222</p><pre class="programlisting">223CREATE TABLE products (224    product_no integer <span class="emphasis"><strong>UNIQUE</strong></span>,225    name text,226    price numeric227);228</pre><p>229    when written as a column constraint, and:230</p><pre class="programlisting">231CREATE TABLE products (232    product_no integer,233    name text,234    price numeric,235    <span class="emphasis"><strong>UNIQUE (product_no)</strong></span>236);237</pre><p>238    when written as a table constraint.239   </p><p>240    To define a unique constraint for a group of columns, write it as a241    table constraint with the column names separated by commas:242</p><pre class="programlisting">243CREATE TABLE example (244    a integer,245    b integer,246    c integer,247    <span class="emphasis"><strong>UNIQUE (a, c)</strong></span>248);249</pre><p>250    This specifies that the combination of values in the indicated columns251    is unique across the whole table, though any one of the columns252    need not be (and ordinarily isn't) unique.253   </p><p>254    You can assign your own name for a unique constraint, in the usual way:255</p><pre class="programlisting">256CREATE TABLE products (257    product_no integer <span class="emphasis"><strong>CONSTRAINT must_be_different</strong></span> UNIQUE,258    name text,259    price numeric260);261</pre><p>262   </p><p>263    Adding a unique constraint will automatically create a unique B-tree264    index on the column or group of columns listed in the constraint.265    A uniqueness restriction covering only some rows cannot be written as266    a unique constraint, but it is possible to enforce such a restriction by267    creating a unique <a class="link" href="indexes-partial.html" title="11.8. Partial Indexes">partial index</a>.268   </p><a id="id-1.5.4.6.7.8" class="indexterm"></a><p>269    In general, a unique constraint is violated if there is more than270    one row in the table where the values of all of the271    columns included in the constraint are equal.272    By default, two null values are not considered equal in this273    comparison.  That means even in the presence of a274    unique constraint it is possible to store duplicate275    rows that contain a null value in at least one of the constrained276    columns.  This behavior can be changed by adding the clause <code class="literal">NULLS277    NOT DISTINCT</code>, like278</p><pre class="programlisting">279CREATE TABLE products (280    product_no integer UNIQUE <span class="emphasis"><strong>NULLS NOT DISTINCT</strong></span>,281    name text,282    price numeric283);284</pre><p>285    or286</p><pre class="programlisting">287CREATE TABLE products (288    product_no integer,289    name text,290    price numeric,291    UNIQUE <span class="emphasis"><strong>NULLS NOT DISTINCT</strong></span> (product_no)292);293</pre><p>294    The default behavior can be specified explicitly using <code class="literal">NULLS295    DISTINCT</code>.  The default null treatment in unique constraints is296    implementation-defined according to the SQL standard, and other297    implementations have a different behavior.  So be careful when developing298    applications that are intended to be portable.299   </p></div><div class="sect2" id="DDL-CONSTRAINTS-PRIMARY-KEYS"><div class="titlepage"><div><div><h3 class="title">5.4.4. Primary Keys <a href="#DDL-CONSTRAINTS-PRIMARY-KEYS" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.6.8.2" class="indexterm"></a><a id="id-1.5.4.6.8.3" class="indexterm"></a><p>300    A primary key constraint indicates that a column, or group of columns,301    can be used as a unique identifier for rows in the table.  This302    requires that the values be both unique and not null.  So, the following303    two table definitions accept the same data:304</p><pre class="programlisting">305CREATE TABLE products (306    product_no integer UNIQUE NOT NULL,307    name text,308    price numeric309);310</pre><p>311 312</p><pre class="programlisting">313CREATE TABLE products (314    product_no integer <span class="emphasis"><strong>PRIMARY KEY</strong></span>,315    name text,316    price numeric317);318</pre><p>319   </p><p>320    Primary keys can span more than one column; the syntax321    is similar to unique constraints:322</p><pre class="programlisting">323CREATE TABLE example (324    a integer,325    b integer,326    c integer,327    <span class="emphasis"><strong>PRIMARY KEY (a, c)</strong></span>328);329</pre><p>330   </p><p>331    Adding a primary key will automatically create a unique B-tree index332    on the column or group of columns listed in the primary key, and will333    force the column(s) to be marked <code class="literal">NOT NULL</code>.334   </p><p>335    A table can have at most one primary key.  (There can be any number336    of unique and not-null constraints, which are functionally almost the337    same thing, but only one can be identified as the primary key.)338    Relational database theory339    dictates that every table must have a primary key.  This rule is340    not enforced by <span class="productname">PostgreSQL</span>, but it is341    usually best to follow it.342   </p><p>343    Primary keys are useful both for344    documentation purposes and for client applications.  For example,345    a GUI application that allows modifying row values probably needs346    to know the primary key of a table to be able to identify rows347    uniquely.  There are also various ways in which the database system348    makes use of a primary key if one has been declared; for example,349    the primary key defines the default target column(s) for foreign keys350    referencing its table.351   </p></div><div class="sect2" id="DDL-CONSTRAINTS-FK"><div class="titlepage"><div><div><h3 class="title">5.4.5. Foreign Keys <a href="#DDL-CONSTRAINTS-FK" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.6.9.2" class="indexterm"></a><a id="id-1.5.4.6.9.3" class="indexterm"></a><a id="id-1.5.4.6.9.4" class="indexterm"></a><p>352    A foreign key constraint specifies that the values in a column (or353    a group of columns) must match the values appearing in some row354    of another table.355    We say this maintains the <em class="firstterm">referential356    integrity</em> between two related tables.357   </p><p>358    Say you have the product table that we have used several times already:359</p><pre class="programlisting">360CREATE TABLE products (361    product_no integer PRIMARY KEY,362    name text,363    price numeric364);365</pre><p>366    Let's also assume you have a table storing orders of those367    products.  We want to ensure that the orders table only contains368    orders of products that actually exist.  So we define a foreign369    key constraint in the orders table that references the products370    table:371</p><pre class="programlisting">372CREATE TABLE orders (373    order_id integer PRIMARY KEY,374    product_no integer <span class="emphasis"><strong>REFERENCES products (product_no)</strong></span>,375    quantity integer376);377</pre><p>378    Now it is impossible to create orders with non-NULL379    <code class="structfield">product_no</code> entries that do not appear in the380    products table.381   </p><p>382    We say that in this situation the orders table is the383    <em class="firstterm">referencing</em> table and the products table is384    the <em class="firstterm">referenced</em> table.  Similarly, there are385    referencing and referenced columns.386   </p><p>387    You can also shorten the above command to:388</p><pre class="programlisting">389CREATE TABLE orders (390    order_id integer PRIMARY KEY,391    product_no integer <span class="emphasis"><strong>REFERENCES products</strong></span>,392    quantity integer393);394</pre><p>395    because in absence of a column list the primary key of the396    referenced table is used as the referenced column(s).397   </p><p>398    You can assign your own name for a foreign key constraint,399    in the usual way.400   </p><p>401    A foreign key can also constrain and reference a group of columns.402    As usual, it then needs to be written in table constraint form.403    Here is a contrived syntax example:404</p><pre class="programlisting">405CREATE TABLE t1 (406  a integer PRIMARY KEY,407  b integer,408  c integer,409  <span class="emphasis"><strong>FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)</strong></span>410);411</pre><p>412    Of course, the number and type of the constrained columns need to413    match the number and type of the referenced columns.414   </p><a id="id-1.5.4.6.9.11" class="indexterm"></a><p>415    Sometimes it is useful for the <span class="quote">“<span class="quote">other table</span>”</span> of a416    foreign key constraint to be the same table; this is called417    a <em class="firstterm">self-referential</em> foreign key.  For418    example, if you want rows of a table to represent nodes of a tree419    structure, you could write420</p><pre class="programlisting">421CREATE TABLE tree (422    node_id integer PRIMARY KEY,423    parent_id integer REFERENCES tree,424    name text,425    ...426);427</pre><p>428    A top-level node would have NULL <code class="structfield">parent_id</code>,429    while non-NULL <code class="structfield">parent_id</code> entries would be430    constrained to reference valid rows of the table.431   </p><p>432    A table can have more than one foreign key constraint.  This is433    used to implement many-to-many relationships between tables.  Say434    you have tables about products and orders, but now you want to435    allow one order to contain possibly many products (which the436    structure above did not allow).  You could use this table structure:437</p><pre class="programlisting">438CREATE TABLE products (439    product_no integer PRIMARY KEY,440    name text,441    price numeric442);443 444CREATE TABLE orders (445    order_id integer PRIMARY KEY,446    shipping_address text,447    ...448);449 450CREATE TABLE order_items (451    product_no integer REFERENCES products,452    order_id integer REFERENCES orders,453    quantity integer,454    PRIMARY KEY (product_no, order_id)455);456</pre><p>457    Notice that the primary key overlaps with the foreign keys in458    the last table.459   </p><a id="id-1.5.4.6.9.14" class="indexterm"></a><a id="id-1.5.4.6.9.15" class="indexterm"></a><p>460    We know that the foreign keys disallow creation of orders that461    do not relate to any products.  But what if a product is removed462    after an order is created that references it?  SQL allows you to463    handle that as well.  Intuitively, we have a few options:464    </p><div class="itemizedlist"><ul class="itemizedlist compact" style="list-style-type: disc; "><li class="listitem"><p>Disallow deleting a referenced product</p></li><li class="listitem"><p>Delete the orders as well</p></li><li class="listitem"><p>Something else?</p></li></ul></div><p>465   </p><p>466    To illustrate this, let's implement the following policy on the467    many-to-many relationship example above: when someone wants to468    remove a product that is still referenced by an order (via469    <code class="literal">order_items</code>), we disallow it.  If someone470    removes an order, the order items are removed as well:471</p><pre class="programlisting">472CREATE TABLE products (473    product_no integer PRIMARY KEY,474    name text,475    price numeric476);477 478CREATE TABLE orders (479    order_id integer PRIMARY KEY,480    shipping_address text,481    ...482);483 484CREATE TABLE order_items (485    product_no integer REFERENCES products <span class="emphasis"><strong>ON DELETE RESTRICT</strong></span>,486    order_id integer REFERENCES orders <span class="emphasis"><strong>ON DELETE CASCADE</strong></span>,487    quantity integer,488    PRIMARY KEY (product_no, order_id)489);490</pre><p>491   </p><p>492    Restricting and cascading deletes are the two most common options.493    <code class="literal">RESTRICT</code> prevents deletion of a494    referenced row. <code class="literal">NO ACTION</code> means that if any495    referencing rows still exist when the constraint is checked, an error496    is raised; this is the default behavior if you do not specify anything.497    (The essential difference between these two choices is that498    <code class="literal">NO ACTION</code> allows the check to be deferred until499    later in the transaction, whereas <code class="literal">RESTRICT</code> does not.)500    <code class="literal">CASCADE</code> specifies that when a referenced row is deleted,501    row(s) referencing it should be automatically deleted as well.502    There are two other options:503    <code class="literal">SET NULL</code> and <code class="literal">SET DEFAULT</code>.504    These cause the referencing column(s) in the referencing row(s)505    to be set to nulls or their default506    values, respectively, when the referenced row is deleted.507    Note that these do not excuse you from observing any constraints.508    For example, if an action specifies <code class="literal">SET DEFAULT</code>509    but the default value would not satisfy the foreign key constraint, the510    operation will fail.511   </p><p>512    The appropriate choice of <code class="literal">ON DELETE</code> action depends on513    what kinds of objects the related tables represent.  When the referencing514    table represents something that is a component of what is represented by515    the referenced table and cannot exist independently, then516    <code class="literal">CASCADE</code> could be appropriate.  If the two tables517    represent independent objects, then <code class="literal">RESTRICT</code> or518    <code class="literal">NO ACTION</code> is more appropriate; an application that519    actually wants to delete both objects would then have to be explicit about520    this and run two delete commands.  In the above example, order items are521    part of an order, and it is convenient if they are deleted automatically522    if an order is deleted.  But products and orders are different things, and523    so making a deletion of a product automatically cause the deletion of some524    order items could be considered problematic.  The actions <code class="literal">SET525    NULL</code> or <code class="literal">SET DEFAULT</code> can be appropriate if a526    foreign-key relationship represents optional information.  For example, if527    the products table contained a reference to a product manager, and the528    product manager entry gets deleted, then setting the product's product529    manager to null or a default might be useful.530   </p><p>531    The actions <code class="literal">SET NULL</code> and <code class="literal">SET DEFAULT</code>532    can take a column list to specify which columns to set.  Normally, all533    columns of the foreign-key constraint are set; setting only a subset is534    useful in some special cases.  Consider the following example:535</p><pre class="programlisting">536CREATE TABLE tenants (537    tenant_id integer PRIMARY KEY538);539 540CREATE TABLE users (541    tenant_id integer REFERENCES tenants ON DELETE CASCADE,542    user_id integer NOT NULL,543    PRIMARY KEY (tenant_id, user_id)544);545 546CREATE TABLE posts (547    tenant_id integer REFERENCES tenants ON DELETE CASCADE,548    post_id integer NOT NULL,549    author_id integer,550    PRIMARY KEY (tenant_id, post_id),551    FOREIGN KEY (tenant_id, author_id) REFERENCES users ON DELETE SET NULL <span class="emphasis"><strong>(author_id)</strong></span>552);553</pre><p>554    Without the specification of the column, the foreign key would also set555    the column <code class="literal">tenant_id</code> to null, but that column is still556    required as part of the primary key.557   </p><p>558    Analogous to <code class="literal">ON DELETE</code> there is also559    <code class="literal">ON UPDATE</code> which is invoked when a referenced560    column is changed (updated).  The possible actions are the same,561    except that column lists cannot be specified for <code class="literal">SET562    NULL</code> and <code class="literal">SET DEFAULT</code>.563    In this case, <code class="literal">CASCADE</code> means that the updated values of the564    referenced column(s) should be copied into the referencing row(s).565   </p><p>566    Normally, a referencing row need not satisfy the foreign key constraint567    if any of its referencing columns are null.  If <code class="literal">MATCH FULL</code>568    is added to the foreign key declaration, a referencing row escapes569    satisfying the constraint only if all its referencing columns are null570    (so a mix of null and non-null values is guaranteed to fail a571    <code class="literal">MATCH FULL</code> constraint).  If you don't want referencing rows572    to be able to avoid satisfying the foreign key constraint, declare the573    referencing column(s) as <code class="literal">NOT NULL</code>.574   </p><p>575    A foreign key must reference columns that either are a primary key or576    form a unique constraint, or are columns from a non-partial unique index.577    This means that the referenced columns always have an index to allow578    efficient lookups on whether a referencing row has a match.  Since a579    <code class="command">DELETE</code> of a row from the referenced table or an580    <code class="command">UPDATE</code> of a referenced column will require a scan of581    the referencing table for rows matching the old value, it is often a good582    idea to index the referencing columns too.  Because this is not always583    needed, and there are many choices available on how to index, the584    declaration of a foreign key constraint does not automatically create an585    index on the referencing columns.586   </p><p>587    More information about updating and deleting data is in <a class="xref" href="dml.html" title="Chapter 6. Data Manipulation">Chapter 6</a>.  Also see the description of foreign key constraint588    syntax in the reference documentation for589    <a class="xref" href="sql-createtable.html" title="CREATE TABLE"><span class="refentrytitle">CREATE TABLE</span></a>.590   </p></div><div class="sect2" id="DDL-CONSTRAINTS-EXCLUSION"><div class="titlepage"><div><div><h3 class="title">5.4.6. Exclusion Constraints <a href="#DDL-CONSTRAINTS-EXCLUSION" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.6.10.2" class="indexterm"></a><a id="id-1.5.4.6.10.3" class="indexterm"></a><p>591    Exclusion constraints ensure that if any two rows are compared on592    the specified columns or expressions using the specified operators,593    at least one of these operator comparisons will return false or null.594    The syntax is:595</p><pre class="programlisting">596CREATE TABLE circles (597    c circle,598    EXCLUDE USING gist (c WITH &amp;&amp;)599);600</pre><p>601   </p><p>602    See also <a class="link" href="sql-createtable.html#SQL-CREATETABLE-EXCLUDE"><code class="command">CREATE603    TABLE ... CONSTRAINT ... EXCLUDE</code></a> for details.604   </p><p>605    Adding an exclusion constraint will automatically create an index606    of the type specified in the constraint declaration.607   </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="ddl-generated-columns.html" title="5.3. Generated Columns">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="ddl.html" title="Chapter 5. Data Definition">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="ddl-system-columns.html" title="5.5. System Columns">Next</a></td></tr><tr><td width="40%" align="left" valign="top">5.3. Generated Columns </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 5.5. System Columns</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai