Setting up Foreign Keys

---

You must create foreign keys using SQL statements. Pervasive Control Center does not provide this ability in the user interface.

When you create a foreign key, you must define the associated rule at the same time. You can define multiple rules on the same key.

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, by using the ALTER TABLE statement with ADD FOREIGN KEY key words. 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 

For more examples, see ALTER TABLE in SQL Engine Reference.