Wikibooks: Structured Query Language/Foreign Key

• Format: Book
• Language: English
• Subjects: DML
• Online Access: https://en.wikibooks.org/wiki/Structured_Query_Language/Foreign_Key

Nav Foreign Keys (FK) define a directed reference from one table (the child) to another table (the parent). This reference acts as long as the involved columns of the two tables contain identical values. It couples one row of the child table to a single row of the parent table a row of the parent table may be coupled by many rows of the child table. E.g. You may have the table department with column id and the table employee with column dept id . If you want to assign an employee to a distinct department you store the department id in its column dept id . This can be done in every case independent from any Foreign Key definition. But in such cases people often have two additional requirements First employees shall only be assigned to departments which really exist. Second as long as employees are assigned to a distinct department it shall be impossible to delete this department. The main purpose of Foreign Keys is to guarantee these two requirements. In other words Foreign Keys guarantee that no orphans will arise. = Foreign Key vs. Join = Within RDBMs identical values are used to link rows of different and sometimes of the same table together. Because this linking works on the basis of values and not of any link or special reference it has no direction. In general we call this technique a JOIN . Foreign Keys have a very similar concept because they also link rows with identical values together. But there are important differences Foreign Keys have a direction. It is important to know which one of the two affected tables is the child table and which one is the parent table. Joins must be expressed within every DML statement which is interested in this join (with the exception of views). In contrast Foreign Keys are part of table definitions. All DML commands bear them in mind without expressing them within a DML statement. = Syntax = As part of CREATE TABLE command CREATE TABLE ( . CONSTRAINT FOREIGN KEY ( ) REFERENCES ( ) ) As part of ALTER TABLE command ALTER TABLE ADD CONSTRAINT . same as above ALTER TABLE ...