Web | This Site

Learning Basic SQL
Advanced SQL
SQL Functions
SQL Useful Resources
Selected Reading
© 2011 TutorialsPoint.COM

| Home |  | References |  | Discussion Forums |  | About TP

# SQL - Foreign Key

A foreign key is a key used to link two tables together. This is sometimes called a referencing key.

Primary key field from one table and insert it into the other table where it becomes a foreign key ie. Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table.

The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.

If a table has a primary key defined on any field(s) then you can not have two records having the same value of that field(s).

# Example:

Consider the structure of the two tables as follows:

CUSTOMERS table:

    CREATE TABLE CUSTOMERS(
           ID   INT              NOT NULL,
           NAME VARCHAR (20)     NOT NULL,
           AGE  INT              NOT NULL,
           ADDRESS  CHAR (25) ,
           SALARY   DECIMAL (18, 2),
           PRIMARY KEY (ID)
    );

ORDERS table:

    CREATE TABLE ORDERS (
           ID          INT        NOT NULL,
           DATE        DATETIME,
           CISTOMER_ID INT references CUSTOMERS(ID),
           AMOUNT     double,
           PRIMARY KEY (ID)
    );

If ORDERS table has already been created, and the foreign key has not yet been, use the syntax for specifying a foreign key by altering a table.

    ALTER TABLE ORDERS
       ADD FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID);

# DROP a FOREIGN KEY Constraint:

To drop a FOREIGN KEY constraint, use the following SQL:

    ALTER TABLE ORDERS
       DROP FOREIGN KEY;

Advertisement

Online Image Processing

Indian Baby Names