Sams Teach Yourself SQL in 24 Hours

Recommended Book

Sams Teach Yourself SQL in 24 Hours, 3rd Edition

Learn More Buy

Recommended Book

Recommended Book 

Sams Teach Yourself SQL in 24 Hours, 3rd Edition

Learn More Buy

Integrity Constraints

Integrity constraints are used to ensure accuracy and consistency of data in a relational database. Data integrity is handled in a relational database through the concept of referential integrity. There are many types of integrity constraints that play a role in referential integrity (RI).

Primary Key Constraints

Primary key is the term used to identify one or more columns in a table that make a row of data unique. Although the primary key typically consists of one column in a table, more than one column can comprise the primary key. For example, either the employee's Social Security number or an assigned employee identification number is the logical primary key for an employee table. The objective is for every record to have a unique primary key or value for the employee's identification number. Because there is probably no need to have more than one record for each employee in an employee table, the employee identification number makes a logical primary key. The primary key is assigned at table creation.

The following example identifies the EMP_ID column as the PRIMARY KEY for the EMPLOYEES table:

CREATE TABLE EMPLOYEE_TBL 
(EMP_ID        CHAR(9)        NOT NULL PRIMARY KEY,
EMP_NAME       VARCHAR (40)   NOT NULL,
EMP_ST_ADDR    VARCHAR (20)   NOT NULL,
EMP_CITY       VARCHAR (15)   NOT NULL,
EMP_ST         CHAR(2)        NOT NULL,
EMP_ZIP        INTEGER(5)      NOT NULL,
EMP_PHONE      INTEGER(10)     NULL,
EMP_PAGER      INTEGER(10)     NULL);

This method of defining a primary key is accomplished during table creation. The primary key in this case is an implied constraint. You can also specify a primary key explicitly as a constraint when setting up a table, as follows:

   
CREATE TABLE EMPLOYEE_TBL
(EMP_ID        CHAR(9)        NOT NULL,
EMP_NAME       VARCHAR (40)   NOT NULL,
EMP_ST_ADDR    VARCHAR (20)   NOT NULL,
EMP_CITY       VARCHAR (15)   NOT NULL,
EMP_ST         CHAR(2)        NOT NULL,
EMP_ZIP        INTEGER(5)      NOT NULL,
EMP_PHONE      INTEGER(10)     NULL,
EMP_PAGER      INTEGER(10)     NULL,
PRIMARY KEY (EMP_ID));

The primary key constraint in this example is defined after the column comma list in the CREATE TABLE statement.

A primary key that consists of more than one column can be defined by either of the following methods:

   
CREATE TABLE PRODUCTS
(PROD_ID       VARCHAR2(10)     NOT NULL,
 VEND_ID       VARCHAR2(10)     NOT NULL,
 PRODUCT       VARCHAR2(30)     NOT NULL,
 COST              NUMBER(8,2)       NOT NULL,
PRIMARY KEY (PROD_ID, VEND_ID));

ALTER TABLE PRODUCTS
ADD CONSTRAINT PRODUCTS_PK PRIMARY KEY (PROD_ID, VEND_ID);

Unique Constraints

A unique column constraint in a table is similar to a primary key in that the value in that column for every row of data in the table must have a unique value. While a primary key constraint is placed on one column, you can place a unique constraint on another column even though it is not actually for use as the primary key.

Study the following example:

CREATE TABLE EMPLOYEE_TBL 
(EMP_ID        CHAR(9)        NOT NULL     PRIMARY KEY,
EMP_NAME       VARCHAR (40)   NOT NULL,
EMP_ST_ADDR    VARCHAR (20)   NOT NULL,
EMP_CITY       VARCHAR (15)   NOT NULL,
EMP_ST         CHAR(2)        NOT NULL,
EMP_ZIP        INTEGER(5)      NOT NULL,
EMP_PHONE      INTEGER(10)     NULL         UNIQUE,
EMP_PAGER      INTEGER(10)     NULL);

The primary key in this example is EMP_ID, meaning that the employee identification number is the column that is used to ensure that every record in the table is unique. The primary key is a column that is normally referenced in queries, particularly to join tables. The column EMP_PHONE has been designated as a UNIQUE value, meaning that no two employees can have the same telephone number. There is not a lot of difference between the two, except that the primary key is used to provide an order to data in a table and, in the same respect, join related tables.

Foreign Key Constraints

A foreign key is a column in a child table that references a primary key in the parent table. A foreign key constraint is the main mechanism used to enforce referential integrity between tables in a relational database. A column defined as a foreign key is used to reference a column defined as a primary key in another table.

