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

Famous quotes containing the words defining, foreign and/or keys:

    “The U.S. is becoming an increasingly fatherless society. A generation ago, an American child could reasonably expect to grow up with his or her father. Today an American child can reasonably expect not to. Fatherlessness is now approaching a rough parity with fatherhood as a defining feature of American childhood.”
    —David Blankenhorn (20th century)

    “Maybe it’s understandable what a history of failures America’s foreign policy has been. We are, after all, a country full of people who came to America to get away from foreigners. Any prolonged examination of the U.S. government reveals foreign policy to be America’s miniature schnauzer—a noisy but small and useless part of the national household.”
    —P.J. (Patrick Jake)

    “Thou hast the keys of Paradise, oh just, subtle, and mighty opium!”
    —Thomas De Quincey (1785–1859)