Database developer can use the foreign key constraint to remove the inconsistency in two tables when the data in one table depends on the data in another table.
A foreign key constraint associates one or more columns (the foreign key) of a table with an identical set of columns (a primary key column) in another table on which a primary key constraint has been defined. The syntax of applying the foreign key constraint when creating table is:
CREATE TABLE table_name
(
Col_name [CONSTRAINT constraint_name FOREIGN KEY (col_name [, col_name [, …]])
REFERENCES table_name (column_name [, column_name [, …]])]
Col_name [, col_name [, col_name [, …]]])
Col_name [, col_name [, col_name [, …]]]
)
Where,
In the example of the EmployeeLeave table under the HumanResources schema, you need to add the foreign key constraint to enforce referential integrity. In the HumanResources schm,the EmployeeID column is set as a primary key in the Employee table. Therefore, you need to set EmployeeID in the EmployeeLeave table as a foreign key.
In the preceding example, you can use the following statement to apply the foreign key constraint in the EmployeeLeave table:
CREATE TABLE HumanResources.EmployeeLeave
(
EmployeeID int CONSTRAINT fkEmployeeID FOREIGN KEY REFERENCES
HumanResources.Employee (EmployeeID),
LeaveStartDate datetime CONSTRAINT cpkLeaveStartDate PRIMARY KEY (EmployeeID, LeaveStartDate) ,
…
…
…
)
The preceding statement creates the EmployeeLeave table with a foreign key constraint on the EmployeeID column. The name of the constraint is fkEmployeeID.
Unique key Constraints
A foreign key constraint associates one or more columns (the foreign key) of a table with an identical set of columns (a primary key column) in another table on which a primary key constraint has been defined. The syntax of applying the foreign key constraint when creating table is:
CREATE TABLE table_name
(
Col_name [CONSTRAINT constraint_name FOREIGN KEY (col_name [, col_name [, …]])
REFERENCES table_name (column_name [, column_name [, …]])]
Col_name [, col_name [, col_name [, …]]])
Col_name [, col_name [, col_name [, …]]]
)
Where,
- Constraint_name is the name of the constraint on which the foreign key constraint is to be defined.
- Col_name is the name of the column on which the foreign key constraint is to be enforced.
- Table_name is the name of the related table in which the primary key constraint has been specified.
- Column_name is the name of the primary key column of the related table on which the primary key constraint has been defined.
In the example of the EmployeeLeave table under the HumanResources schema, you need to add the foreign key constraint to enforce referential integrity. In the HumanResources schm,the EmployeeID column is set as a primary key in the Employee table. Therefore, you need to set EmployeeID in the EmployeeLeave table as a foreign key.
In the preceding example, you can use the following statement to apply the foreign key constraint in the EmployeeLeave table:
CREATE TABLE HumanResources.EmployeeLeave
(
EmployeeID int CONSTRAINT fkEmployeeID FOREIGN KEY REFERENCES
HumanResources.Employee (EmployeeID),
LeaveStartDate datetime CONSTRAINT cpkLeaveStartDate PRIMARY KEY (EmployeeID, LeaveStartDate) ,
…
…
…
)
The preceding statement creates the EmployeeLeave table with a foreign key constraint on the EmployeeID column. The name of the constraint is fkEmployeeID.
Unique key Constraints