begriffs open source - ai-pg/blob - full-docs/src/sgml/html/ddl-constraints.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.5. 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.4. Generated Columns" /><link rel="next" href="ddl-system-columns.html" title="5.6. 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.5. Constraints</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="ddl-generated-columns.html" title="5.4. 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 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="ddl-system-columns.html" title="5.6. 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.5. 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.5.1. Check Constraints</a></span></dt><dt><span class="sect2"><a href="ddl-constraints.html#DDL-CONSTRAINTS-NOT-NULL">5.5.2. Not-Null Constraints</a></span></dt><dt><span class="sect2"><a href="ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS">5.5.3. Unique Constraints</a></span></dt><dt><span class="sect2"><a href="ddl-constraints.html#DDL-CONSTRAINTS-PRIMARY-KEYS">5.5.4. Primary Keys</a></span></dt><dt><span class="sect2"><a href="ddl-constraints.html#DDL-CONSTRAINTS-FK">5.5.5. Foreign Keys</a></span></dt><dt><span class="sect2"><a href="ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION">5.5.6. Exclusion Constraints</a></span></dt></dl></div><a id="id-1.5.4.7.2" class="indexterm"></a><p>
   3    Data types are a way to limit the kind of data that can be stored
   4    in a table.  For many applications, however, the constraint they
   5    provide is too coarse.  For example, a column containing a product
   6    price should probably only accept positive values.  But there is no
   7    standard data type that accepts only positive numbers.  Another issue is
   8    that you might want to constrain column data with respect to other
   9    columns or rows.  For example, in a table containing product
  10    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 and
  13    tables.  Constraints give you as much control over the data in your
  14    tables as you wish.  If a user attempts to store data in a column
  15    that would violate a constraint, an error is raised.  This applies
  16    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.5.1. Check Constraints <a href="#DDL-CONSTRAINTS-CHECK-CONSTRAINTS" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.7.5.2" class="indexterm"></a><a id="id-1.5.4.7.5.3" class="indexterm"></a><p>
  18     A check constraint is the most generic constraint type.  It allows
  19     you to specify that the value in a certain column must satisfy a
  20     Boolean (truth-value) expression.  For instance, to require positive
  21     product prices, you could use:
  22 </p><pre class="programlisting">
  23 CREATE 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 and
  32     constraints can be listed in any order.  A check constraint
  33     consists of the key word <code class="literal">CHECK</code> followed by an
  34     expression in parentheses.  The check constraint expression should
  35     involve the column thus constrained, otherwise the constraint
  36     would not make too much sense.
  37    </p><a id="id-1.5.4.7.5.6" class="indexterm"></a><p>
  38     You can also give the constraint a separate name.  This clarifies
  39     error messages and allows you to refer to the constraint when you
  40     need to change it.  The syntax is:
  41 </p><pre class="programlisting">
  42 CREATE 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 word
  49     <code class="literal">CONSTRAINT</code> followed by an identifier followed
  50     by the constraint definition.  (If you don't specify a constraint
  51     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 you
  54     store a regular price and a discounted price, and you want to
  55     ensure that the discounted price is lower than the regular price:
  56 </p><pre class="programlisting">
  57 CREATE 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 one
  67     uses a new syntax.  It is not attached to a particular column,
  68     instead it appears as a separate item in the comma-separated
  69     column list.  Column definitions and these constraint
  70     definitions can be listed in mixed order.
  71    </p><p>
  72     We say that the first two constraints are column constraints, whereas the
  73     third one is a table constraint because it is written separately
  74     from any one column definition.  Column constraints can also be
  75     written as table constraints, while the reverse is not necessarily
  76     possible, since a column constraint is supposed to refer to only the
  77     column it is attached to.  (<span class="productname">PostgreSQL</span> doesn't
  78     enforce that rule, but you should follow it if you want your table
  79     definitions to work with other database systems.)  The above example could
  80     also be written as:
  81 </p><pre class="programlisting">
  82 CREATE 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">
  94 CREATE 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 as
 105     column constraints:
 106 </p><pre class="programlisting">
 107 CREATE 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.7.5.12" class="indexterm"></a><p>
 118     It should be noted that a check constraint is satisfied if the
 119     check expression evaluates to true or the null value.  Since most
 120     expressions will evaluate to the null value if any operand is null,
 121     they will not prevent null values in the constrained columns.  To
 122     ensure that a column does not contain null values, the not-null
 123     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 support
 126      <code class="literal">CHECK</code> constraints that reference table data other than
 127      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 simple
 129      tests, it cannot guarantee that the database will not reach a state
 130      in which the constraint condition is false (due to subsequent changes
 131      of the other row(s) involved).  This would cause a database dump and
 132      restore to fail.  The restore could fail even when the complete
 133      database state is consistent with the constraint, due to rows not
 134      being loaded in an order that will satisfy the constraint.  If
 135      possible, use <code class="literal">UNIQUE</code>, <code class="literal">EXCLUDE</code>,
 136      or <code class="literal">FOREIGN KEY</code> constraints to express
 137      cross-row and cross-table restrictions.
 138     </p><p>
 139      If what you desire is a one-time check against other rows at row
 140      insertion, rather than a continuously-maintained consistency
 141      guarantee, a custom <a class="link" href="triggers.html" title="Chapter 37. Triggers">trigger</a> can be used
 142      to implement that.  (This approach avoids the dump/restore problem because
 143      <span class="application">pg_dump</span> does not reinstall triggers until after
 144      restoring data, so that the check will not be enforced during a
 145      dump/restore.)
 146     </p></div><div class="note"><h3 class="title">Note</h3><p>
 147      <span class="productname">PostgreSQL</span> assumes that
 148      <code class="literal">CHECK</code> constraints' conditions are immutable, that
 149      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 other
 152      times.  (The warning above about not referencing other table data is
 153      really a special case of this restriction.)
 154     </p><p>
 155      An example of a common way to break this assumption is to reference a
 156      user-defined function in a <code class="literal">CHECK</code> expression, and
 157      then change the behavior of that
 158      function.  <span class="productname">PostgreSQL</span> does not disallow
 159      that, but it will not notice if there are rows in the table that now
 160      violate the <code class="literal">CHECK</code> constraint. That would cause a
 161      subsequent database dump and restore to fail.
 162      The recommended way to handle such a change is to drop the constraint
 163      (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.5.2. Not-Null Constraints <a href="#DDL-CONSTRAINTS-NOT-NULL" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.7.6.2" class="indexterm"></a><a id="id-1.5.4.7.6.3" class="indexterm"></a><p>
 166     A not-null constraint simply specifies that a column must not
 167     assume the null value.  A syntax example:
 168 </p><pre class="programlisting">
 169 CREATE 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 numeric
 173 );
 174 </pre><p>
 175     An explicit constraint name can also be specified, for example:
 176 </p><pre class="programlisting">
 177 CREATE TABLE products (
 178     product_no integer NOT NULL,
 179     name text <span class="emphasis"><strong>CONSTRAINT products_name_not_null</strong></span> NOT NULL,
 180     price numeric
 181 );
 182 </pre><p>
 183    </p><p>
 184     A not-null constraint is usually written as a column constraint.  The
 185     syntax for writing it as a table constraint is
 186 </p><pre class="programlisting">
 187 CREATE TABLE products (
 188     product_no integer,
 189     name text,
 190     price numeric,
 191     <span class="emphasis"><strong>NOT NULL product_no</strong></span>,
 192     <span class="emphasis"><strong>NOT NULL name</strong></span>
 193 );
 194 </pre><p>
 195     But this syntax is not standard and mainly intended for use by
 196     <span class="application">pg_dump</span>.
 197    </p><p>
 198     A not-null constraint is functionally equivalent to creating a check
 199     constraint <code class="literal">CHECK (<em class="replaceable"><code>column_name</code></em>
 200     IS NOT NULL)</code>, but in
 201     <span class="productname">PostgreSQL</span> creating an explicit
 202     not-null constraint is more efficient.
 203    </p><p>
 204     Of course, a column can have more than one constraint.  Just write
 205     the constraints one after another:
 206 </p><pre class="programlisting">
 207 CREATE TABLE products (
 208     product_no integer NOT NULL,
 209     name text NOT NULL,
 210     price numeric NOT NULL CHECK (price &gt; 0)
 211 );
 212 </pre><p>
 213     The order doesn't matter.  It does not necessarily determine in which
 214     order the constraints are checked.
 215    </p><p>
 216     However, a column can have at most one explicit not-null constraint.
 217    </p><p>
 218     The <code class="literal">NOT NULL</code> constraint has an inverse: the
 219     <code class="literal">NULL</code> constraint.  This does not mean that the
 220     column must be null, which would surely be useless.  Instead, this
 221     simply selects the default behavior that the column might be null.
 222     The <code class="literal">NULL</code> constraint is not present in the SQL
 223     standard and should not be used in portable applications.  (It was
 224     only added to <span class="productname">PostgreSQL</span> to be
 225     compatible with some other database systems.)  Some users, however,
 226     like it because it makes it easy to toggle the constraint in a
 227     script file.  For example, you could start with:
 228 </p><pre class="programlisting">
 229 CREATE TABLE products (
 230     product_no integer NULL,
 231     name text NULL,
 232     price numeric NULL
 233 );
 234 </pre><p>
 235     and then insert the <code class="literal">NOT</code> key word where desired.
 236    </p><div class="tip"><h3 class="title">Tip</h3><p>
 237      In most database designs the majority of columns should be marked
 238      not null.
 239     </p></div></div><div class="sect2" id="DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS"><div class="titlepage"><div><div><h3 class="title">5.5.3. Unique Constraints <a href="#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.7.7.2" class="indexterm"></a><a id="id-1.5.4.7.7.3" class="indexterm"></a><p>
 240     Unique constraints ensure that the data contained in a column, or a
 241     group of columns, is unique among all the rows in the
 242     table.  The syntax is:
 243 </p><pre class="programlisting">
 244 CREATE TABLE products (
 245     product_no integer <span class="emphasis"><strong>UNIQUE</strong></span>,
 246     name text,
 247     price numeric
 248 );
 249 </pre><p>
 250     when written as a column constraint, and:
 251 </p><pre class="programlisting">
 252 CREATE TABLE products (
 253     product_no integer,
 254     name text,
 255     price numeric,
 256     <span class="emphasis"><strong>UNIQUE (product_no)</strong></span>
 257 );
 258 </pre><p>
 259     when written as a table constraint.
 260    </p><p>
 261     To define a unique constraint for a group of columns, write it as a
 262     table constraint with the column names separated by commas:
 263 </p><pre class="programlisting">
 264 CREATE TABLE example (
 265     a integer,
 266     b integer,
 267     c integer,
 268     <span class="emphasis"><strong>UNIQUE (a, c)</strong></span>
 269 );
 270 </pre><p>
 271     This specifies that the combination of values in the indicated columns
 272     is unique across the whole table, though any one of the columns
 273     need not be (and ordinarily isn't) unique.
 274    </p><p>
 275     You can assign your own name for a unique constraint, in the usual way:
 276 </p><pre class="programlisting">
 277 CREATE TABLE products (
 278     product_no integer <span class="emphasis"><strong>CONSTRAINT must_be_different</strong></span> UNIQUE,
 279     name text,
 280     price numeric
 281 );
 282 </pre><p>
 283    </p><p>
 284     Adding a unique constraint will automatically create a unique B-tree
 285     index on the column or group of columns listed in the constraint.
 286     A uniqueness restriction covering only some rows cannot be written as
 287     a unique constraint, but it is possible to enforce such a restriction by
 288     creating a unique <a class="link" href="indexes-partial.html" title="11.8. Partial Indexes">partial index</a>.
 289    </p><a id="id-1.5.4.7.7.8" class="indexterm"></a><p>
 290     In general, a unique constraint is violated if there is more than
 291     one row in the table where the values of all of the
 292     columns included in the constraint are equal.
 293     By default, two null values are not considered equal in this
 294     comparison.  That means even in the presence of a
 295     unique constraint it is possible to store duplicate
 296     rows that contain a null value in at least one of the constrained
 297     columns.  This behavior can be changed by adding the clause <code class="literal">NULLS
 298     NOT DISTINCT</code>, like
 299 </p><pre class="programlisting">
 300 CREATE TABLE products (
 301     product_no integer UNIQUE <span class="emphasis"><strong>NULLS NOT DISTINCT</strong></span>,
 302     name text,
 303     price numeric
 304 );
 305 </pre><p>
 306     or
 307 </p><pre class="programlisting">
 308 CREATE TABLE products (
 309     product_no integer,
 310     name text,
 311     price numeric,
 312     UNIQUE <span class="emphasis"><strong>NULLS NOT DISTINCT</strong></span> (product_no)
 313 );
 314 </pre><p>
 315     The default behavior can be specified explicitly using <code class="literal">NULLS
 316     DISTINCT</code>.  The default null treatment in unique constraints is
 317     implementation-defined according to the SQL standard, and other
 318     implementations have a different behavior.  So be careful when developing
 319     applications that are intended to be portable.
 320    </p></div><div class="sect2" id="DDL-CONSTRAINTS-PRIMARY-KEYS"><div class="titlepage"><div><div><h3 class="title">5.5.4. Primary Keys <a href="#DDL-CONSTRAINTS-PRIMARY-KEYS" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.7.8.2" class="indexterm"></a><a id="id-1.5.4.7.8.3" class="indexterm"></a><p>
 321     A primary key constraint indicates that a column, or group of columns,
 322     can be used as a unique identifier for rows in the table.  This
 323     requires that the values be both unique and not null.  So, the following
 324     two table definitions accept the same data:
 325 </p><pre class="programlisting">
 326 CREATE TABLE products (
 327     product_no integer UNIQUE NOT NULL,
 328     name text,
 329     price numeric
 330 );
 331 </pre><p>
 332
 333 </p><pre class="programlisting">
 334 CREATE TABLE products (
 335     product_no integer <span class="emphasis"><strong>PRIMARY KEY</strong></span>,
 336     name text,
 337     price numeric
 338 );
 339 </pre><p>
 340    </p><p>
 341     Primary keys can span more than one column; the syntax
 342     is similar to unique constraints:
 343 </p><pre class="programlisting">
 344 CREATE TABLE example (
 345     a integer,
 346     b integer,
 347     c integer,
 348     <span class="emphasis"><strong>PRIMARY KEY (a, c)</strong></span>
 349 );
 350 </pre><p>
 351    </p><p>
 352     Adding a primary key will automatically create a unique B-tree index
 353     on the column or group of columns listed in the primary key, and will
 354     force the column(s) to be marked <code class="literal">NOT NULL</code>.
 355    </p><p>
 356     A table can have at most one primary key.  (There can be any number
 357     of unique constraints, which combined with not-null constraints are functionally almost the
 358     same thing, but only one can be identified as the primary key.)
 359     Relational database theory
 360     dictates that every table must have a primary key.  This rule is
 361     not enforced by <span class="productname">PostgreSQL</span>, but it is
 362     usually best to follow it.
 363    </p><p>
 364     Primary keys are useful both for
 365     documentation purposes and for client applications.  For example,
 366     a GUI application that allows modifying row values probably needs
 367     to know the primary key of a table to be able to identify rows
 368     uniquely.  There are also various ways in which the database system
 369     makes use of a primary key if one has been declared; for example,
 370     the primary key defines the default target column(s) for foreign keys
 371     referencing its table.
 372    </p></div><div class="sect2" id="DDL-CONSTRAINTS-FK"><div class="titlepage"><div><div><h3 class="title">5.5.5. Foreign Keys <a href="#DDL-CONSTRAINTS-FK" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.7.9.2" class="indexterm"></a><a id="id-1.5.4.7.9.3" class="indexterm"></a><a id="id-1.5.4.7.9.4" class="indexterm"></a><p>
 373     A foreign key constraint specifies that the values in a column (or
 374     a group of columns) must match the values appearing in some row
 375     of another table.
 376     We say this maintains the <em class="firstterm">referential
 377     integrity</em> between two related tables.
 378    </p><p>
 379     Say you have the product table that we have used several times already:
 380 </p><pre class="programlisting">
 381 CREATE TABLE products (
 382     product_no integer PRIMARY KEY,
 383     name text,
 384     price numeric
 385 );
 386 </pre><p>
 387     Let's also assume you have a table storing orders of those
 388     products.  We want to ensure that the orders table only contains
 389     orders of products that actually exist.  So we define a foreign
 390     key constraint in the orders table that references the products
 391     table:
 392 </p><pre class="programlisting">
 393 CREATE TABLE orders (
 394     order_id integer PRIMARY KEY,
 395     product_no integer <span class="emphasis"><strong>REFERENCES products (product_no)</strong></span>,
 396     quantity integer
 397 );
 398 </pre><p>
 399     Now it is impossible to create orders with non-NULL
 400     <code class="structfield">product_no</code> entries that do not appear in the
 401     products table.
 402    </p><p>
 403     We say that in this situation the orders table is the
 404     <em class="firstterm">referencing</em> table and the products table is
 405     the <em class="firstterm">referenced</em> table.  Similarly, there are
 406     referencing and referenced columns.
 407    </p><p>
 408     You can also shorten the above command to:
 409 </p><pre class="programlisting">
 410 CREATE TABLE orders (
 411     order_id integer PRIMARY KEY,
 412     product_no integer <span class="emphasis"><strong>REFERENCES products</strong></span>,
 413     quantity integer
 414 );
 415 </pre><p>
 416     because in absence of a column list the primary key of the
 417     referenced table is used as the referenced column(s).
 418    </p><p>
 419     You can assign your own name for a foreign key constraint,
 420     in the usual way.
 421    </p><p>
 422     A foreign key can also constrain and reference a group of columns.
 423     As usual, it then needs to be written in table constraint form.
 424     Here is a contrived syntax example:
 425 </p><pre class="programlisting">
 426 CREATE TABLE t1 (
 427   a integer PRIMARY KEY,
 428   b integer,
 429   c integer,
 430   <span class="emphasis"><strong>FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)</strong></span>
 431 );
 432 </pre><p>
 433     Of course, the number and type of the constrained columns need to
 434     match the number and type of the referenced columns.
 435    </p><a id="id-1.5.4.7.9.11" class="indexterm"></a><p>
 436     Sometimes it is useful for the <span class="quote">“<span class="quote">other table</span>”</span> of a
 437     foreign key constraint to be the same table; this is called
 438     a <em class="firstterm">self-referential</em> foreign key.  For
 439     example, if you want rows of a table to represent nodes of a tree
 440     structure, you could write
 441 </p><pre class="programlisting">
 442 CREATE TABLE tree (
 443     node_id integer PRIMARY KEY,
 444     parent_id integer REFERENCES tree,
 445     name text,
 446     ...
 447 );
 448 </pre><p>
 449     A top-level node would have NULL <code class="structfield">parent_id</code>,
 450     while non-NULL <code class="structfield">parent_id</code> entries would be
 451     constrained to reference valid rows of the table.
 452    </p><p>
 453     A table can have more than one foreign key constraint.  This is
 454     used to implement many-to-many relationships between tables.  Say
 455     you have tables about products and orders, but now you want to
 456     allow one order to contain possibly many products (which the
 457     structure above did not allow).  You could use this table structure:
 458 </p><pre class="programlisting">
 459 CREATE TABLE products (
 460     product_no integer PRIMARY KEY,
 461     name text,
 462     price numeric
 463 );
 464
 465 CREATE TABLE orders (
 466     order_id integer PRIMARY KEY,
 467     shipping_address text,
 468     ...
 469 );
 470
 471 CREATE TABLE order_items (
 472     product_no integer REFERENCES products,
 473     order_id integer REFERENCES orders,
 474     quantity integer,
 475     PRIMARY KEY (product_no, order_id)
 476 );
 477 </pre><p>
 478     Notice that the primary key overlaps with the foreign keys in
 479     the last table.
 480    </p><a id="id-1.5.4.7.9.14" class="indexterm"></a><a id="id-1.5.4.7.9.15" class="indexterm"></a><p>
 481     We know that the foreign keys disallow creation of orders that
 482     do not relate to any products.  But what if a product is removed
 483     after an order is created that references it?  SQL allows you to
 484     handle that as well.  Intuitively, we have a few options:
 485     </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>
 486    </p><p>
 487     To illustrate this, let's implement the following policy on the
 488     many-to-many relationship example above: when someone wants to
 489     remove a product that is still referenced by an order (via
 490     <code class="literal">order_items</code>), we disallow it.  If someone
 491     removes an order, the order items are removed as well:
 492 </p><pre class="programlisting">
 493 CREATE TABLE products (
 494     product_no integer PRIMARY KEY,
 495     name text,
 496     price numeric
 497 );
 498
 499 CREATE TABLE orders (
 500     order_id integer PRIMARY KEY,
 501     shipping_address text,
 502     ...
 503 );
 504
 505 CREATE TABLE order_items (
 506     product_no integer REFERENCES products <span class="emphasis"><strong>ON DELETE RESTRICT</strong></span>,
 507     order_id integer REFERENCES orders <span class="emphasis"><strong>ON DELETE CASCADE</strong></span>,
 508     quantity integer,
 509     PRIMARY KEY (product_no, order_id)
 510 );
 511 </pre><p>
 512    </p><p>
 513     The default <code class="literal">ON DELETE</code> action is <code class="literal">ON DELETE NO
 514     ACTION</code>; this does not need to be specified.  This means that the
 515     deletion in the referenced table is allowed to proceed.  But the
 516     foreign-key constraint is still required to be satisfied, so this
 517     operation will usually result in an error.  But checking of foreign-key
 518     constraints can also be deferred to later in the transaction (not covered
 519     in this chapter).  In that case, the <code class="literal">NO ACTION</code> setting
 520     would allow other commands to <span class="quote">“<span class="quote">fix</span>”</span> the situation before the
 521     constraint is checked, for example by inserting another suitable row into
 522     the referenced table or by deleting the now-dangling rows from the
 523     referencing table.
 524    </p><p>
 525     <code class="literal">RESTRICT</code> is a stricter setting than <code class="literal">NO
 526     ACTION</code>.  It prevents deletion of a referenced row.
 527     <code class="literal">RESTRICT</code> does not allow the check to be deferred until
 528     later in the transaction.
 529    </p><p>
 530     <code class="literal">CASCADE</code> specifies that when a referenced row is deleted,
 531     row(s) referencing it should be automatically deleted as well.
 532    </p><p>
 533     There are two other options:
 534     <code class="literal">SET NULL</code> and <code class="literal">SET DEFAULT</code>.
 535     These cause the referencing column(s) in the referencing row(s)
 536     to be set to nulls or their default
 537     values, respectively, when the referenced row is deleted.
 538     Note that these do not excuse you from observing any constraints.
 539     For example, if an action specifies <code class="literal">SET DEFAULT</code>
 540     but the default value would not satisfy the foreign key constraint, the
 541     operation will fail.
 542    </p><p>
 543     The appropriate choice of <code class="literal">ON DELETE</code> action depends on
 544     what kinds of objects the related tables represent.  When the referencing
 545     table represents something that is a component of what is represented by
 546     the referenced table and cannot exist independently, then
 547     <code class="literal">CASCADE</code> could be appropriate.  If the two tables
 548     represent independent objects, then <code class="literal">RESTRICT</code> or
 549     <code class="literal">NO ACTION</code> is more appropriate; an application that
 550     actually wants to delete both objects would then have to be explicit about
 551     this and run two delete commands.  In the above example, order items are
 552     part of an order, and it is convenient if they are deleted automatically
 553     if an order is deleted.  But products and orders are different things, and
 554     so making a deletion of a product automatically cause the deletion of some
 555     order items could be considered problematic.  The actions <code class="literal">SET
 556     NULL</code> or <code class="literal">SET DEFAULT</code> can be appropriate if a
 557     foreign-key relationship represents optional information.  For example, if
 558     the products table contained a reference to a product manager, and the
 559     product manager entry gets deleted, then setting the product's product
 560     manager to null or a default might be useful.
 561    </p><p>
 562     The actions <code class="literal">SET NULL</code> and <code class="literal">SET DEFAULT</code>
 563     can take a column list to specify which columns to set.  Normally, all
 564     columns of the foreign-key constraint are set; setting only a subset is
 565     useful in some special cases.  Consider the following example:
 566 </p><pre class="programlisting">
 567 CREATE TABLE tenants (
 568     tenant_id integer PRIMARY KEY
 569 );
 570
 571 CREATE TABLE users (
 572     tenant_id integer REFERENCES tenants ON DELETE CASCADE,
 573     user_id integer NOT NULL,
 574     PRIMARY KEY (tenant_id, user_id)
 575 );
 576
 577 CREATE TABLE posts (
 578     tenant_id integer REFERENCES tenants ON DELETE CASCADE,
 579     post_id integer NOT NULL,
 580     author_id integer,
 581     PRIMARY KEY (tenant_id, post_id),
 582     FOREIGN KEY (tenant_id, author_id) REFERENCES users ON DELETE SET NULL <span class="emphasis"><strong>(author_id)</strong></span>
 583 );
 584 </pre><p>
 585     Without the specification of the column, the foreign key would also set
 586     the column <code class="literal">tenant_id</code> to null, but that column is still
 587     required as part of the primary key.
 588    </p><p>
 589     Analogous to <code class="literal">ON DELETE</code> there is also
 590     <code class="literal">ON UPDATE</code> which is invoked when a referenced
 591     column is changed (updated).  The possible actions are the same,
 592     except that column lists cannot be specified for <code class="literal">SET
 593     NULL</code> and <code class="literal">SET DEFAULT</code>.
 594     In this case, <code class="literal">CASCADE</code> means that the updated values of the
 595     referenced column(s) should be copied into the referencing row(s).
 596     There is also a noticeable difference between <code class="literal">ON UPDATE NO
 597     ACTION</code> (the default) and <code class="literal">ON UPDATE RESTRICT</code>.
 598     The former will allow the update to proceed and the foreign-key constraint
 599     will be checked against the state after the update.  The latter will
 600     prevent the update to run even if the state after the update would still
 601     satisfy the constraint.  This prevents updating a referenced row to a
 602     value that is distinct but compares as equal (for example, a character
 603     string with a different case variant, if a character string type with a
 604     case-insensitive collation is used).
 605    </p><p>
 606     Normally, a referencing row need not satisfy the foreign key constraint
 607     if any of its referencing columns are null.  If <code class="literal">MATCH FULL</code>
 608     is added to the foreign key declaration, a referencing row escapes
 609     satisfying the constraint only if all its referencing columns are null
 610     (so a mix of null and non-null values is guaranteed to fail a
 611     <code class="literal">MATCH FULL</code> constraint).  If you don't want referencing rows
 612     to be able to avoid satisfying the foreign key constraint, declare the
 613     referencing column(s) as <code class="literal">NOT NULL</code>.
 614    </p><p>
 615     A foreign key must reference columns that either are a primary key or
 616     form a unique constraint, or are columns from a non-partial unique index.
 617     This means that the referenced columns always have an index to allow
 618     efficient lookups on whether a referencing row has a match.  Since a
 619     <code class="command">DELETE</code> of a row from the referenced table or an
 620     <code class="command">UPDATE</code> of a referenced column will require a scan of
 621     the referencing table for rows matching the old value, it is often a good
 622     idea to index the referencing columns too.  Because this is not always
 623     needed, and there are many choices available on how to index, the
 624     declaration of a foreign key constraint does not automatically create an
 625     index on the referencing columns.
 626    </p><p>
 627     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 constraint
 628     syntax in the reference documentation for
 629     <a class="xref" href="sql-createtable.html" title="CREATE TABLE"><span class="refentrytitle">CREATE TABLE</span></a>.
 630    </p></div><div class="sect2" id="DDL-CONSTRAINTS-EXCLUSION"><div class="titlepage"><div><div><h3 class="title">5.5.6. Exclusion Constraints <a href="#DDL-CONSTRAINTS-EXCLUSION" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.7.10.2" class="indexterm"></a><a id="id-1.5.4.7.10.3" class="indexterm"></a><p>
 631     Exclusion constraints ensure that if any two rows are compared on
 632     the specified columns or expressions using the specified operators,
 633     at least one of these operator comparisons will return false or null.
 634     The syntax is:
 635 </p><pre class="programlisting">
 636 CREATE TABLE circles (
 637     c circle,
 638     EXCLUDE USING gist (c WITH &amp;&amp;)
 639 );
 640 </pre><p>
 641    </p><p>
 642     See also <a class="link" href="sql-createtable.html#SQL-CREATETABLE-EXCLUDE"><code class="command">CREATE
 643     TABLE ... CONSTRAINT ... EXCLUDE</code></a> for details.
 644    </p><p>
 645     Adding an exclusion constraint will automatically create an index
 646     of the type specified in the constraint declaration.
 647    </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.4. 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.6. System Columns">Next</a></td></tr><tr><td width="40%" align="left" valign="top">5.4. Generated Columns </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 5.6. System Columns</td></tr></table></div></body></html>