CHAPTER 7 Ensuring Data Integrity

It is up to the application programmer to decide how to present this information to the user and enable the user to take appropriate action. The appropriate action in this case is usually just to provide a unique value for the primary key.

Primary keys enforce entity integrity

Once the primary key for each table is specified, no further action is needed by client application developers or by the database administrator to maintain entity integrity, if it is a single-column primary key.

For a multi-column primary key, you can create a stored procedure to check insertions into all primary key columns, so that the combined columns would always produce a unique value.

The primary key for a table is defined by the table owner when the table is created. If the structure of a table is modified at a later date, the primary key may also be redefined using the ALTER TABLE statement clauses DELETE PRIMARY KEY or ADD PRIMARY KEY. For details, see the ALTER TABLE statement in Adaptive Server IQ Reference Manual.

Some application development systems and database design tools allow you to create and alter database tables. If you are using such a system, you may not have to enter the CREATE TABLE or ALTER TABLE command explicitly: the application generates the statement itself from the information you provide.

For information on creating primary keys, see “Creating primary and foreign keys”. For the detailed syntax of the CREATE TABLE statement, see “CREATE TABLE statement;” for information about changing table structure, see the “ALTER TABLE statement,” both in the Adaptive Server IQ Reference.

Declaring referential integrity

A foreign key relates the information in one table (the foreign table) to information in another (referenced or primary) table. A particular column, or combination of columns, in a foreign table is designated as a foreign key to the primary table.

For the foreign key relationship to be valid, the entries in the foreign key must correspond to the primary key values of a row in the referenced table. Occasionally, some other unique column combination may be referenced, instead of a primary key.

283

Page 303
Image 303
Sybase 12.4.2 manual Primary keys enforce entity integrity, Declaring referential integrity, 283