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 Name | Datatype | 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 Name | Datatype | 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.
SQL foreign keys are used because they can provide the following benefits / functions:
The SQL CREATE FOREIGN KEY clause is used as follows.
SQL CREATE FOREIGN KEY Clause Syntax
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:
Advertisements:
Infogoal.com is organized to help you gain mastery.
Examples may be simplified to facilitate learning.
Content is reviewed for errors but is not warranted to be 100% correct.
In order to use this site, you must read and agree to the
terms of use, privacy policy and cookie policy.
Copyright 2006-2020 by Infogoal, LLC. All Rights Reserved.