From 6f8bb7c1e9610dd7af20cdaf74c4ff6e6d678d44 Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Mon, 13 May 2024 11:31:09 +0200 Subject: Revert structural changes to not-null constraints There are some problems with the new way to handle these constraints that were detected at the last minute, and require fixes that appear too invasive to be doing this late in the cycle. Revert this (again) for now, we'll try again with these problems fixed. The following commits are reverted: b0e96f311985 Catalog not-null constraints 9b581c534186 Disallow changing NO INHERIT status of a not-null constraint d0ec2ddbe088 Fix not-null constraint test ac22a9545ca9 Move privilege check to the right place b0f7dd915bca Check stack depth in new recursive functions 3af721794272 Update information_schema definition for not-null constraints c3709100be73 Fix propagating attnotnull in multiple inheritance d9f686a72ee9 Fix restore of not-null constraints with inheritance d72d32f52d26 Don't try to assign smart names to constraints 0cd711271d42 Better handle indirect constraint drops 13daa33fa5a6 Disallow NO INHERIT not-null constraints on partitioned tables d45597f72fe5 Disallow direct change of NO INHERIT of not-null constraints 21ac38f498b3 Fix inconsistencies in error messages Discussion: https://postgr.es/m/202405110940.joxlqcx4dogd@alvherre.pgsql --- doc/src/sgml/catalogs.sgml | 13 +++++---- doc/src/sgml/ddl.sgml | 55 ++++++++------------------------------ doc/src/sgml/ref/alter_table.sgml | 13 +++------ doc/src/sgml/ref/create_table.sgml | 8 +++++- 4 files changed, 29 insertions(+), 60 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index b530c030f01..56deb4ac5fd 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1270,8 +1270,7 @@ attnotnull bool - This column is marked not-null, either by a not-null constraint - or a primary key. + This represents a not-null constraint. @@ -2502,10 +2501,14 @@ SCRAM-SHA-256$<iteration count>:&l - The catalog pg_constraint stores check, not-null, - primary key, unique, foreign key, and exclusion constraints on tables. + The catalog pg_constraint stores check, primary + key, unique, foreign key, and exclusion constraints on tables, as well as + not-null constraints on domains. (Column constraints are not treated specially. Every column constraint is equivalent to some table constraint.) + Not-null constraints on relations are represented in the + pg_attribute + catalog, not here. @@ -2567,7 +2570,7 @@ SCRAM-SHA-256$<iteration count>:&l c = check constraint, f = foreign key constraint, - n = not-null constraint, + f = not-null constraint (domains only), 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 026bfff70f3..6aab79e901c 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -763,38 +763,17 @@ 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 functionally equivalent to creating a check + A not-null constraint is always written as a column constraint. 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. + not-null constraint is more efficient. The drawback is that you + cannot give explicit names to not-null constraints created this + way. @@ -811,10 +790,6 @@ 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 @@ -1008,7 +983,7 @@ CREATE TABLE example ( A table can have at most one primary key. (There can be any number - of unique constraints, which combined with not-null constraints are functionally almost the + of unique and not-null constraints, which 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 @@ -1668,16 +1643,11 @@ 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 is normally not written as a table - constraint, this special syntax is available: + To add a not-null constraint, which cannot be written as a table + constraint, use this syntax: ALTER TABLE products ALTER COLUMN product_no SET NOT NULL; - This command silently does nothing if the column already has a - not-null constraint. @@ -1718,15 +1688,12 @@ ALTER TABLE products DROP CONSTRAINT some_name; - Simplified syntax is available to drop a not-null constraint: + This works the same for all constraint types except not-null + constraints. To drop a not-null constraint use: ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL; - 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.) + (Recall that not-null constraints do not have names.) diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 0bf11f6cb6d..5d352abf991 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -105,7 +105,7 @@ WITH ( MODULUS numeric_literal, REM and column_constraint is: [ CONSTRAINT constraint_name ] -{ NOT NULL [ NO INHERIT ] | +{ NOT NULL | NULL | CHECK ( expression ) [ NO INHERIT ] | DEFAULT default_expr | @@ -121,7 +121,6 @@ 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 ) ] | @@ -1942,17 +1941,11 @@ ALTER TABLE sales_list MERGE PARTITIONS (sales_west, sales_east, sales_central) Compatibility - The forms ADD [COLUMN], + The forms ADD (without USING INDEX), DROP [COLUMN], DROP IDENTITY, RESTART, SET DEFAULT, SET DATA TYPE (without USING), SET GENERATED, and SET sequence_option - 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 + conform with the SQL standard. 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 75f06bc49cc..a5bf80fb27e 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -77,7 +77,6 @@ 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 [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters | EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] | @@ -2392,6 +2391,13 @@ 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