codekingpro/portable-devtools
114k
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 > 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 > 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 > 0),61 discounted_price numeric CHECK (discounted_price > 0),62 <span class="emphasis"><strong>CHECK (price > 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 > 0),87 discounted_price numeric,88 CHECK (discounted_price > 0),89 CHECK (price > 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 > 0),98 discounted_price numeric,99 CHECK (discounted_price > 0 AND price > 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 > 0),112 discounted_price numeric,113 CHECK (discounted_price > 0),114 <span class="emphasis"><strong>CONSTRAINT valid_discount</strong></span> CHECK (price > 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 > 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 &&)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>