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:
“Art, if one employs this term in the broad sense that includes poetry within its realm, is an art of creation laden with ideals, located at the very core of the life of a people, defining the spiritual and moral shape of that life.”
—Ivan Sergeevich Turgenev (1818–1883)
“A foreign minister, I will maintain it, can never be a good man of business if he is not an agreeable man of pleasure too. Half his business is done by the help of his pleasures: his views are carried on, and perhaps best, and most unsuspectedly, at balls, suppers, assemblies, and parties of pleasure; by intrigues with women, and connections insensibly formed with men, at those unguarded hours of amusement.”
—Philip Dormer Stanhope, 4th Earl Chesterfield (1694–1773)
“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)