Establishing Relationships between Tables

To establish a link between the data in two tables, you add one or more columns that hold one table's primary key values to the other table. This column becomes a foreign key in the second table. In SQL DDL, you can use a FOREIGN KEY constraint to reference another table. Foreign keys can be single- or multi-column.

A FOREIGN KEY constraint enforces referential integrity by ensuring that changes made to data in the primary key table do not break the link to data in the foreign key table. For example, you cannot delete a record in a primary key table or change a primary key value if the deleted or changed primary key value corresponds to a value in the FOREIGN KEY constraint of another table. The REFERENCES clause identifies the parent table of the relation.

Enforcing Data Integrity and Relationships between Tables | 419 Programming with the Jet Data Definition Language

To create a brand new table and relate it to an existing table, the following steps are required:

1. Use the CREATE TABLE statement followed by a table name. Example: CREATE TABLE tblOrder_Details

2. Follow the table name with one or more column definitions. A column definition consists of ColumnName followed by the data type and column size (if required).

Example: InvoiceID CHAR, ProductId CHAR, Units LONG, PRICE MONEY

3. To designate a primary key, use the CONSTRAINT clause followed by the constraint name, the PRIMARY KEY clause, and the name of the column or columns to be designated as the primary key.

Example: CONSTRAINT PrimaryKey PRIMARY KEY(InvoiceId, ProductId)

4. To designate a foreign key, use the CONSTRAINT clause followed by the constraint name, the FOREIGN KEY clause, and the name of the column to be designated as foreign key.

Example: CONSTRAINT fkInvoiceId FOREIGN KEY (InvoiceId)

5. Use the REFERENCES clause to specify the parent table to which a relationship is established.

Example: REFERENCES tblProduct_Orders

6. If required, specify ON UPDATE CASCADE and/or ON DELETE CASCADE to enable referential integrity rules with cascading updates or deletes.


^^ Note: You may choose not to enforce referential integrity rules by specifying ON UPDATE NO ACTION or ON DELETE NO ACTION, or skipping the ON UPDATE or ON DELETE keywords. If you choose this path, you will not be able to change the value of a primary key if matching records exist in the foreign table.

Refer to the procedure in Hands-On 20-4 to find out how to correctly combine the above example statements into a single SQL statement.

0 0

Post a comment