Hello
When we create a table, what is the difference between
the REFERENCE constraint on column and the FOREIGN KEY constraint on table ?
I guess that the FOREIGN KEY constraint assures us that the referenced column is
a PRIMARY KEY in an another table.
You can reference any colums with the REFERENCE constraint on column.
Am I right?
If no, what is the difference?
If yes, if I reference a foreign key in the column constraint, am I doing
something wrong?
Here's the doc about creating table:
CREATE TABLE
Creates a new table
Synopsis
CREATE [ TEMPORARY | TEMP ] TABLE table_name (
{ column_name type [ column_constraint [ ... ] ]
| table_constraint } [, ... ]
) [ INHERITS ( inherited_table [, ... ] ) ]
where column_constraint can be:
[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | UNIQUE | PRIMARY KEY | DEFAULT value | CHECK (condition)
|
REFERENCES table [ ( column ) ] [ MATCH FULL | MATCH PARTIAL ]
[ ON DELETE action ] [ ON UPDATE action ]
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE
]
}
and table_constraint can be:
[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) |
PRIMARY KEY ( column_name [, ... ] ) |
CHECK ( condition ) |
FOREIGN KEY ( column_name [, ... ] ) REFERENCES table [ ( column [, ... ] )
]
[ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ]
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] }
Thank you very much
Luc
