SQL FOREIGN KEY Constraint

SQL FOREIGN KEY Constraint

The FOREIGN KEY is also an important key factor for any table or schema design in SQL. Keeping this in mind the interviewer can ask the different questions from the Foreign Key as well. In this article, we will walk through the FOREIGN KEY with examples and related interview questions as well.

I will highly recommend you to please read the previous Primary Key Constraint and Unique Key Constraint articles before continuing this article here:

SQL Primary Key Constraint and Misconceptions

SQL Unique Key Constraint and Misconceptions

Please visit our YouTube Channel for SQL Interview Questions and Answers videos by the below link:

Watch SQL Interview Questions and Answers videos here.

Introduction

In the database, a key consists of one or more columns of a table that have specific properties. So when you define a table, you specify the key. There are three different keys known as Primary Key, Unique Key, and Foreign Key. They are also known as respective constraints as well. In this article, we will focus on FOREIGN KEY in detail.

SQL Constraints

In SQL, Constraints are the rules that the database enforces to improve data integrity. They are also used to specify the business rules for data in a table.

SQL Constraints are used to restrict the type of data that can be inserted into a table. By doing so, it ensures the accuracy and reliability of the data in the table.

If there is any violation between the defined constraint and the data to be inserted, updated, or deleted, the respective action is aborted or terminated by the SQL.

A user can define constraints either at column level or table level. The column-level constraints apply only to a column, while table-level constraints apply to the whole table.

SQL Constraints can be specified when the table is created by using CREATE TABLE statement, or after the table is created by using ALTER TABLE statement.

SQL FOREIGN KEY Constraint

A FOREIGN KEY is basically used to create a relationship between two tables. This constraint prevents actions that would destroy links between the tables.

A FOREIGN KEY is a constraint that enforces Referential Integrity in the SQL Server Database. They always enforce and ensure that the referential integrity is preserved. 

A Foreign Key Constraint prevents you from inserting or updating values that aren’t found in the related reference table's column. It also prevents deleting the records from the parent table.

-- Primary or Parent Table

CREATE TABLE  tblEmployee

(

ID INT NOT NULL PRIMARY KEY,  

NAME VARCHAR(250) NOT NULL

);

-- Secondary or Child Table

CREATE TABLE tblUserRole

(

Id INT IDENTITY(1,1) PRIMARY KEY,

        RoleName VARCHAR(20),

UserId INT CONSTRAINT FK_UserId FOREIGN KEY REFERENCES tblEmployee(ID),

);

A FOREIGN KEY can be a set of one or more than one column as well. In that case, it is known as Composite Foreign Key.

-- Composite Primary Key Column

CREATE TABLE  tmpCustomerProductOrder

(

Customer_ID INT NOT NULL,

Product_ID INT NOT NULL,

Order_Quantity INT NOT NULL,

Constraint CPK_CID_PID PRIMARY KEY (Customer_ID, Product_ID)

);

-- Composite Foreign Key Column

CREATE TABLE  tmpCustomerProductOrderDelivery

(

Id INT IDENTITY(1,1) PRIMARY KEY,

Customer_ID INT NOT NULL,

Product_ID INT NOT NULL,

LogisticName VARCHAR(250) NOT NULL,

Constraint CFK_CID_PID FOREIGN KEY(Customer_ID, Product_ID) REFERENCES tmpCustomerProductOrder(Customer_ID, Product_ID)

);

If the table is already created you can add it by the ALTER TABLE command:

ALTER TABLE tblUserRole  WITH CHECK ADD CONSTRAINT FK_UserId FOREIGN KEY(UserId) REFERENCES tblEmployee(ID);

A table can contain multiple foreign keys. In fact, a single column can have multiple Foreign Key Constraints as well.

How Foreign Key Preserves the Referential Integrity of the Database?

A Foreign Key is a constraint that enforces referential integrity in the SQL Database. The Referential Integrity says that if column value A refers to column value B, then column value B must exist.

The relationship between the child and the parent tables is maintained by checking the existence of the child table FOREIGN KEY values in the referenced table. So Referential Integrity ensures that the relationship between the database tables is preserved during the data insertion/updation/deletion process.

