Oracle Constraints

The constraints is a keyword. The constraint are rules that restrict the values for one or more columns in a table.The Oracle Server uses constraints to prevent invalid data entry into tables.The constraints store the validate data and without constraints we can just store invalid data. The constraints is a important part of the table.

Five types of constraints in Oracle

1. Primary Key Constraint
2. Check Constraint
3. Not null Constraint
4. Unique Constraint
5. Foreign Key Constraint

Below explanation is running on the below table.

EX:

CREATE TABLE STUDENT

( 

  STUDENT_ID     NUMBER      PRIMARY KEY,

  STUDENT_NUMBER NUMBER      NOT NULL,

  STUDENT_SEX    VARCHAR2(1) NOT NULL CHECK (UPPER(STUDENT_SEX)='M' or UPPER(STUDENT_SEX)='F'),

  STUDENT_SSN    VARCHAR2(20) CONSTRAINT UN_STUDENT_SSN UNIQUE

);

Primary Key Constraint:

Primary key constraints define a column or series of columns that uniquely identify a given row in a table. Defining a primary key on a table is optional and you can only define a single primary key on a table. A primary key constraint can consist of one or many columns (up to 32). Any column that is defined as a primary key column is automatically set with a NOT NULL status.

The previous example of the creation of the STUDENT table included the definition of the STUDENT_ID column as the primary key of that table by using the primary key keyword.

If you need to primary key constraint to a table after the fact, simply use the alter table command.

ALTER TABLE STUDENT ADD CONSTRAINT pk_student_id PRIMARY KEY (STUDENT_ID);

Did you notice on the line where we define the column STUDENT_ID, that we included the word PRIMARY KEY?

This is known as an in-line constraint because we are defining the constraint on the same line as the column associated with the constraint. In this case the keyword primary key means we are defining a primary key constraint on the STUDENT table called PK_STUDENT.

Check Constraints:

Check constraints validate that values in a given column meet a specific criteria. For example, you could create a check constraint on a varchar2 column so it only can contain the values M or F as in the above example.

Notice the constraint keyword, this indicates that we are getting ready to define a constraint. This particular example is known as an in-line constraint because the constraint is being defined in the same line as the column being defined.

If you need to add a check constraint to a table after the fact, simply use the alter table command. Here is an example:

ALTER TABLE STUDENT ADD (CONSTRAINT CK_STUDENT_SEX CHECK (UPPER(STUDENT_SEX)='M' OR UPPER(STUDENT_SEX)='F'));

Boolean Expressions can be used with Check Constraints.

NOT NULL Constraints:

NOT NULL constraints are in-line constraints that indicate that a column can not contain NULL values. 

The previous example of the creation of the STUDENT table contained two examples of NOT NULL constraints being defined. For example, the STUDENT_ID column is defined as NOT NULL in that example.

If you need to add a NOT NULL constraint to a table after the fact, simply use the alter table command as in this example:

ALTER TABLE STUDENT MODIFY ( STUDENT_ID NOT NULL);

Unique Constraints:

Unique constraints are like alternative primary key constraints. A unique constraint defines a column, or series of columns, that must be unique in value. You can have a number of unique constraints defined and the columns can have NULL values in them, unlike a column that belongs to a primary key constraint. 

If you need to add unique key constraints to a table after the fact, simply use the alter table command.

ALTER TABLE STUDENT ADD CONSTRAINT UK_STUDENT_ID_NUMBER UNIQUE (STUDENT_ID, STUDENT_NUMBER);

Foreign Key Constraints:

A foreign key constraint is used to enforce a relationship between two tables. 

As an example, take the case of two tables, ITEM and PART. These tables have a relationship (an item can have none, one or many parts). Foreign key constraints help to enforce that relationship.  In the DBA world, we use diagrams called Entity Relationship Diagrams (ERD) to show visually how tables relate. Here is an ERD that shows how the ITEM and PART tables relate.

CREATE TABLE PART(PART_NO NUMBER PRIMARY KEY, PART_DESC VARCHAR2(200) NOT NULL);

CREATE TABLE ITEM(
  ITEM_NO NUMBER, 
  PART_NO NUMBER, 
  ITEM_DESC VARCHAR2(200) NOT NULL,
  CONSTRAINT FK_ITEM_PART_NO FOREIGN KEY (PART_NO) REFERENCES PART(PART_NO),
  CONSTRAINT PK_ITEM PRIMARY  KEY (ITEM_NO, PART_NO) 

);

In the above tables, there is a one-to-many relationship between the ITEM and PART tables. An item may have no parts assigned to build it, or it may have one or many parts that go into building the part.

In this example, what we are really interested in is the creation of the ITEM table. First, note that we defined the primary key as an out of line primary key. This is because it is a composite primary key and composite primary keys have to be defined out of line. Now, we are interested in the foreign key definition. You must define foreign key constraints as out of line constraints, as we have done in our example. Here is a snippet of the command that we used:

CONSTRAINT FK_ITEM_PART_NO FOREIGN KEY (PART_NO) REFERENCES PART(PART_NO)

Note that we start out using the constraint keyword. This tells Oracle that we are about to define a constraint. Then, we name the constraint. In this case we named the constraint FK_ITEM_PART_NO. Constraint names have to be unique for each table and can be no more than 30 characters in length. We then use the foreign key keyword to indicate that we are defining a foreign key constraint. Next we define the column in the table being created that this foreign key belongs to.

Next we use the references keyword to indicate that we are going to define the table and column that this foreign key references. The referenced table name is next listed. In this case, it's the PART table. Finally the column in the PART table that is being referenced is listed, PART_NO.

If you need to add foreign key constraints to a table after the fact, simply use the alter table command as seen here:

ALTER TABLE ITEM ADD CONSTRAINT fk_ITEM_NO FOREIGN KEY (PART_NO) REFERENCES PART(PART_NO);