Re: Difference between REFERENCES and FOREIGN KEY

Luc,
If I understand your question correctly, you want to know if you can
create a FOREIGN KEY on a table which references a column other than
the PRIMARY KEY in the referenced table.  You can only reference a
PRIMARY KEY or UNIQUE constraint when creating a FOREIGN KEY, if you
try to reference any other type of constrained column, you will get
the following error:

Elapsed: 00:00:00.02
10:25:49 SQL> alter table reference
10:25:58   2  add constraint ref_test foreign key (x) references
activity_log(activity_type);
add constraint ref_test foreign key (x) references
activity_log(activity_type)
                                                                *
ERROR at line 2:
ORA-02270: no matching unique or primary key for this column-list

REFERENCES is only used when creating a FOREIGN KEY, you would not
used it when creating a UNIQUE constraint for instance.

I hope this helps.

Sebastian DiFelice


Luc Martineau <[EMAIL PROTECTED]> wrote in message news:<[EMAIL PROTECTED]>...
> 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