• A FOREIGN KEY constraint stops you from entering a value that is not found in the referenced column of the referenced table i.e stops you from adding a foreign key value that doesn’t exist in the primary key or unique key or unique index column.
• A FOREIGN KEY constraint stops you from changing the foreign key value to one which doesn’t exist as a value in the referenced table’s primary key.
• A FOREIGN KEY constraint stops you from deleting a record from the referenced table.
• A FOREIGN KEY constraint stops you from creating orphan records by making sure that the child record is always deleted first.

So when a value other than NULL is entered in the column of a FOREIGN KEY constraint, the value must exist in the Referenced Column. Otherwise, a Foreign Key violation error message is returned. 

Trusted vs. NonTrusted Referential Integrity Constraint

There are two types of Referential Integrity Constraints, hence two different ways to add the Foreign Key also.

When you add the FK Constraint by the "WITH CHECK" option it creates a Trusted Foreign Key Constraint because it always checks for any Foreign Key violation before actually creating the Foreign Key Constraint:

ALTER TABLE tblUserRole  WITH CHECK ADD CONSTRAINT FK_UserId FOREIGN KEY(UserId) REFERENCES tblEmployee(ID);

On the other hand, if you add the Foreign Key Constraint by the "WITH NO CHECK" option it creates a NonTrusted Foreign Key Constraint because it never checks for any FK violation before actually creating the Foreign Key constraint:

ALTER TABLE tblUserRole  WITH NOCHECK ADD CONSTRAINT FK_UserId FOREIGN KEY(UserId) REFERENCES tblEmployee(ID);

It will also be a NonTrusted Foreign Key when you disabled the Foreign Key Constraint because by doing so SQL Server internally flag the constraint as not being "trustworthy". So by disabling it, SQL will not check for the FK violation anymore.

You can disable a foreign key constraint during INSERT and UPDATE transactions in SQL Server. After you disable these constraints, future inserts or updates to the column will not be validated against the constraint conditions.

ALTER TABLE tblUserRole NOCHECK CONSTRAINT FK_UserId;  

Again when you want, you can enable it again by the below command:

ALTER TABLE tblUserRole CHECK CONSTRAINT FK_UserId; 

Similarly, one more option is there i.e  "NOT FOR REPELCIATION" option. When a Foreign Key is set “NOT FOR REPELCIATION”, the validation is done only when the user inserts, deletes, or updates data. The validation is not fired when the replication agents sync the changes made by the user to other ends.

ALTER TABLE tmpEmployeeRole WITH CHECK ADD CONSTRAINT FK_RoleIdRep FOREIGN KEY(RoleId) REFERENCES tblEmployee (Id) NOT FOR REPLICATION;

By default, the constraint is marked as not trusted for this and it is only available on SQL Server 2008 and later versions.

FOREIGN KEY Cascading Effects

We know that if a table column is referenced by the Foreign Key any modification will not be possible in the parent table until it is done in the child table. So SQL provides CASCADE Operations for Foreign Key to do the same.

ALTER TABLE tmpEmployeeRole  WITH CHECK ADD CONSTRAINT FK_EmployeeId_Delete FOREIGN KEY(EmpId)

REFERENCES tblEmployee(ID) ON DELETE CASCADE;

The FOREIGN KEY constraint provides you the ability to control what action will be taken when the referenced value in the parent table is updated or deleted, using the ON UPDATE and ON DELETE clauses. 

The supported actions that can be taken when deleting or updating the parent table’s values include:

NO ACTION: When the ON UPDATE or ON DELETE clauses are set to NO ACTION, the performed update or delete operation in the parent table will fail with an error.

CASCADE: Setting the ON UPDATE or ON DELETE clauses to CASCADE, the same action performed on the referenced values of the parent table will be reflected the related values in the child table. For example, if the referenced value is deleted in the parent table, all related rows in the child table are also deleted.

SET NULL: With this ON UPDATE and ON DELETE clauses option, if the referenced values in the parent table are deleted or modified, all related values in the child table are set to a NULL Value.

SET DEFAULT: Using the SET DEFAULT option of the ON UPDATE and ON DELETE clauses specify that, if the referenced values in the parent table are updated or deleted, the related values in the child table with FOREIGN KEY columns will be set to their Default Value.

