How to handle foreign key in postgresql?
How to handle foreign key in postgresql?
1 answer
by
rollin ,
7 months ago
@herminia_bruen
To handle foreign keys in PostgreSQL, you can follow these steps:
- Define a foreign key constraint in your table creation script. For example:
1 2 3 4 5 6 7 |
CREATE TABLE table1 (
id SERIAL PRIMARY KEY,
column1 INTEGER,
column2 TEXT,
foreign_key_id INTEGER,
FOREIGN KEY (foreign_key_id) REFERENCES table2(id)
);
|
- You can also add a foreign key constraint to an existing table using the ALTER TABLE statement. For example:
1 2 3 |
ALTER TABLE table1 ADD CONSTRAINT fk_foreign_key FOREIGN KEY (foreign_key_id) REFERENCES table2(id); |
- To delete a foreign key constraint, you can use the DROP CONSTRAINT statement. For example:
1 2 |
ALTER TABLE table1 DROP CONSTRAINT fk_foreign_key; |
- You can also disable and enable a foreign key constraint using the DISABLE and ENABLE CONSTRAINT statements. For example:
1 2 3 4 5 |
ALTER TABLE table1 DISABLE CONSTRAINT fk_foreign_key; ALTER TABLE table1 ENABLE CONSTRAINT fk_foreign_key; |
- To check the foreign key constraints in the database, you can use the following query:
1 2 3 4 5 6 7 8 9 10 |
SELECT
tc.table_name, kcu.column_name, ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY';
|
By following these steps, you can effectively handle foreign keys in PostgreSQL.