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 industrial world would be a more peaceful place if workers were called in as collaborators in the process of establishing standards and defining shop practices, matters which surely affect their interests and well-being fully as much as they affect those of employers and consumers.”
    —Mary Barnett Gilson (1877–?)

    “Meanwhile I, deserted, was lamenting a little to myself your long delays in foreign loves, until sleep with its pleasing wings compelled me, fallen.”
    —Propertius Sextus (c. 50–16 B.C.)

    “McCoy: That shark’s been following us ever since the surgeon died, waiting for the burial. Couldn’t I have a musket to shoot it, sir?
    Fletcher Christian: Take the deck, McCoy. I’ll get the keys to the arms chest.
    McCoy: Get two muskets, sir. I’d like to shoot that shark on board.”
    —Talbot Jennings (1896–1985)