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

• Unique Constraints

• Foreign Key Constraints

• NOT NULL Constraints

  
  

  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       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);
  
  
  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       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         UNIQUE, EMP_PAGER      NUMBER(10)     NULL);
  
  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 co-lumn 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 theEMPLOYEE_PAY_TBL table. Study Table  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);
  
  
  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
  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 NULLvalues 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.