From b0e96f311985bceba79825214f8e43f65afa653a Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Fri, 25 Aug 2023 13:31:24 +0200 Subject: Catalog not-null constraints MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit We now create contype='n' pg_constraint rows for not-null constraints. We propagate these constraints to other tables during operations such as adding inheritance relationships, creating and attaching partitions and creating tables LIKE other tables. We also spawn not-null constraints for inheritance child tables when their parents have primary keys. These related constraints mostly follow the well-known rules of conislocal and coninhcount that we have for CHECK constraints, with some adaptations: for example, as opposed to CHECK constraints, we don't match not-null ones by name when descending a hierarchy to alter it, instead matching by column name that they apply to. This means we don't require the constraint names to be identical across a hierarchy. For now, we omit them for system catalogs. Maybe this is worth reconsidering. We don't support NOT VALID nor DEFERRABLE clauses either; these can be added as separate features later (this patch is already large and complicated enough.) psql shows these constraints in \d+. pg_dump requires some ad-hoc hacks, particularly when dumping a primary key. We now create one "throwaway" not-null constraint for each column in the PK together with the CREATE TABLE command, and once the PK is created, all those throwaway constraints are removed. This avoids having to check each tuple for nullness when the dump restores the primary key creation. pg_upgrading from an older release requires a somewhat brittle procedure to create a constraint state that matches what would be created if the database were being created fresh in Postgres 17. I have tested all the scenarios I could think of, and it works correctly as far as I can tell, but I could have neglected weird cases. This patch has been very long in the making. The first patch was written by Bernd Helmle in 2010 to add a new pg_constraint.contype value ('n'), which I (Álvaro) then hijacked in 2011 and 2012, until that one was killed by the realization that we ought to use contype='c' instead: manufactured CHECK constraints. However, later SQL standard development, as well as nonobvious emergent properties of that design (mostly, failure to distinguish them from "normal" CHECK constraints as well as the performance implication of having to test the CHECK expression) led us to reconsider this choice, so now the current implementation uses contype='n' again. During Postgres 16 this had already been introduced by commit e056c557aef4, but there were some problems mainly with the pg_upgrade procedure that couldn't be fixed in reasonable time, so it was reverted. In 2016 Vitaly Burovoy also worked on this feature[1] but found no consensus for his proposed approach, which was claimed to be closer to the letter of the standard, requiring an additional pg_attribute column to track the OID of the not-null constraint for that column. [1] https://postgr.es/m/CAKOSWNkN6HSyatuys8xZxzRCR-KL1OkHS5-b9qd9bf1Rad3PLA@mail.gmail.com Author: Álvaro Herrera Author: Bernd Helmle Reviewed-by: Justin Pryzby Reviewed-by: Peter Eisentraut Reviewed-by: Dean Rasheed --- doc/src/sgml/catalogs.sgml | 11 ++++---- doc/src/sgml/ddl.sgml | 55 ++++++++++++++++++++++++++++++-------- doc/src/sgml/ref/alter_table.sgml | 11 ++++++-- doc/src/sgml/ref/create_table.sgml | 8 +----- 4 files changed, 59 insertions(+), 26 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 307ad88b506..d17ff51e282 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1270,7 +1270,8 @@ attnotnull bool - This represents a not-null constraint. + This column is marked not-null, either by a not-null constraint + or a primary key. @@ -2484,13 +2485,10 @@ SCRAM-SHA-256$<iteration count>:&l - The catalog pg_constraint stores check, primary - key, unique, foreign key, and exclusion constraints on tables. + The catalog pg_constraint stores check, not-null, + primary key, unique, foreign key, and exclusion constraints on tables. (Column constraints are not treated specially. Every column constraint is equivalent to some table constraint.) - Not-null constraints are represented in the - pg_attribute - catalog, not here. @@ -2552,6 +2550,7 @@ SCRAM-SHA-256$<iteration count>:&l c = check constraint, f = foreign key constraint, + n = not-null constraint, p = primary key constraint, u = unique constraint, t = constraint trigger, diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 58aaa691c6a..075ff329912 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -651,17 +651,38 @@ CREATE TABLE products ( price numeric ); + An explicit constraint name can also be specified, for example: + +CREATE TABLE products ( + product_no integer NOT NULL, + name text CONSTRAINT products_name_not_null NOT NULL, + price numeric +); + + + + + A not-null constraint is usually written as a column constraint. The + syntax for writing it as a table constraint is + +CREATE TABLE products ( + product_no integer, + name text, + price numeric, + NOT NULL product_no, + NOT NULL name +); + + But this syntax is not standard and mainly intended for use by + pg_dump. - A not-null constraint is always written as a column constraint. A - not-null constraint is functionally equivalent to creating a check + A not-null constraint is functionally equivalent to creating a check constraint CHECK (column_name IS NOT NULL), but in PostgreSQL creating an explicit - not-null constraint is more efficient. The drawback is that you - cannot give explicit names to not-null constraints created this - way. + not-null constraint is more efficient. @@ -678,6 +699,10 @@ CREATE TABLE products ( order the constraints are checked. + + However, a column can have at most one explicit not-null constraint. + + The NOT NULL constraint has an inverse: the NULL constraint. This does not mean that the @@ -871,7 +896,7 @@ CREATE TABLE example ( A table can have at most one primary key. (There can be any number - of unique and not-null constraints, which are functionally almost the + of unique constraints, which combined with not-null constraints are functionally almost the same thing, but only one can be identified as the primary key.) Relational database theory dictates that every table must have a primary key. This rule is @@ -1531,11 +1556,16 @@ ALTER TABLE products ADD CHECK (name <> ''); ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no); ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups; - To add a not-null constraint, which cannot be written as a table - constraint, use this syntax: + + + + To add a not-null constraint, which is normally not written as a table + constraint, this special syntax is available: ALTER TABLE products ALTER COLUMN product_no SET NOT NULL; + This command silently does nothing if the column already has a + not-null constraint. @@ -1576,12 +1606,15 @@ ALTER TABLE products DROP CONSTRAINT some_name; - This works the same for all constraint types except not-null - constraints. To drop a not null constraint use: + Simplified syntax is available to drop a not-null constraint: ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL; - (Recall that not-null constraints do not have names.) + This mirrors the SET NOT NULL syntax for adding a + not-null constraint. This command will silently do nothing if the column + does not have a not-null constraint. (Recall that a column can have at + most one not-null constraint, so it is never ambiguous which constraint + this command acts on.) diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index d4d93eeb7c6..2c4138e4e9f 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -113,6 +113,7 @@ WITH ( MODULUS numeric_literal, REM [ CONSTRAINT constraint_name ] { CHECK ( expression ) [ NO INHERIT ] | + NOT NULL column_name [ NO INHERIT ] | UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] ) index_parameters | EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] | @@ -1763,11 +1764,17 @@ ALTER TABLE measurement Compatibility - The forms ADD (without USING INDEX), + The forms ADD [COLUMN], DROP [COLUMN], DROP IDENTITY, RESTART, SET DEFAULT, SET DATA TYPE (without USING), SET GENERATED, and SET sequence_option - conform with the SQL standard. The other forms are + conform with the SQL standard. + The form ADD table_constraint + conforms with the SQL standard when the USING INDEX and + NOT VALID clauses are omitted and the constraint type is + one of CHECK, UNIQUE, PRIMARY KEY, + or REFERENCES. + The other forms are PostgreSQL extensions of the SQL standard. Also, the ability to specify more than one manipulation in a single ALTER TABLE command is an extension. diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 10ef699fab9..e04a0692c4e 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -77,6 +77,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI [ CONSTRAINT constraint_name ] { CHECK ( expression ) [ NO INHERIT ] | + NOT NULL column_name [ NO INHERIT ] | UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] ) index_parameters | EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] | @@ -2314,13 +2315,6 @@ CREATE TABLE cities_partdef constraint, and index names must be unique across all relations within the same schema. - - - Currently, PostgreSQL does not record names - for NOT NULL constraints at all, so they are not - subject to the uniqueness restriction. This might change in a future - release. - -- cgit v1.2.3