Prev | Advanced Operations Guide | Next |
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, referencingmytable.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
Prev Setting up Primary Keys |
Contents Up Check for Revisions | Next Interactions Between Btrieve and Relational Constraints |