xxxxxxxxxx
Foreign Key is a non-key attribute which is derived from the primary key
of another table which links those tables together.
xxxxxxxxxx
CREATE TABLE Employee(
EmployeeID int NOT NULL,
LastName varchar(50) NOT NULL,
FirstName varchar(20) NOT NULL,
Age int,
DeptNo int,
PRIMARY KEY (EmployeeID),
FOREIGN KEY (DeptNo) REFERENCES Department(DeptNo)
);
xxxxxxxxxx
CREATE TABLE Employee(
EmployeeID int NOT NULL,
LastName varchar(50) NOT NULL,
FirstName varchar(20) NOT NULL,
Age int,
DeptNo int,
PRIMARY KEY (EmployeeID),
FOREIGN KEY (DeptNo) REFERENCES Department(DeptNo)
);
xxxxxxxxxx
USE Organization
CREATE TABLE Employee
(
Id INT PRIMARY KEY IDENTITY(1,1),
Name VARCHAR (50) NOT NULL,
Age INT,
Gender VARCHAR (50),
Dep_Id int FOREIGN KEY REFERENCES Department(Id),
Insur_Id int FOREIGN KEY REFERENCES Insurance(Id)
)
xxxxxxxxxx
Foreign Key:
It is a column that comes from a different table and
using Foreign key tables are related each other
It is the primary key of another table
It can be duplicate or null for another table
Primary Key :
It is unique column in every table in a database
It can ONLY accept;
- nonduplicate values
- cannot be NULL
Unique Key:
Only unique value and also can contain NULL
xxxxxxxxxx
ALTER TABLE your_table ADD FOREIGN KEY (your_column) REFERENCES other_table(other_column);
xxxxxxxxxx
USE Organization
CREATE TABLE Employee_Office
(
Id INT PRIMARY KEY IDENTITY(1,1),
Emp_Id int FOREIGN KEY REFERENCES Employee(Id),
Office_Id int FOREIGN KEY REFERENCES Office(Id)
)
xxxxxxxxxx
Foreign Key:
It is a column that comes from a different table and
using Foreign key tables are related each other
It is the primary key of another table
It can be duplicate or null for another table