SQL How to Add FOREIGN KEY Constraint to Table

To maintain data consistency, you may want to allow values in a column of one table from those stored in Primary Key Column of another table. In case you want to define the relationship between two tables apply the FOREIGN KEY constraint. Each newly inserted or updated value of such column must take value from those stored in primary key column of the referenced table. In this tutorial you will understand how to add FOREIGN KEY Constraint to Table.

The Foreign Key constraint can be applied to single column or a group of columns. In case you are defining Foreign Key constraint on a set of columns, that group of columns must be the primary key in the referenced table.

How to Add FOREIGN KEY Constraint to Table

FOREIGN KEY constraint can be added as column level or table level constraint.

Syntax for Column Level Foreign Key Constraint

Column-name DATATYPE(size) [CONSTRAINT constraint-name] REFERENCES table-name(column),

  • Column-Name is the name of the column that you want to make foreign Key
  • CONSTRAINT Constraint-name- the keyword and the name of the FOREIGN KEY constraint (this is optional)
  • REFERENCES keyword
  • Column is the Primary Key column(s) of the referenced table-name

Referenced or Master Table

CREATE TABLE CityTbl
(
CITYID Number(4) PRIMARY KEY,
CITYNAME varchar2(20)
);

Data Inserted in the Master Table CityTbl

CITYID CITYNAME
1 New York
2 Chicago
3 Washington
4 Ohio

 Referring or Dependent Table

Column Level Foreign Key Constraint without Constraint Name

CREATE TABLE SalesTbl
(
SPID number(6) PRIMARY KEY,
SPNAME varchar2(30),
SPCITYID number(4)  references CityTbl(CITYID),
SPCOMM number(2,2)
);

Column Level Foreign Key Constraint with Constraint Name

CREATE TABLE SalesTbl
(
SPID number(6) PRIMARY KEY,
SPNAME varchar2(30),
SPCITYID number(4) CONSTRAINT fk_SPCID references CityTbl(CITYID),
SPCOMM number(2,2)
);

Output after insertion in SalesTbl

insert into salestbl values(1,’Sam’,2,.3); success

insert into salestbl values(1,’Sam’,5,.3); Failure ORA-00001: unique constraint (SQL_OSEHQUKFGTAMZKTNLJPQJARUO.SYS_C0013876443) violated ORA-06512: at “SYS.DBMS_SQL”, line 1721

Syntax for Table Level Foreign Key Constraint

(Table definition,

[CONSTRAINT constraint-name] FOREIGN KEY (Column-names) REFERENCES table-name (columns of referenced table as Primary Key),

  • Column-Names are the names of the columns that you want to make foreign Key
  • CONSTRAINT Constraint-name- the keyword and the name of the FOREIGN KEY constraint (this is optional)
  • FOREIGN KEY and REFERENCES keywords
  • Primary Key column(s) of the referenced table
CREATE TABLE SalesTbl
(
SPID number(6) PRIMARY KEY,
SPNAME varchar2(30),
SPCITYID number(4) ,
SPCOMM number(2,2),
CONSTRAINT fk_SPCID FOREIGN KEY(SPCITYID) references CityTbl(CITYID)
);

Points to Remember

  • You can create multiple Foreign Keys in one table. They can be defined individually for different columns linking with primary keys of different tables.
  • The name of the foreign key column in referring table and primary key column name of referenced table need not be the same but their data type must be same or conversion compatible.