foreign key example in sql
Example 1: mysql add foreign key
-- On Create
CREATE TABLE tableName (
ID INT,
SomeEntityID INT,
PRIMARY KEY (ID),
FOREIGN KEY (SomeEntityID)
REFERENCES SomeEntityTable(ID)
ON DELETE CASCADE
);
-- On Alter, if the column already exists but has no FK
ALTER TABLE
tableName
ADD
FOREIGN KEY (SomeEntityID) REFERENCES SomeEntityTable(ID) ON DELETE CASCADE;
-- Add FK with a specific name
-- On Alter, if the column already exists but has no FK
ALTER TABLE
tableName
ADD CONSTRAINT fk_name
FOREIGN KEY (SomeEntityID) REFERENCES SomeEntityTable(ID) ON DELETE CASCADE;
Example 2: sql foreign key
# 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
Example 3: add constraint fk
ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
Example 4: foreign key in sql
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