Developing By Me

Project Development and Design

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)

 

%d bloggers like this: