Postgresql: difference between table constraints and column constraints

Question

Excerpt from pgsql 8.4 docs: "[...] a column constraint is only a notation to use when a constraint affects only one column." How is this virtual 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 the PRIMARY KEY and FOREIGN KEY constraints, which should only affect the column on the same row where the constraints are declared, as Catcall stated

Answer 1

Elsewhere in the documents .,.

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

Answer 2

1) A column level restriction is declared during table creation, but a table level restriction is created after the table is created.

2) A NOT NULL constraint cannot be created at the table level, because All Constraint will provide a logical view to this particular column, but NOT NULL assign the structure of the table itself. This is why we can see the NOT NULL during the table description; no other constraint will be visible.

3) A composite primary key must be declared at the table level.

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

Answer 3

If a foreign key constraint or primary key constraint spans only one column, you have the choice to write it your own 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 PK or FK spans more than one column, you should use a “table constraint”, as I showed you.

There is no more magic behind it!

Answer 4

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

We expect you to review this.