The Data Management Center

SQL ADD FOREIGN KEY


David Haertzen David Haertzen, Tutorial Author
Check out our
Data Warehousing Tutorial.

HOME | SQL OVERVIEW | SQL BASICS | SQL ADMIN | SQL ADVANCED | SQL SYNTAX | Bookmark and Share
Previous | Next

SQL > SQL Administration > SQL ADD FOREIGN KEY Clause


What is the SQL ADD FOREIGN KEY Clause?

A Foreign Key is a constraint that ensures that column(s) in one table match the primary key column(s) in another table. For example, we may have a table named SALES_ORDER_LINE that contains orders for products and a table named PRODUCT that contains product information.

SALES_ORDER_LINE Table:
Column NameDatatype Constraint
sales_order_line_id INT NOT NULL - PRIMARY KEY
sales_order_id INT NOT NULL
line_nbr INT NOT NULL
product_id INT NOT NULL
line_order_qty INT NOT NULL
line_ship_qty INT NOT NULL
line_status_code CHAR(10) NOT NULL

PRODUCT Table:
Column NameDatatype Constraint
product_id INT NOT NULL - PRIMARY KEY
product_nbr VARCHAR(20) NOT NULL
product_name VARCHAR(100)NOT NULL
inventory_qty INT NOT NULL
product_status_code CHAR(10) NOT NULL

A foreign key is placed on the product_id column of the SALES_ORDER_LINE table to the primary key of the PRODUCT table. This establishes "Referential Integrity", RI for short, that requires a row to exist in the PRODUCT table that matches the row in the SALES_ORDER_LINE table.

The SQL CREATE FOREIGN KEY clause is the SQL capability that adds a new foreign key an existing table to an SQL database.

Why Use the SQL ADD FOREIGN KEY Clause?

SQL foreign keys are used because they can provide the following benefits / functions:

  • Improve data quality by enforcing Referential Integrity (RI) rules
  • Enable understanding of data structures

How To Use the SQL ADD FOREIGN KEY Clause

The SQL CREATE FOREIGN KEY clause is used as follows.

SQL CREATE FOREIGN KEY Clause Syntax

 
ALTER TABLE <table_name>
ADD <constraint_name> FOREIGN KEY
(<column_name1> ,
<column_name2> )
REFERENCES <table_name>
(<column_name1> ,
<column_name2>)
 

The number of characters that can make up SQL names for tables, columns and foreign keys varies by DBMS. In many cases the limit is 30 characters. The leading character of the name must be alphabetic - not a number or special character. The name of a new foreign key can not duplicate the name of an existing foreign key for the database and should not be the same as a SQL reserved word. To make the foreign key unique it is common practice to include the table and column name as part of the foreign key name. The underscore character can be used to improve readability. List elements are seperated by commas.

SQL ADD FOREIGN KEY Clause Example

The following example creates a foreign key on column product_id on table SALES_ORDER_LINE with column product_id which is the primary key of table product.

This SQL ADD FOREIGN KEY class is executed:

 
ALTER TABLE SALES_ORDER_LINE
ADD CONSTRAINT FK_SALES_ORDER_LINE_PRODUCT FOREIGN KEY (product_id)
REFERENCES PRODUCT
(product_id)

 

HOME | SQL OVERVIEW | SQL BASICS | SQL ADMINISTRATION | SQL ADVANCED | SQL SYNTAX
infogoal.com HOME
SQL OVERVIEW
SQL SYNTAX
SQL BOOKREVIEWS
SQL BASICS
SQL SELECT
SQL WHERE
SQL INSERT
SQL UPDATE
SQL DELETE
SQL ADMINISTRATION
SQL CREATE DATABASE
SQL DROP DATABASE
SQL CREATE TABLE
SQL ALTER TABLE
SQL DROP TABLE
SQL CREATE INDEX
SQL DROP INDEX
SQL ADD FOREIGN KEY
SQL DROP FOREIGN KEY
SQL CREATE VIEW
SQL DROP VIEW
SQL ADVANCED
SQL CONCAT
SQL SUBSTRING
SQL TRIM
SQL AND & OR
SQL IN
SQL BETWEEN
SQL LIKE
SQL DISTINCT
SQL GROUP BY
SQL AGGREGATE
SQL HAVING
SQL ORDER BY
SQL JOIN
SQL OUTER JOIN

Copyright© 1999-2014, First Place Software, Inc.