Study the creation of the foreign key in the following example:

   
CREATE TABLE EMPLOYEE_PAY_TBL
(EMP_ID           CHAR(9)        NOT NULL,
POSITION           VARCHAR2(15)   NOT NULL,
DATE_HIRE          DATE           NULL,
PAY_RATE           NUMBER(4,2)    NOT NULL,
DATE_LAST_RAISE    DATE           NULL,
CONSTRAINT EMP_ID_FK FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE_TBL (EMP_ID));

The EMP_ID column in this example has been designated as the foreign key for the EMPLOYEE_PAY_TBL table. This foreign key, as you can see, references the EMP_ID column in the EMPLOYEE_TBL table. This foreign key ensures that for every EMP_ID in the EMPLOYEE_PAY_TBL, there is a corresponding EMP_ID in the EMPLOYEE_TBL. This is called a parent/child relationship. The parent table is the EMPLOYEE_TBL table, and the child table is the EMPLOYEE_PAY_TBL table. Study Figure 3.2 for a better understanding of the parent table/child table relationship.

Figure 3.2 The parent/child table relationship.

In this figure, the EMP_ID column in the child table references the EMP_ID column in the parent table. In order for a value to be inserted for EMP_ID in the child table, there must first exist a value for EMP_ID in the parent table. Likewise, for a value to be removed for EMP_ID in the parent table, all corresponding values for EMP_ID must first be removed from the child table. This is how referential integrity works.

A foreign key can be added to a table using the ALTER TABLE command, as shown in the following example:

ALTER TABLE EMPLOYEE_PAY_TBL 
ADD CONSTRAINT ID_FK FOREIGN KEY (EMP_ID)
REFERENCES EMPLOYEE_TBL (EMP_ID);

NOT NULL Constraints

Previous examples use the keywords NULL and NOT NULL listed on the same line as each column and after the data type. NOT NULL is a constraint that you can place on a table's column. This constraint disallows the entrance of NULL values into a column; in other words, data is required in a NOT NULL column for each row of data in the table. NULL is generally the default for a column if NOT NULL is not specified, allowing NULL values in a column.

Using Check Constraints

Check (CHK) constraints can be utilized to check the validity of data entered into particular table columns. Check constraints are used to provide back-end database edits, although edits are commonly found in the front-end application as well. General edits restrict values that can be entered into columns or objects, whether within the database itself or on a front-end application. The check constraint is a way of providing another protective layer for the data.

The following example illustrates the use of a check constraint:

   
CREATE TABLE EMPLOYEE_TBL
(EMP_ID        CHAR(9)        NOT NULL,
EMP_NAME       VARCHAR2(40)   NOT NULL,
EMP_ST_ADDR    VARCHAR2(20)   NOT NULL,
EMP_CITY       VARCHAR2(15)   NOT NULL,
EMP_ST         CHAR(2)        NOT NULL,
EMP_ZIP        NUMBER(5)      NOT NULL,
EMP_PHONE      NUMBER(10)     NULL,
EMP_PAGER      NUMBER(10)     NULL),
PRIMARY KEY (EMP_ID),
CONSTRAINT CHK_EMP_ZIP CHECK ( EMP_ZIP = '46234');

The check constraint in this table has been placed on the EMP_ZIP column, ensuring that all employees entered into this table have a ZIP code of '46234'. Perhaps that is a little restricting. Nevertheless, you can see how it works.

If you wanted to use a check constraint to verify that the ZIP code is within a list of values, your constraint definition could look like the following:

   
CONSTRAINT CHK_EMP_ZIP CHECK ( EMP_ZIP in ('46234','46227','46745') );

If there is a minimum pay rate that can be designated for an employee, you could have a constraint that looks like the following:

   
CREATE TABLE EMPLOYEE_PAY_TBL
(EMP_ID            CHAR(9)        NOT NULL,
POSITION           VARCHAR2(15)   NOT NULL,
DATE_HIRE          DATE           NULL,
PAY_RATE           NUMBER(4,2)    NOT NULL,
DATE_LAST_RAISE    DATE           NULL,
CONSTRAINT  EMP_ID_FK FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE_TBL (EMP_ID),
CONSTRAINT CHK_PAY CHECK ( PAY_RATE > 12.50 ) );

In this example, any employee entered in this table must be paid more than $12.50 an hour. You can use just about any condition in a check constraint, as you can with a SQL query. You learn more about these conditions in later hours.

Dropping Constraints

Any constraint that you have defined can be dropped using the ALTER TABLE command with the DROP CONSTRAINT option. For example, to drop the primary key constraint in the EMPLOYEES table, you can use the following command:

   
   

   ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_PK;

   
Table altered.

Some implementations may provide shortcuts for dropping certain constraints. For example, to drop the primary key constraint for a table in Oracle, you can use the following command:

   

   ALTER TABLE EMPLOYEES DROP PRIMARY KEY;

   
Table altered.