FOREIGN KEY
FOREING KEYs connect a table to the PRIMARY KEY of another table. Through this process it is possible to ensure data integrity across multiple tables. Some databases do not handle this well however SQL Server can manage multiple indexes on every table with minimal impact. The following syntax will create the FOREIGN KEY on a table.
| SQL Syntax | Description | SQL Database |
| CREATE TABLE table1 (id INT NOT NULL PRIMARY KEY,table2_id INT FOREIGN KEY REFERENCES table2(id))
|
Creates a table with a FOREIGN KEY | SQL Server, Oracle |
| CREATE TABLE table1 (id INT NOT NULL,table2_id INT,PRIMARY KEY (id),
FOREIGN KEY (table2_id) REFERNCES table2(id) )
|
Creates a table with a FOREIGN KEY | MySQL, PostgreSQL |
| CREATE TABLE table1 (id INT NOT NULL,table2_id INT,PRIMARY KEY (id),
CONSTRAINT index_name FOREIGN KEY (table2_id) REFERENCES table2(id) ) |
Creates a table with a FOREIGN KEY | MySQL, SQL Server, Oracle |
| CREATE TABLE table1 (id INTEGER PRIMARY KEY,table2_id INTEGER REFERENCES table2) | Creates a table with a FOREIGN KEY | PostgreSQL (key must be a natural key meaning the PRIMARY KEY and FOREIGN KEY have the same name) |
