Database Constraints

A wise man once said: "Data must protect itself!". This is exactly what database constraints do. A rule that restricts the values in a database to maintain the valid set of data. Most databases available today lets you create six types of Constraints. You can declare these constraints during table creation or after table creation. Isn't this nice?, you can modify the constraint anytime!

These six types of Integrity Constraints are described briefly below. Knowing these constraints will save developers and DBA's time. I have seen developer implementing these check or features outside of database therefore it is better to ask if your databases have that features you are trying to implement in your application.

Oracle Database Constraints 

1. A NOT NULL constraint prohibits a database value from being null.
2. A UNIQUE Constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be NULL. Unique is case sensitive meaning the values ‘ABC’ and ‘abc’ are accepted. (No duplicates, case sensitive, and  NULL Allowed)
3. A PRIMARY KEY Constraint combines a NOT NULL constraint and a unique constraint in a single declaration. That is, it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null. (Unique, indexed,  & Check NOT NULL )
4. A FOREIGN KEY Constraint requires values in one table to match values in another table
5. .A CHECK Constraint requires a value in the database to comply with a specified condition.
6. A REF column by definition references an object in another object type or in a relational table. A REF Constraint lets you further describe the relationship between the REF column and the object it references.

 CONSTRAINT_TYPE ( Oracle)
                C – Check constraint on a table
                P – Primary key
                U – Unique key
                R – Referential integrity
                V – With check option, on a view
                O – With read only, on a view
                H – Hash expression
                F – Constraint that involves a REF column
                S – Supplemental logging

To demo the examples of database constraints, we are going to use an employee table.

CREATE TABLE employee 
  ( 
     emp_id  NUMBER(10), 
     first   VARCHAR(15), 
     last    VARCHAR(20), 
     age     NUMBER(3), 
     address VARCHAR(30), 
     city    VARCHAR(20), 
     state   VARCHAR(20), 
     phone   VARCHAR(12), 
  );

Constraint Examples:

NOT NULL:

ALTER TABLE employee 
  MODIFY (first NOT NULL); 

UNIQUE:

ALTER TABLE employeee  ADD CONSTRAINT phone_unique
UNIQUE( phone);


PRIMARY KEY:

ALTER TABLE employeee 
  ADD CONSTRAINT phone_unique UNIQUE( phone);

FOREIGN KEY:

Check Blog for this.

CHECK:

ALTER TABLE employee ADD (CONSTRAINT check (state ='NE' 
OR          state ='MO' 
OR          state ='CA'));

REF:
Check Oracle Document

Creating Primary Key will create ( CHECK NOT NULL Constraint and  UNIQUE Index. Remember, ‘abc’ and ‘ABC’ are two different record and the primary key accepts both. In reality they both are same and you can't ask the user to enter everything in upper case. How do you make your PK case insensitive and insert into database all in CAPS.

Solution 1:
  Syntax: CREATE UNIQUE INDEX index_name    ON my_table (Upper(column_name));

Solution 2:
Syntax: CREATE UNIQUE INDEX index_name    ON my_table (Lower(column_name));

Here, we created functional index on both the solutions to treat a string 'acb' same as 'ACB' or in other word make the constraint case insensitive.  You now have a solution that works and the blog does not end here.You need to explain how functional index solved your issue?