- What Are Database Objects?
- What Is a Schema?
- Tables: 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 ensure accuracy and consistency of data in a relational database. Data integrity is handled in a relational database through the concept of referential integrity. Many types of integrity constraints play a role in referential integrity (RI).
Primary Key Constraints
Primary key is the term that identifies 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.
You can define a primary key that consists of more than one column by either of the following methods, which demonstrate creating a primary key in an Oracle table:
CREATE TABLE PRODUCT_TST (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_TST 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. Although 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 ensuring 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 provides an order to data in a table and, in the same respect, joins 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 that enforces referential integrity between tables in a relational database. A column defined as a foreign key references 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_TST (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.4 for a better understanding of the parent table/child table relationship.
)
Figure 3.4 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. For a value to be inserted for EMP_ID in the child table, a value for EMP_ID in the parent table must exist. Likewise, for a value to be removed for EMP_ID in the parent table, all corresponding first values for EMP_ID must be removed from the child table. This is how referential integrity works.
You can add a foreign key 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.
Check Constraints
You can utilize check (CHK) constraints to check the validity of data entered into particular table columns. Check constraints 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 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 in Oracle:
CREATE TABLE EMPLOYEE_CHECK_TST (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 into 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 an SQL query. You learn more about these conditions in Hours 5 and 7.
Dropping Constraints
Using the ALTER TABLE command with the DROP CONSTRAINT option, you can drop any constraint that you have defined. 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 provide shortcuts for dropping certain constraints. For example, to drop the primary key constraint for a table in MySQL, you can use the following command:
ALTER TABLE EMPLOYEES DROP PRIMARY KEY; Table altered.