When we create a Referential integrity, Parent column can be a PRIMARY KEY or UNIQUE column!
OK.
1. Crete a Parent Table
CREATE TABLE MasterTable1
(
Id INT IDENTITY(1,1) PRIMARY KEY,
Column1 VARCHAR(10)
)
GO
2. Create a Child table & Refer MasterTable1
CREATE TABLE ChildTable1
(
Id INT IDENTITY(1,1) PRIMARY KEY,
MasterID INT FOREIGN KEY REFERENCES MasterTable1,
Column2 VARCHAR(10)
)
GO
So, The above statement uses only the Parent Table name. But, not Primary Key column.
By default, It refers the PRIMARY KEY column of the reference table(MasterTable1). So, no need to give the PRIMARY KEY column name explicitly.
But, Have you tried with UNIQUE key for the same scenario ?
1. Crete a Parent Table
CREATE TABLE MasterTable1
(
Id INT IDENTITY(1,1) UNIQUE,
Column1 VARCHAR(10)
)
GO
2. Create a Child table & Refer MasterTable1
CREATE TABLE ChildTable1
(
Id INT IDENTITY(1,1) PRIMARY KEY,
MasterID INT FOREIGN KEY REFERENCES MasterTable1,
Column2 VARCHAR(10)
)
GO
You will get an Err message!
Msg 1773, Level 16, State 0, Line 1
Foreign key 'FK__ChildTabl__Maste__658C0CBD' has implicit reference to object 'MasterTable1' which does not have a primary key defined on it.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
So, when referring an UNIQUE column, It should be TableName(ColumnName)
CREATE TABLE ChildTable1
(
Id INT IDENTITY(1,1) PRIMARY KEY,
MasterID INT FOREIGN KEY REFERENCES MasterTable1(Id),
Column2 VARCHAR(10)
)
GO
So, Using TableName(columnName) is mandatory when referring an UNIQUE key column!!!
OK.
1. Crete a Parent Table
CREATE TABLE MasterTable1
(
Id INT IDENTITY(1,1) PRIMARY KEY,
Column1 VARCHAR(10)
)
GO
2. Create a Child table & Refer MasterTable1
CREATE TABLE ChildTable1
(
Id INT IDENTITY(1,1) PRIMARY KEY,
MasterID INT FOREIGN KEY REFERENCES MasterTable1,
Column2 VARCHAR(10)
)
GO
So, The above statement uses only the Parent Table name. But, not Primary Key column.
By default, It refers the PRIMARY KEY column of the reference table(MasterTable1). So, no need to give the PRIMARY KEY column name explicitly.
But, Have you tried with UNIQUE key for the same scenario ?
1. Crete a Parent Table
CREATE TABLE MasterTable1
(
Id INT IDENTITY(1,1) UNIQUE,
Column1 VARCHAR(10)
)
GO
2. Create a Child table & Refer MasterTable1
CREATE TABLE ChildTable1
(
Id INT IDENTITY(1,1) PRIMARY KEY,
MasterID INT FOREIGN KEY REFERENCES MasterTable1,
Column2 VARCHAR(10)
)
GO
You will get an Err message!
Msg 1773, Level 16, State 0, Line 1
Foreign key 'FK__ChildTabl__Maste__658C0CBD' has implicit reference to object 'MasterTable1' which does not have a primary key defined on it.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
So, when referring an UNIQUE column, It should be TableName(ColumnName)
CREATE TABLE ChildTable1
(
Id INT IDENTITY(1,1) PRIMARY KEY,
MasterID INT FOREIGN KEY REFERENCES MasterTable1(Id),
Column2 VARCHAR(10)
)
GO
So, Using TableName(columnName) is mandatory when referring an UNIQUE key column!!!
No comments:
Post a Comment