Difference between REFERENCES and FOREIGN KEY

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Luc Martineau

    Difference between REFERENCES and FOREIGN KEY

    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_constrai nt [ ... ] ]
    | table_constrain t } [, ... ]
    ) [ INHERITS ( inherited_table [, ... ] ) ]

    where column_constrai nt 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_constrain t 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

Working...