Setting up Foreign Keys
You can create foreign keys using SQL statements or PSQL Control Center. When you create a foreign key, you may define an associated rule at the same time. You can define multiple rules on the same key. If you create a foreign key without specifying associated rules, the default referential integrity is restrict for both update and delete.
Creating a Foreign Key During Table Creation
You can create a foreign key when you create a table, by using the REFERENCES keyword in your column definition. A foreign key can consist of one or more columns. The data types of the column(s) must be the same as the primary key that this foreign key references. The example next shows the column named
your_id
being created then being designated the foreign key, referencing
mytable.id
:
CREATE TABLE yourtable (your_id INTEGER REFERENCES mytable(id) ON DELETE CASCADE, yourname CHAR(20))
You can also add the foreign key designation at the end of the statement. You must use this technique if you wish to use multiple columns in the key:
CREATE TABLE yourtable (your_id INTEGER,
yourname CHAR(20),
FOREIGN KEY(your_id, yourname) REFERENCES
mytable(id, myname) ON DELETE CASCADE)
When you create a foreign key, the database engine adds an index on the designated columns.
For more examples, see
CREATE TABLE
in
SQL Engine Reference
.
Adding a Foreign Key to an Existing Table
You can add a foreign key to an existing table with PCC or by using the ALTER TABLE statement with ADD FOREIGN KEY. In
PSQL User's Guide
, see
Foreign Keys Tasks
and
SQL Editor
.
In the following example, two rules are defined for this foreign key, both a delete rule and an update rule:
ALTER TABLE yourtable ADD FOREIGN KEY (your_id,yourname) REFERENCES mytable(id,myname) ON DELETE CASCADE ON UPDATE RESTRICT
Use DELETE CASCADE with caution. See examples in
Delete Cascade
.
For more examples, see
ALTER TABLE
in
SQL Engine Reference
.