you are at Encyclopedia of SQL >> FOREIGN KEY
FOREIGN KEY
FOREIGN KEY is a column which points to PRIARY KEY in another table. This kind of key
prevents actions which can destroy link between two tables. Foreign key doesn't have
to have any value. It should point to certain record in second table.
Foreign key can be set during creating table or for existing table.
Syntax for creating table with foreign key
MySQL:
CREATE TABLE `table_name1`
{
`column_name1` data_type,
`column_name2` data_type,
PRIMARY KEY (`column_name1`),
FOREIGN KEY (`column_name2`)
REFERENCES `table_name2`(`column_name3`)
SQL Server / Oracle / MS Access:
CREATE TABLE `table_name`
{
`column_name1` data_type PRIMARY KEY,
`column_name2` data_type FOREIGN KEY
REFERENCES `table_name2`(`column_name3`)
}
Syntax for creating table with named foreign key
CREATE TABLE `table_name`
{
`column_name1` data_type ,
`column_name2` data_type,
CONSTRAINT fk_name
FOREIGN KEY `column_name2`
REFERENCES `table_name`(`column_name3`)
}
Syntax for specifing foreign key for existing table
ALTER TABLE `table_name`
ADD FOREIGN KEY (`column_name`)
REFERENCES `table_name2`(`column_name3`)
Syntax for specifing foreign key for existing table
ALTER TABLE `table_name`
ADD CONSTRAINT fk_name
FOREIGN KEY `table_name2`(`column_name3`)
Syntax for deleting foreign key from existing table
MySQL:
ALTER TABLE `table_name`
DROP FOREIGN KEY fk_name
SQL Server / Oracle / MS Access:
ALTER TABLE `table_name`
DROP CONSTRAINT fk_name
[ wróć na górę strony ]
