Lone Learner Lone Learner - 6 months ago 12
SQL Question

How to define a foreign key in the same line where the column is being defined?

I am learning to use Oracle 12.1.

What's wrong with the foreign key definition below?

set echo on
drop table city;
drop table emp;

create table city (
id number primary key,
name varchar2(20)
);

create table emp (
id number,
cityid number foreign key references city(id)
);


When I execute it, I get the following error.

$ sqlplus / as sysdba @foo.sql
...
...
SQL>
SQL> create table city (
2 id number primary key,
3 name varchar2(20)
4 );

Table created.

SQL>
SQL> create table emp (
2 id number,
3 cityid number foreign key references city(id)
4 );
cityid number foreign key references city(id)
*
ERROR at line 3:
ORA-00907: missing right parenthesis


I thought it was valid to define foreign key constraint for a column in the same line where the column is being defined.

I am using the syntax defined under http://www.w3schools.com/sql/sql_foreignkey.asp > SQL FOREIGN KEY Constraint on CREATE TABLE > SQL Server / Oracle / MS Access.

Is this syntax really invalid in Oracle 12.1?

Answer

In Oracle you do not need explicit foreign key clause:

create table emp (
    id number,
    cityid number references city(id)
);

If you want to name your constraint, without creating it with a seperate statement, you can use:

create table emp (
    id number,
    cityid number constraint CONSTRAINT_NAME references city(id)
);
Comments