Advanced SQL tutorial pdf, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Free Download SQL SERVER Interview questions, Resource Database, SQL, SQL Common Interview Questions, SQL Common Interview Questions and answers, SQL FAQ, SQL FAQs, SQL Interview Q & A, SQL Interview Questions, SQL Queries asked in interviews, SQL Questions, SQL Server - General Interview Questions and Answers, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL SERVER Indexes, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, sql server interview questions and answers for net developers, SQL Server Resource Database, SQL SERVER Tips, SQL Tips & Tricks, SQL Tips and Tricks, SQL Tricks, T-SQL Server Interview Questions
SQL SERVER – Why would a table use its primary key as a foreign key to itself ?
There have been cases where you have to create a reference of a foreign key to a primary key of the same table. Well it is typically used to build a hierarchy structure. This scenario is not very common.
Example: Parent – Manager Hierarchical Problem
-- CREATE TABLE EmployeeDetails ( EmployeeId INT PRIMARY KEY ,EmployeeName VARCHAR(50) ,ManagerId INT ) ALTER TABLE EmployeeDetails ADD CONSTRAINT fk_employee FOREIGN KEY (ManagerId) REFERENCES EmployeeDetails(EmployeeId) --
In the above case Foreign Key (ManagerId) from the table back to itself means all managers are also employees hence the ManagerId is actually an EmployeeId. It just that he is working as a manager in the company. Now let’s discuss some issues we face while inserting and deleting data from these kind of data structures.
INSERT CASE PROBLEM
Now in this type of scenario you cannot insert data directly. Check out the first example below-
-- INSERT INTO EmployeeDetails VALUES ( 1 , 'Pawan' , 2 ) --
Now if you try to insert the above row in the table it will return an error (Foreign Key Constraint). That’s because we don’t have an employee with Employee Id = 2. Check out the error below-
Msg 547, Level 16, State 0, Line 19
The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint “fk_employee”. The conflict occurred in database “Pawan”, table “dbo.EmployeeDetails”, column ‘EmployeeId’.
The statement has been terminated.
In this case you have to insert employee and their manager information together.
-- INSERT INTO EmployeeDetails VALUES ( 1 , 'Pawan' , 2 ) , ( 2 , 'Isha' , NULL ) --
In the above case Employee ID = 1 and Employee ID = 2 are inserted together that’s why we didn’t get any foreign key constraint errors and records get inserted successfully. Now if you have a scenario where two employees are reporting to each other then also we can use the above logic. Check the example below.
-- INSERT INTO EmployeeDetails VALUES ( 3 , 'Ramesh' , 4 ) , ( 4 , 'Kangana' , 3 ) --
Table ‘EmployeeDetails’. Scan count 0, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(2 row(s) affected)
DELETE CASE PROBLEM
In delete case you have to delete the values together if there is a foreign key reference. If you try to delete the single entity you will get an error. Check out the example below-
-- DELETE FROM EmployeeDetails WHERE EmployeeId = 1 OR EmployeeId = 2 --
I hope you have enjoyed the tricks we use in these scenarios. Cheers, Thanks for reading !