Foreign Key - Defining Foreign Keys

Defining Foreign Keys

Foreign keys are defined in the ANSI SQL Standard, through a FOREIGN KEY constraint. The syntax to add such a constraint to an existing table is defined in SQL:2003 as shown below. Omitting the column list in the REFERENCES clause implies that the foreign key shall reference the primary key of the referenced table.

ALTER TABLE ADD FOREIGN KEY ( {, }... ) REFERENCES

Likewise, foreign keys can be defined as part of the CREATE TABLE SQL statement.

CREATE TABLE TABLE_NAME ( id INTEGER PRIMARY KEY, col2 CHARACTER VARYING(20), col3 INTEGER, ... FOREIGN KEY(col3) REFERENCES other_table(key_col) ON DELETE CASCADE, ... )

If the foreign key is a single column only, the column can be marked as such using the following syntax:

CREATE TABLE TABLE_NAME ( id INTEGER PRIMARY KEY, col2 CHARACTER VARYING(20), col3 INTEGER REFERENCES other_table(column_name), ... )

Foreign keys can be defined with a stored procedure statement.

sp_foreignkey tabname, pktabname, col1 ...

• tabname: the name of the table or view that contains the foreign key to be defined.
• pktabname: the name of the table or view that has the primary key to which the foreign key applies. The primary key must already be defined.
• col1: the name of the first column that makes up the foreign key. The foreign key must have at least one column and can have a maximum of eight columns.

Read more about this topic:  Foreign Key