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
ALTER TABLE sounds ADD COLUMN sounds INT NOT NULL,
ADD FOREIGN KEY (sounds) REFERENCES characters(character_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
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
# 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
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.