CODEWITHSUNDEEP
postgresqlconstraints
JojoBop
JojoBop

Reputation: 63

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

Upvotes: 6

Views: 8115

Answers (4)

Rudra
Rudra

Reputation: 81

1) Column level constraint is declared at the time of creating a table but table level constraint is created after table is created.

2) NOT NULL constraint can't be created at table level because All Constraint will give logical view to that particular column but NOT NULL will assign to the structure of the table itself.. That's why we can see NOT NULL constraint while Describe the table, no other constraint will be seen.

3) Composite primary key must be declared at table level.

4) All the constraints can be created at table level but for table level NOT NULL is not allowed.

Upvotes: 8

anonymous
anonymous

Reputation: 11

NOT NULL can be assigned even after the table is created using alter table command. I tested it in oracle sql+ 10g. The syntax I used is as follows: ALTER TABLE tablename MODIFY columnname NOTNULL;

Expecting you views on this.

Upvotes: 1

A.H.
A.H.

Reputation: 66263

If the foreign key contstraint or the primary key constraint cover only one column you have the choice to write it your way or like this:

CREATE TABLE test(
    id integer NOT NULL CHECK (id3>=id2),
    id2 integer,
    id3 integer,
    CONSTRAINT PK_test PRIMARY KEY(id, id2, id3)
);

But if either the PK or the FK cover more than one column, you must use the "table constraint" as I have shown you.

There is no more magic behind it!

Upvotes: 1

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95622

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.)

Upvotes: 6

Related Questions