Setting up Foreign Keys

You can create foreign keys using SQL statements or Pervasive 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 below 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 Pervasive PSQL User's Guide, see Foreign Keys Tasks and SQL Editor .

In the example below, 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.