Code has been added to clipboard!

Using SQL FOREIGN KEY Constraint

Reading time 2 min
Published Aug 9, 2017
Updated Oct 3, 2019

SQL FOREIGN KEY Constraint: Main Tips

  • A FOREIGN KEY based in table links to a PRIMARY KEY based on other tables.
  • The FOREIGN KEY constraint prevents actions that could destroy links between tables.
  • The FOREIGN KEY constraint disallow invalid data from being inserted into the foreign key column.

Demo Database

This is demo example from the "Developers" table in the database:

ID Name City Country
1 Tom Kurkutis New York USA
2 Ana Fernandez London UK
3 Antonio Indigo Paris France
4 Aarav Kaelin Delhi India
5 Andrew Tumota Miami USA

This is demo example from the "Orders" table in the database:

Order_ID Client_ID Developer_ID Date
1509 9 1 2017-08-18
1510 20 5 2016-12-19
1511 15 5 2017-01-25

SQL FOREIGN KEY Constraint: Explained

The "DeveloperID" column in the "Orders" table links to the "DeveloperID" column in the "Developers" table.

The "DeveloperID" column in the "Developers" table is interpreted as the PRIMARY KEY in the "Developers" table.

The "DeveloperID" column in the "Orders" table is interpreted as a FOREIGN KEY in the "Orders" table.

Udacity
Pros
  • Simplistic design (no unnecessary information)
  • High-quality courses (even the free ones)
  • Variety of features
Main Features
  • Nanodegree programs
  • Suitable for enterprises
  • Paid certificates of completion
Coursera
Pros
  • Professional service
  • Flexible timetables
  • A variety of features to choose from
Main Features
  • Professional certificates of completion
  • University-level courses
  • Multiple Online degree programs
Datacamp
Pros
  • Great user experience
  • Offers quality content
  • Very transparent with their pricing
Main Features
  • Free certificates of completion
  • Focused on data science skills
  • Flexible learning timetable

SQL FOREIGN KEY Constraint on CREATE TABLE

Use this SQL syntax to build a FOREIGN KEY on the "DeveloperID" column when "Orders" table already exists:

MySQL:

Example
CREATE TABLE Orders (
    OrderID int NOT NULL,
    DeveloperID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (DeveloperID) REFERENCES Developers(DeveloperID)
);

SQL Server / MS Access / Oracle:

Example
CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    DeveloperID int FOREIGN KEY REFERENCES Developers(DeveloperID)
);

Use this SQL syntax to allow a FOREIGN KEY constraint to be named, and a FOREIGN KEY constraint on various columns to be defined:

MySQL / SQL Server / MS Access / Oracle:

Example
CREATE TABLE Orders (
    OrderID int NOT NULL,
    DeveloperID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_DeveloperOrder FOREIGN KEY (DeveloperID)
    REFERENCES Developers(DeveloperID)
);

SQL FOREIGN KEY Constraint on ALTER TABLE

Use this SQL syntax to build a FOREIGN KEY constraint on the "DeveloperID" column when the "Orders" table already exists:

MySQL / SQL Server / MS Access / Oracle:

Example
ALTER TABLE Orders
ADD FOREIGN KEY (DeveloperID) REFERENCES Developers(DeveloperID);

Use this SQL syntax to allow a FOREIGN KEY constraint to be named, and a FOREIGN KEY constraint on various columns to be defined:

MySQL / SQL Server / MS Access / Oracle:

Example
ALTER TABLE Orders
ADD CONSTRAINT FK_DeveloperOrder
FOREIGN KEY (DeveloperID) REFERENCES Developers(DeveloperID);

To DROP a FOREIGN KEY Constraint

Use this SQL syntax, to drop a FOREIGN KEY constraint.

MySQL:

Example
ALTER TABLE Orders
DROP FOREIGN KEY FK_DeveloperOrder;

SQL Server / MS Access / Oracle:

Example
ALTER TABLE Orders
DROP CONSTRAINT FK_DeveloperOrder;

Latest Udacity Coupon Found:

Verified STAFF PICK

75% OFF COURSES

Udacity Black Friday Offer

The best time to save on Udacity courses is now - follow this coupon to access a 75% Udacity Black Friday discount & enjoy learning at a very low cost!

Expiration date: 28/02/2021
3176 People Used
Only 97 Left
Rating
5.0