Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Postgresql: Difference between table constraint and column constraint

Excerpt fro pgsql 8.4 docs : " [...]a column constraint is only a notational convenience for use when the constraint only affects one column." How is this valid sql then?



DROP TABLE IF EXISTS test;
CREATE TABLE test(
id integer CONSTRAINT PK_test PRIMARY KEY  CONSTRAINT nenull NOT NULL CHECK (id3>=id2) 
--the check constraint affects two columns none of which is id
,id2 integer 
, id3 integer
);

the excerpt seems to apply only to PRIMARY KEY and FOREIGN KEY constraints which should only affect the column on the same line where the constraints are declarated as Catcall has stated

like image 576
JojoBop Avatar asked Sep 17 '11 15:09

JojoBop


People also ask

What is table constraint in PostgreSQL?

Constraints are the rules enforced on data columns on table. These are used to prevent invalid data from being entered into the database. This ensures the accuracy and reliability of the data in the database. Constraints could be column level or table level.

What is the difference between a column constraint and a table constraint quizlet?

What is the difference between a column constraint and a table constraint? A column constraint applies to a single column; a table restraint may apply to many columns.

Why would you use column constraints?

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted. Constraints can be column level or table level.

What is column constraint?

Column constraints are restrictions on the data that can be inserted into a given column.


1 Answers

Elsewhere in the docs . . .

We say that the first two constraints are column constraints, whereas the third one is a table constraint because it is written separately from any one column definition. Column constraints can also be written as table constraints, while the reverse is not necessarily possible, since a column constraint is supposed to refer to only the column it is attached to. (PostgreSQL doesn't enforce that rule, but you should follow it if you want your table definitions to work with other database systems.)

like image 104
Mike Sherrill 'Cat Recall' Avatar answered Nov 15 '22 07:11

Mike Sherrill 'Cat Recall'