1. SQL Tutorial
  2. SQL FOREIGN KEY Constraint

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

  1.   CREATE TABLE Students
  2.   (
  3.   StudentID INT  PRIMARY KEY,
  4.   Firstname text NOT NULL,
  5.   Lastname text NOT NULL,
  6.  
  7.   CourseID INT FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
  8.   )

Example For MySQL

  1.   CREATE TABLE Students
  2.   (
  3.   StudentID INT,
  4.   Firstname text NOT NULL,
  5.   Lastname text NOT NULL, CourseID INT NOT NULL,
  6.   PRIMARY KEY (StudentID),
  7.   FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
  8.   )

ALTER TABLE Syntax

Example

  1.   ALTER TABLE Students
  2.   ADD FOREIGN KEY (CourseID)
  3.   REFERENCES Course(CourseID)

TO REMOVE FOREIGN KEY

Example

  1.   ALTER TABLE Students
  2.   DROP FOREIGN KEY
Tags
sql tutorial
FOREIGN KEY Constraint

Add new comment

About text formats
  • 207 views