Tech Point Fundamentals

Sunday, May 8, 2022

SQL Interview Questions And Answers - FOREIGN KEY

SQL Interview Questions And Answers - FOREIGN KEY

Foreign-Key-Interview-Questions

In SQL Foreign Key is the most important topic for both development and interview point of view as well. In this article, we will see some common interview questions which are based on the Foreign KeyFor experienced developers, there can be a lot of questions based on this topic. The interviewer may try to confuse you by asking the same question in a more tricky way.


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

SQL Unique Key Interview Questions and Answers


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




Introduction


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

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. 

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.


I will recommend to please read the previous Foreign Key Constraint article here first for more details about the Foreign Key.







Below are some Common Interview Questions based on the SQL Foreign Key Constraint:




Q01. What is the use of FOREIGN KEY constraints? What is the difference between Primary Key and Foreign Key Constraints?

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

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 generally references the Primary Key of the referenced table.


Q02. Can a Unique Key be referenced by a FOREIGN KEY Constraint?

Yes, a UNIQUE KEY column can also be referenced by the FOREIGN KEY Constraint.




Q03. How many Foreign Keys can be created in a table?

Multiple Foreign Keys are allowed in a table. There is no bar on the number of Foreign Key Constraints on a table but a max of 253 is recommended.


Q04. Can a FOREIGN KEY reference multiple columns of the Parent Table?

Yes, a Foreign Key Constraint can be a composite column as well.

-- 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)
);




Q05. Is NULL value allowed in the FOREIGN KEY column?

Yes. 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.


Q06. Can a FOREIGN KEY have more than one matching record in the parent table?

No, Because a Foreign Key always references a UNIQUE KEY or UNIQUE INDEX

But the reverse is true, the referenced Primary Key can have multiple matching foreign key records in the child table.




Q07. Can a FOREIGN KEY exist without the key name?

No, If you do not specify the Foreign Key Name, SQL will create a random name for it. In fact, duplicate key name is not allowed in SQL.

If you are providing a key name, which already exists, you will get an error:

Msg 2714, Level 16, State 5, Line 8
There is already an object named 'FK_EmployeeId_Delete' in the database.
Msg 1750, Level 16, State 0, Line 8
Could not create constraint or index. See previous errors.


Q08. Can you create a FOREIGN KEY on a Composite Primary Key?

Yes, a Foreign Key can be created on Composite Primary Key.




Q09. Can you create a Composite FOREIGN KEY?

Yes, if the referenced key column is a composite key.


Q10. Which type of index is created by default for the FOREIGN KEY Constraint?

No index is created by the Foreign Key by default. But if you want you can add an index to it.


Q11. Can a FOREIGN KEY contain duplicate values?

Yes




Q12. Can you create a FOREIGN KEY without referencing any other table? or Can a FOREIGN KEY reference a column of the same table?

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

CREATE TABLE SelfReferenceTable
(
EmpID   INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
EmpName VARCHAR(50) NOT NULL, 
ManagerEmpId INT NULL CONSTRAINT FK_ManagerEmpId FOREIGN KEY REFERENCES SelfReferenceTable(EmpID)  
);


Q13. Can a FOREIGN KEY reference the FOREIGN KEY column of other tables?

Yes, 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.




Q14. What is the use of the CASCADE Keyword? or What is CASCADE DELETE or UPDATE in SQL?

The FOREIGN KEY constraint provides 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. 

ALTER TABLE tmpEmployeeRole  WITH CHECK ADD CONSTRAINT FK_EmployeeId_Delete FOREIGN KEY(EmpId)
REFERENCES tblEmployee(ID) ON DELETE CASCADE;

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.




Q15. How many tables are required for creating a FOREIGN KEY constraint? or How can you create the FOREIGN KEY in a single table?

Generally, two tables are required for defining a Foreign Key Constraint, but a foreign key can be defined in a single table as well. This is known as Self-Referencing Foreign Key.


Q16. Can a FOREIGN KEY reference the Non-KEY column of the parent table?

No, A Foreign Key requires Primary Key, Unique Key, or Unique Index column for reference.




Q17. What is the use of "WITH CHECK" and "WITH NOCHECK" Clause in the FOREIGN KEY Constraint?

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);


Q18. How can you create a FOREIGN KEY Constraint after creating a table?

We can use the ALTER TABLE command to add or modify the FK Constraint.

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




Q19. Are duplicate Key Names allowed in the database?

No, If you try to add a key with an existing key name you will get an error:

