Is it possible to create a Foreign key without Primary key?

Yes. We can create a Foreign key without having Primary key in the parent table.

Most of us are in a misconception that we can not create a Foreign key without primary key in the parent table. Also most of the books on SQL explains the foreign keys with primary key in the parent table. So people think that creation of foreign key is possible only with primary key. But in reality, for creating Foreign key we should have a unique id in the parent table. That unique id can be primary key or unique key.

Please look at the below example to understand better.

Ex:

It is valid. 

————————————————————————-
— Parent Table with Primary key in it.
————————————————————————-
CREATE TABLE Parent_Table
(
P_ID INT NOT NULL PRIMARY KEY,
P_Name VARCHAR(100) NOT NULL
)
GO
————————————————————————-
— Child Table with Primary Key in Parent table.
————————————————————————-
CREATE TABLE Child_Table
(
C_ID INT NOT NULL PRIMARY KEY,
C_Name VARCHAR(100) NOT NULL,
CONSTRAINT fk_PC FOREIGN KEY (C_ID)
REFERENCES Parent_Table(P_ID)
)
GO
————————————————————————-

It is valid.

————————————————————————-
— Parent Table with Unique key in it.
————————————————————————-
CREATE TABLE Parent_Table
(
P_ID INT NOT NULL UNIQUE,
P_Name VARCHAR(100) NOT NULL
)
GO
————————————————————————-
— Child Table with Unique Key in Parent table.
————————————————————————-
CREATE TABLE Child_Table
(
C_ID INT NOT NULL PRIMARY KEY,
C_Name VARCHAR(100) NOT NULL,
CONSTRAINT fk_PC FOREIGN KEY (C_ID)
REFERENCES Parent_Table(P_ID)
)
GO
————————————————————————-

It is Invalid.

————————————————————————-
— Parent Table without Primary/Unique key in it.
————————————————————————-
CREATE TABLE Parent_Table
(
P_ID INT NOT NULL,
P_Name VARCHAR(100) NOT NULL
)
GO
————————————————————————-
— Child Table without Primary/Unique key in Parent table.
————————————————————————-
CREATE TABLE Child_Table
(
C_ID INT NOT NULL PRIMARY KEY,
C_Name VARCHAR(100) NOT NULL,
CONSTRAINT fk_PC FOREIGN KEY (C_ID)
REFERENCES Parent_Table(P_ID)
)
GO
————————————————————————-

Error:

Msg 1776, Level 16, State 0, Line 17
There are no primary or candidate keys in the referenced table ‘Parent_Table’ that match the referencing column list in the foreign key ‘fk_PC’.
Msg 1750, Level 16, State 0, Line 17
Could not create constraint. See previous errors.

Foreign-key-without-unique-key

Happy Reading :)

Leave a Reply

Your email address will not be published. Required fields are marked *