SQL FOREIGN KEY Constraint

Submitted by admin on Sunday, April 3, 2011 - 21:44.

A FOREIGN KEY is a key in another table that relates from the PRIMARY KEY in the parent table. A Foreign Key is needed in the related table to connect data from the primary table.

Let's take a look at the following example

Course Table

CourseID Course
1 Math 101
2 English 101

Students Table

StudentID CourseID Lastname Firstname
1 1 Smith John
2 2 Simon Mathew
3 2 Steve Bill
4 2 Rogers Amanda

From the above table, Smith has taken the course Math 101 and the other three English 101.

Here's how to create Foreign Key and Primary Key.

Example For Oracle/MS Access/SQL Server

CREATE TABLE Students ( StudentID Int PRIMARY KEY, Firstname text NOT NULL, Lastname text NOT NULL, CourseID int FOREIGN KEY (CourseID) REFERENCES Course(CourseID) )

Example For MySQL

CREATE TABLE Students ( StudentID Int, Firstname text NOT NULL, Lastname text NOT NULL, CourseID int NOT NULL, PRIMARY KEY (StudentID), FOREIGN KEY (CourseID) REFERENCES Course(CourseID) )

ALTER TABLE Syntax

Example

ALTER TABLE Students ADD FOREIGN KEY (CourseID) REFERENCES Course(CourseID)

TO REMOVE FOREIGN KEY

Example

ALTER TABLE Students DROP FOREIGN KEY

Tags

Add new comment

About text formats
  • 168 views