How to Maintain Entity Integrity in a Multitable SQL Database

By Allen G. Taylor

Every table in a database in SQL corresponds to an entity in the real world. That entity may be physical or conceptual, but in some sense, the entity’s existence is independent of the database. A table has entity integrity if the table is entirely consistent with the entity that it models.

To have entity integrity, a table must have a primary key that uniquely identifies each row in the table. Without a primary key, you can’t be sure that the row retrieved is the one you want.

To maintain entity integrity, be sure to specify that the column (or group of columns) making up the primary key is NOT NULL. In addition, you must constrain the primary key to be UNIQUE. Some SQL implementations enable you to add such constraints to the table definition. With other implementations, however, you must apply the constraint later, after you specify how to add, change, or delete data from the table.

The best way to ensure that your primary key is both NOT NULL and UNIQUE is to give the key the PRIMARY KEY constraint when you create the table, as shown in the following example:

CREATE TABLE CLIENT (
 ClientName  CHAR (30)  PRIMARY KEY,
 Address1   CHAR (30),
 Address2   CHAR (30),
 City    CHAR (25),
 State    CHAR (2),
 PostalCode  CHAR (10),
 Phone    CHAR (13),
 Fax    CHAR (13),
 ContactPerson  CHAR (30)
 ) ;

An alternative is to use NOT NULL in combination with UNIQUE, as shown in the following example:

CREATE TABLE CLIENT (
 ClientName  CHAR (30)  NOT NULL,
 Address1   CHAR (30),
 Address2   CHAR (30),
 City    CHAR (25),
 State    CHAR (2),
 PostalCode  CHAR (10),
 Phone    CHAR (13),
 Fax    CHAR (13),
 ContactPerson  CHAR (30),
 UNIQUE (ClientName) ) ;