But different CASCADE Actions for the same type (UPDATE or DELETE) are not allowed. If you try to add multiple FK constraints with different CASCADE Actions for the same operation, you will get an error. 

ALTER TABLE tmpEmployeeRole  WITH CHECK ADD CONSTRAINT FK_EmployeeId_UpdateNULL FOREIGN KEY(EmpId)

REFERENCES tblEmployee(ID) ON UPDATE SET NULL;

If you try to add the above FK on the table you will get below error:

Msg 1785, Level 16, State 0, Line 8

Introducing FOREIGN KEY constraint 'FK_EmployeeId_UpdateNULL' on table 'tmpEmployeeRole' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Msg 1750, Level 16, State 0, Line 8

Could not create constraint or index. See previous errors.

FOREIGN KEY Fundamental Points

1. There can be more than one FOREIGN KEY Constraint in any table. In fact, more than one Foreign Key is allowed on the same columns as well. But there can be only one Primary Key in any table. If you do not specify the name of the constraint, the SQL server creates a foreign key constraint with a random name.

The SQL Database Engine doesn`t limit the number of FOREIGN KEY constraints owned by other tables that reference a specific table or the number of Foreign Key constraints that a table can have. But max 253 is recommended count.

2. A Foreign Key is a constraint that enforces referential integrity in the SQL Database. The Referential Integrity says that if column value A refers to column value B, then column value B must exist.

3. To create a SQL Foreign Key Constraint, the parent table should have either a PRIMARY KEYcolumn or UNIQUE KEY column or a Column with UNIQUE INDEX. Otherwise, you cannot reference any non-key column.

4. A FOREIGN KEY constraint specified at the column level can list only one reference column. This column must have the same data type as the column on which the constraint is defined.

5. A FOREIGN KEY constraint specified at the table level must have the same number of reference columns as the number of columns in the constraint column list. The data type of each reference column must also be the same as the corresponding column in the column list.

6. A Foreign Key can also be a Composite Foreign Key. It is important to note that all the columns in a single foreign key must point to the same table. So it is not possible to have a foreign key that references to a column in Table 1 and a column in Table 2.

7. A FOREIGN KEY Constraint cannot be enforced on Temporary Tables (both local and global). If you add the FOREIGN KEY on the Temp Table you will get a warning, but the table will be created successfully but FK Constraint will not be created along with the table.

Skipping FOREIGN KEY constraint 'FK_UserId' definition for temporary table. FOREIGN KEY constraints are not enforced on local or global temporary tables.

Msg 1766, Level 16, State 0, Line 26

Foreign key references to temporary tables are not supported. Foreign key 'FK_EmpId'.

Msg 1750, Level 16, State 0, Line 26

Could not create constraint or index. See previous errors.

There is one more scenario of this Temp Table and Foreign Key as well. A permanent table cannot reference any Temp Table for defining any Foreign Key constraint also.

If you try to do that you will get an error:

Msg 1766, Level 16, State 0, Line 9

Foreign key references to temporary tables are not supported. Foreign key 'FK_EmpId'.

Msg 1750, Level 16, State 0, Line 9

Could not create constraint or index. See previous errors.

8. A  FOREIGN KEY constraint cannot be added on Table-Valued Type as well. 

CREATE TYPE Employee_TVP AS TABLE 

(

[Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,

[Name] [varchar](50) NOT NULL,

[MangerId] INT NULL CONSTRAINT FK_ManagerId FOREIGN KEY              REFERENCES Employee_TVP(Id)

);

If you try to add it, you will get an error:

Msg 156, Level 15, State 1, Line 5

Incorrect syntax near the keyword 'FOREIGN'.

Please watch the Table-Valued Type video here for more details.

9. A  FOREIGN KEY constraint cannot be added on Table Variable as well. 

DECLARE  @EmployeeTableVariable TABLE

(

[Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,

[Name] [varchar](50) NOT NULL,

[MangerId] INT NULL CONSTRAINT FK_ManagerId FOREIGN KEY REFERENCES @EmployeeTableVariable(Id)

);

If you try to add it, you will get an error:

Msg 102, Level 15, State 1, Line 5

Incorrect syntax near '@EmployeeTableVariable'.

Please watch the Table Variable video here for more details.

10. A FOREIGN KEY constraint can reference only tables within the same database on the same server.  If you try to do that you will get an error:

Msg 1763, Level 16, State 0, Line 2

Cross-database foreign key references are not supported. Foreign key 'TechPointFunda.dbo.Role'.

Msg 1750, Level 16, State 0, Line 2

Could not create constraint or index. See previous errors.

So for ensuring referential integrity in Cross-Database you can use either Trigger or CHECK Constraint. Cross-Database Referential Integrity must be implemented through Triggers or CHECK Constraint.

You can also create a function and then use it in the CHECK Constraint. If the function is being referenced by the CHECK Constraint,  no one can DROP that function at all.

Common Misconceptions About FOREIGN KEY

There are some misconceptions about the FOREIGN KEY as well among the developers and the interviewee. We will some common misconceptions here.

Misconception 1: DEFINING A FOREIGN KEY ALWAYS REQUIRES AT LEAST TWO TABLES

One of the common misconceptions about Foreign Key is that a FOREIGN KEY always requires at least two tables for defining the FK constraint i.e parent and child table. But it is not always true.

A Foreign Key Constraint can be defined in a single self table as well. This is known as a self-reference.

If you remember my SQL SELF JOIN video where I have explained, how you will fetch the data from this type of table. If not please feel free to watch it here now.

Misconception 2: A FOREIGN KEY ONLY REFERENCE THE PRIMARY KEY COLUMN OF THE PARENT TABLE

This is also one of the famous misconceptions that a FOREIGN KEY can only reference the PRIMARY KEY columns of the parent table. But it is not always true.

A Foreign Key constraint can reference the UNIQUE KEY column also for specifying the FK Constraint. Please read more about the UNIQUE KEY here.

Not even this, but a Foreign Key constraint can also reference the UNIQUE INDEX column as well for specifying the FK Constraint.

Please watch the SQL UNIQUE Index video here for more details.

Misconception 3: IT IS ALWAYS REQUIRED TO SPECIFY THE REFERENCED COLUMN NAME WHILE DEFINING THE FOREIGN KEY CONSTRAINT

There is one more misconception about Foreign Key that it is mandatory to specify the referencing column name while defining the FOREIGN  KEY Constraint. But it is also not true always.

You can skip the referenced column name as well. You just have to specify the referencing table name only for creating the Foreign Key constraint. In this case, the SQL will reference the Primary Key of the referenced table implicitly.

But the referencing table must have Primary Key defined. If the referenced table does not have any Primary Key defined, you will get an error:

Msg 1773, Level 16, State 0, Line 7

Foreign key 'FK_EmpId' has implicit reference to object 'tblUser' which does not have a primary key defined on it.

Msg 1750, Level 16, State 0, Line 7

Could not create constraint or index. See previous errors.

But if you want to add a Foreign Key on a UNIQUE KEY column or UNIQUE INDEX column you must have to specify the column name for defining the FK Constraint. 

Misconception 4: FOREIGN KEY CAN NOT REFERENCE A FOREIGN KEY COLUMN

One of the common misconceptions about Foreign Key is that a FOREIGN KEY never references another FOREIGN KEY column. But it is not true actually.

Actually, when you reference an existing FOREIGN KEY column for the FK Constraint, you will get the below error:

Msg 1776, Level 16, State 0, Line 12

There are no primary or candidate keys in the referenced table 'tblEmployeeRole' that match the referencing column list in the foreign key 'FK_VerifiedEmpId'.

Msg 1750, Level 16, State 0, Line 12

Could not create constraint or index. See previous errors.

But if you add a UNIQUE INDEX on the referencing Foreign Key column, you will be able to reference that in the FK constraint.

Please watch the SQL UNIQUE INDEX video here for more details.

Misconception 5: FOREIGN KEY NEVER ALLOWS NULL VALUES

This is also a misconception that a FOREIGN KEY never allows NULL Values in the respective column. But it is not true.

We have seen that a Foreign Key can reference a UNIQUE KEY and UNIQUE INDEX column as well and they can hold the NULL value so a Foreign Key Constraint as well.

On the other hand, a FOREIGN KEY Constraint can be created on the nullable column as well. So a Foreign Key referencing Primary Key column can also hold NULL value.

Please watch the SQL NULL Value video here for more details.

Misconception 6: FOREIGN KEY CAN NOT BE CREATED ON COMPUTED COLUMN

One more misconception about Foreign Key is that a FOREIGN  KEY cannot be created on Computed Column. But it is not true actually.

A Foreign Key Constraint can be added on the Computed Column as well but the condition is it must be specified as persisted. If you do not specify the "PERSISTED" keyword, you will get an error:

Msg 8183, Level 16, State 1, Line 6

Only UNIQUE or PRIMARY KEY constraints can be created on computed columns, while CHECK, FOREIGN KEY, and NOT NULL constraints require that computed columns be persisted.

Basically, the Persisted Keyword specifies that the Database Engine will physically store the computed values in the table, and update the values when any other columns on which the computed column depends are updated. 

So marking a computed column as PERSISTED allows an index to be created on a computed column that is deterministic, but not precise.

Misconception 7: FOREIGN KEY ALSO CREATE AN INDEX INTERNALLY 

One misconception about Foreign Key is that a FOREIGN KEY also creates a Non-Clustered Index internally same as Primary Key and Unique Key. But it is not true at all.

Actually, the Foreign Key does not create any index internally. But you can add an index on the Foreign Key column explicitly if required. You can check about the index and constraint by below SQL Query:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME='table-name';

EXEC sys.sp_helpconstraint 'table-name', 'nomsg';

EXEC sp_helpindex 'table-name';

You can see that the sp_helpindex procedure is returning nothing here.

Misconception 8: FOREIGN KEY COLUMN CAN NOT BE AN IDENTITY COLUMN

This is also a misconception that a FOREIGN KEY column cannot be Identity Column. But it is not true.

Actually making a Foreign Key column as Identity Column never causes any error or syntax error. You can make a foreign key as an Identity column.

But you will get an FK violation error if a record is inserted into the child table and the corresponding matching record is not found in the parent table(tblEmployee).  

For example, if you insert the below record you will get an error:

INSERT INTO FK_IdentityDemo(EMP_NAME) VALUES ('Peter');

Msg 547, Level 16, State 0, Line 13

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__FK_Identi__EMP_I__53E25DCE". The conflict occurred in database "TechPoint", table "dbo.tblEmployee", column 'Id'.

The statement has been terminated.

So there is no actual mean of this kind of FK, but syntactically there is no issue making a Foreign Key an Identity Column.

Misconception 9: AN INDEX CAN NOT BE CREATED ON FOREIGN KEY COLUMN

This is also a misconception that an explicit index cannot be created on the Foreign Key column. But it is not true.

It is true that a Foreign Key does not create any index internally. But it does not mean that we cannot create an index on it. If required you can add an index on the Foreign Key column as well.

Please watch the Clustered vs Non-Clustered Index video here for more details.

Conclusion

A Foreign Key is a constraint that enforces referential integrity in the SQL Database. It cannot be created on Temp Tables. Multiple Foreign Keys are allowed. We can also add an index on the FK column. It can reference the Primary Key column, Unique Key Column, or Unique Index column.

Live Demo

Recommended Articles

Most Frequently Asked SQL Interview Questions
Most Frequently Asked C# Interview Questions
Foreign Key Interview Questions
Unique Key Interview Questions
Primary Key Interview Questions
Derived Table (DT) Interview Questions
Unicode vs Non-Unicode Data Types Interview Questions
Table Variable (TV) Interview Questions
Table-Valued Type (TVT) Interview Questions
Common Table Expression(CTE) Interview Questions
Temp Table Interview Questions
Static Class Interview Questions and Answers
Sealed Class Interview Questions and Answers
Sealed Method Interview Questions and Answers
Abstract Class vs Interface
Interface Interview Questions and Answers

Thanks for visiting this page. Please follow us on Twitter, Facebook, LinkedIn, Telegram, WhatsApp, Youtube, and Quora for regular updates.