CODEWITHSUNDEEP
sqlpostgresql
user3284469
user3284469

Reputation:

Is there inconsistency in PostgreSQL syntax of adding constraints to a table?

From PostgreSQL document

To add a constraint, the table constraint syntax is used. For example:

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:

ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

Why the word after ADD differ for constraints of different kinds?

Why does unique constraint has a more generic ADD CONSTRAINT some_name than check and foreign key?

Why is not null constraint specified by ALTER COLUMN, instead of ADD CONSTRAINT some_name NOT NULL (col_name)?

Is there inconsistency in PostgreSQL syntax of adding constraints to a table? Does this belong to the SQL standard?

Upvotes: 0

Views: 147

Answers (2)

melpomene
melpomene

Reputation: 85777

The word after ADD differs so the database knows what you mean. E.g. CHECK introduces a generic boolean condition; UNIQUE is followed by a list of column names; FOREIGN KEY is followed by a column name, REFERENCES, and a target table/column. Without these keywords it would be ambiguous which kind of constraint you mean.

The CONSTRAINT constraint_name syntax is not limited to unique constraints. See the definition of column_constraint and table_constraint in https://www.postgresql.org/docs/10/static/sql-createtable.html; both allow an optional leading CONSTRAINT constraint_name to name the constraint.

As for NOT NULL, see https://www.postgresql.org/docs/10/static/ddl-constraints.html#id-1.5.4.5.6:

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. The drawback is that you cannot give explicit names to not-null constraints created this way.

I assume not-null constraints are a special case internally, allowing for better optimization than a generic CHECK constraint, which can use any boolean expression.

Upvotes: 2

Gaj
Gaj

Reputation: 886

We cant use add constraint syntax for Not Null. You have to use modify column syntax to add not null eg. alter table modify ( not null);

Upvotes: 1

Related Questions