Msg 2714, Level 16, State 5, Line 8
There is already an object named 'FK_EmployeeId_Delete' in the database.
Msg 1750, Level 16, State 0, Line 8
Could not create constraint or index. See previous errors.


Q20. What is a Trusted and Non-Trusted referential integrity constraint?

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.

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.

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.




Q21. Can a FOREIGN KEY column contain NULL values?

Yes. We know 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.


Q22. How many NULL values are allowed in a FOREIGN KEY column?

If the FK is nullable, we can add multiple NULL values.


Q23. Can a FOREIGN KEY Contain an empty(blank) value or whitespace?

Yes, blank value is allowed in the foreign key as well.



Q24. Can you make the FOREIGN KEY column an Identity Column?

Yes, a foreign key can also be an Identity Column.

Q25. Can you create an index on the FOREIGN KEY column?

Yes, an index can be created on the foreign key as well.

Q26. How can you delete or update the records referenced by the FOREIGN KEY constraint?

Actually, first, we have to update or delete the child table record then the primary table record or first we have to disable the Foreign Key constraint then, we can modify the parent table record.




Q27. Can you disable the FOREIGN KEY constraint?

Yes. We can disable and enable the Foreign Key Constraints.

ALTER TABLE tblUserRole NOCHECK CONSTRAINT FK_UserId;  

Again if we want, we can enable it again by the below command:

ALTER TABLE tblUserRole CHECK CONSTRAINT FK_UserId; 


Q28. What is the use of the 'NOT FOR REPELCIATION' option in the FOREIGN KEY constraint?

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.




Q29. Can you add FOREIGN KEY REFERENCES on the Temp Table?

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.




Q30. Can you add multiple FOREIGN KEY REFERENCES on the same column?

Yes, multiple foreign keys are allowed on the same column as well.

Q31. Can you modify the existing FOREIGN KEY REFERENCES?

Yes, we can modify the existing foreign key by ALTER command.

Q32. Can you create a FOREIGN KEY constraint on the computed column?

Yes. 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.



Q33. Can you reference a column in the FOREIGN KEY which is neither Primary Key nor Unique Key but has a Unique Index?

Yes, a column with Unique Index can be referenced by the foreign key.


Q34. Can you create a FOREIGN KEY constraint without referencing any column name?

Yes, 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.

CREATE TABLE tblUserRole
(
RoleID   INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
RoleName VARCHAR(50) NOT NULL, 
EmpId INT NULL CONSTRAINT FK_ManagerEmpId FOREIGN KEY REFERENCES tblUser  
);




Q35. Can a FOREIGN KEY REFERENCES across the database table? or Can you reference an external database table for creating the FOREIGN KEY REFERENCES?

No, 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 ConstraintCross-Database Referential Integrity must be implemented through Triggers or CHECK Constraint.



Q36. What will happen if a Foreign Key column has multiple foreign key constraints with different CASCADE actions that reference the same column?

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:

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.


Q37. What is the difference between INSERT INTO and BULK INSERT when you have to add the data into a table which has FOREIGN KEY REFERENCES?

Actually, when you use INSERT INTO for record insert, the Foreign Key Constraint violation is always triggered and checked which causes performance overheads.

On the other hand, the BULK INSERT provides an option to skip this FK check for each and every record insert. 

In the BULK INSERT command, the CHECK_CONSTRAINTS specifies that all constraints on the target table or view must be checked during the bulk-import operation. Without the CHECK_CONSTRAINTS option, any CHECK and FOREIGN KEY constraints are ignored, and after the operation, the constraint on the table is marked as not-trusted.

Please read more here.




Q38. Can you DROP the UNIQUE INDEX of the column that is referenced by the FOREIGN KEY Constraint?

No, First you have to drop the FK constraint, only then the referenced index can be deleted. 


Q39. Can you add a FOREIGN KEY constraint on the varchar(max) column?

A column of type varchar(max) can participate in a FOREIGN KEY constraint only if the primary key it references is also defined as type varchar(max).




Q40. How do the FOREIGN KEY constraints maintain the referential integrity of the database?

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.

  • 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.
  • 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.
  • FOREIGN KEY constraint stops you from deleting a record from the referenced table.
  • FOREIGN KEY constraint stops you from creating orphan records by making sure that the child record is always deleted first.



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 and Answers
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, Youtube, and Quora for regular updates.

No comments:

Post a Comment

Please do not enter any HTML. JavaScript or spam link in the comment box.