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
# A foreign key is essentially a reference to a primary
# key in another table.
# A Simple table of Users,
CREATE TABLE users(
userId INT NOT NULL,
username VARCHAR(64) NOT NULL,
passwd VARCHAR(32) NOT NULL,
PRIMARY KEY(userId);
);
# Lets add a LEGIT user!
INSERT INTO users VALUES(1000,"Terry","Teabagface$2");
# We will create an order table that holds a reference
# to an order made by our Terry
CREATE TABLE orders(
orderId INT NOT NULL,
orderDescription VARCHAR(255),
ordererId INT NOT NULL,
PRIMARY KEY(orderId),
FOREIGN KEY (ordererId) REFERENCES users(userId)
);
# Now we can add an order from Terry
INSERT INTO orders VALUES(0001,"Goat p0rn Weekly",1000);
# Want to know more about the plight of Goats?
# See the link below
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 Jobs(
job_id number not null,
job_title varchar(30),
min_salary number,
max_salary number
);
create table job_history(
employee_id number not null,
start_date date,
end_date date,
job_id number not null,
department_id number
);
alter table jobs add constraint pk_jobs primary key(job_id);
alter table job_history add constraint fk_job foreign key(job_id) references jobs(job_id);
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
# A foreign key is essentially a reference to a primary
# key in another table.
# A Simple table of Users,
CREATE TABLE users(
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
PRIMARY KEY(id)
);
# Lets add a LEGIT user!
INSERT INTO users VALUES(2,"Sharon","sharon@gmail.com");
# We will create an order table that holds a reference
# to an order made by our Terry
CREATE TABLE notes(
id INT NOT NULL AUTO_INCREMENT,
body text,
user_id INT NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY (user_id) REFERENCES users(id)
);
# Now we can add an order from Terry
INSERT INTO notes VALUES(4,"Update my php storm to version 22.3",2);
# Want to know more about the plight of Goats?
# See the link below
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
ALTER TABLE Evaluation
ADD CONSTRAINT FK_Evaluation_CodeEpreuve
FOREIGN KEY (CodeEpreuve) REFERENCES YourReferencedTable(ReferencedColumn);
xxxxxxxxxx
ALTER TABLE your_table ADD FOREIGN KEY (your_column) REFERENCES other_table(other_column);
xxxxxxxxxx
Foreign Key is a non-key attribute which is derived from the primary key
of another table which links those tables together.