Link for all dot net and sql server video tutorial playlists
Link for slides, code samples and text version of the video
Can we join two tables without primary foreign key relation
Yes, we can join two tables without primary foreign key relation as long as the column values involved in the join can be converted to one type.
ID column in Departments table is not the primary Key and DepartmentId column in Employees table is not the foreign key. But we can still join these tables using ID column from Departments table and DepartmentId column from Employees table, as both the columns involved in the join have same data type i.e int.
Select Employees.Name as EmployeeName, Departments.Name as DepartmentName
from Employees
join Departments on Departments.ID = Employees.DepartmentId
The obious next question is, if primary foreign key relation is not mandatory for 2 tables to be joined then what is the use of these keys?
Primary key enforces uniqueness of values over one or more columns. Since ID is not a primary key in Departments table, 2 or more departments may end up having same ID value, which makes it impossible to distinguish between them based on the ID column value.
Foreign key enforces referential integrity. Without foreign key constraint on DepartmentId column in Employees table, it is possible to insert a row into Employees table with a value for DepartmentId column that does not exist in Departments table.
The following insert statement, successfully inserts a new Employee into Employees table whose DepartmentId is 100. But we don’t have a department with ID = 100 in Departments table. This means this employee row is an orphan row, and the referential integrity is lost as result
Insert into Employees values (8, ‘Mary’, ‘Female’, 80000, 100)
If we have had a foreign key constraint on DepartmentId column in Employees table, the following insert statement would have failed with the following error.
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint. The conflict occurred in database “Sample”, table “dbo.Departments”, column ‘ID’.
