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
ALTER TABLE sounds ADD COLUMN sounds INT NOT NULL,
ADD FOREIGN KEY (sounds) REFERENCES characters(character_id);
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
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
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id int REFERENCES Customers(id)
);
xxxxxxxxxx
ALTER TABLE Table_Name
ADD column_name Data_Type,
CONSTRAINT FK_Name FOREIGN KEY(column_name)
REFERENCES PK_Table_Name(PK_Column_Name);
xxxxxxxxxx
A foreign key is an attribute of a relation (subordinate/dependent)
that points to the primary key of another relation (main/master)
FK is used to create a many-to-one (one-to-many) relationship.