Databases
How do I set up a foreign key?
Quick answer
To set up a foreign key, define it in the child table referencing the primary key of the parent table during table creation or modification.
Setting up a foreign key ensures referential integrity between two tables in a database.
Steps
- 1
MySQL
Use the following SQL command: `ALTER TABLE child_table ADD CONSTRAINT fk_name FOREIGN KEY (child_column) REFERENCES parent_table(parent_column);`
- 2
PostgreSQL
You can create a foreign key during table creation: `CREATE TABLE child_table (child_column INT, FOREIGN KEY (child_column) REFERENCES parent_table(parent_column));`
- 3
SQL Server
Use the command: `ALTER TABLE child_table ADD CONSTRAINT fk_name FOREIGN KEY (child_column) REFERENCES parent_table(parent_column);`
- 4
Oracle
Create a foreign key with: `ALTER TABLE child_table ADD CONSTRAINT fk_name FOREIGN KEY (child_column) REFERENCES parent_table(parent_column);`
Understanding Foreign Keys
A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. It establishes a link between the two tables.
Platform-Specific Steps
The steps to create a foreign key can vary based on the database management system (DBMS) you are using.
Watch out for
- Ensure that the data types of the foreign key and primary key match.
- Foreign keys can only reference unique or primary keys in the parent table.
FAQ
What happens if the referenced row in the parent table is deleted?
If the referenced row is deleted, the behavior depends on the foreign key constraints set (e.g., CASCADE, SET NULL, or RESTRICT).
Can a foreign key reference a composite primary key?
Yes, a foreign key can reference a composite primary key by including all columns of the primary key in the foreign key definition.
Is it possible to create a foreign key after table creation?
Yes, you can add a foreign key to an existing table using the `ALTER TABLE` command.