- What Are Database Objects?
- What Is a Schema?
- A Table: The Primary Storage for Data
- Integrity Constraints
- Summary
- Q&A
- Workshop
This chapter is from the book
This chapter is from the book
This chapter is from the book
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.
){kind=link}
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.
NOTE
The foreign key constraint is one area where MySQL does not comply with the ANSI standard. Foreign keys exist in MySQL, but are not used to enforce referential integrity as they are in most implementations such as Oracle.
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);
NOTE
The options available with the ALTER TABLE command differ among different implementations of SQL, particularly when dealing with constraints. In addition, the actual use and definitions of constraints also vary, but the concept of referential integrity should be the same with all relational databases.
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.
NOTE
Some implementations allow you to disable constraints. Instead of permanently dropping a constraint from the database, you may want to temporarily disable the constraint, and then enable it later.