Oracle Constraints

By Paulus, 14 February, 2007

Constraints are used to keep accurate data. In oracle you can apply these policies to a column or table. There are five different types of constraints:

Constraint Abbreviation Description
PRIMARY KEY _pk This is a unique field that is not allowed to have null values.
FOREIGN KEY _fk This constraint is placed in the "many" table. The data that's entered in this field must have a value in the parent table.
UNIQUE _uk All data that is stored in a column with a UNIQUE constraint on it will not have two of the same values. This constraint is a bit different from a PRIMARY KEY where the PRIMARY KEY does not allow NULLs, a UNIQUE constraint does allow NULLs.
CHECK _ck This constraint ensures that a specified condition is true before any data is added to the table.
NOT NULL _nn This makes sure that there is a value going into the table.

Altering Tables to Add Constraints

One way of adding constraints is to add them after a table is created.

Syntax

ALTER TABLE tablename
ADD CONSTRAINT [constraint_name] PRIMARY KEY(col1,col2,...);

ALTER TABLE tablename
ADD CONSTRAINT [constraint_name] FOREIGN KEY(colname)
REFERENCES referencedtablename (referencedcolumnname);

ALTER TABLE tablename
ADD CONSTRAINT [constraint_name] UNIQUE(colname)

ALTER TABLE tablename
ADD CONSTRAINT constraint_name
CHECK (condition);

ALTER TABLE tablename
MODIFY (columnname [CONSTRAINT constraintname]
NOT NULL);

Example

ALTER TABLE ex_table
ADD CONSTRAINT ex_table_pk ex_table_pk PRIMARY KEY(id);

or for composite primary keys:

ALTER TABLE ex_table
ADD CONSTRAINT ex_table_pk ex_table_pk PRIMARY KEY(id,uid);

ALTER TABLE ex_table
ADD CONSTRAINT ex_table_customer#_fk FOREIGN KEY(customer#)
REFERENCES exampletable (customer#) [ON DELETE CASCADE];

ALTER TABLE ex_table
ADD CONSTRAINT ex_table_uk UNIQUE(uid);

ALTER TABLE orders
ADD CONSTRAINT orders_chipdate_ck CHECK(shipdate=>orderdate);

ALTER TABLE ex_table
MODIFY ( name CONSTRAINT ex_table_name_nn NOT NULL);
In the event that you need to delete a record from the parent table that is referenced by another table; you won't be able to do that until you delete all the data in the child table first. A foreign key can not reference a table that does not have a primary key assigned. If you want to delete a table that is linked by a foreign key you have two options:

  1. Drop the child table and then drop the parent.
  2. Drop the parent table with the CASCADE CONSTRAINTS option. This will delete the foreign key constraint in the child and drop the parent. The command for dropping the parent table along with the constraints is:
    ALTER TABLE tablename
    CASCADE CONSTRAINTS

The CHECK constraint is pretty straight forward. You're making a statement and telling Oracle that if it's not true then don't insert any data into the table. Some of the operators that you can use are as follows:

  • Less Than ( < )
  • Greater Than ( > )
  • Range ( Between ): commission BETWEEN 50 AND 1000
  • List of values ( IN ): direction IN ('N', 'S', 'E', 'W')

The NOT NULL constraint can only be added by modifying a table, using the MODIFY clause.

As you create tables, it's advisable to create the constraints as soon as possible. That way there is no time for users to insert any invalid data into the table. The syntax is a mixture of a CREATE statement and some CONSTRAINT statements.

CREATE TABLE exampletable
( id NUMBER(4),
name VARCHAR2(30) CONSTRAINT exampletable_name_nn NOT NULL,
uid NUMBER(4),
date_added DATE DEFAULT SYS_DATE,
CONSTRAINT exampletable_id_pk PRIMARY KEY (id),
CONSTRAINT exampletable_uid_fk FOREIGN KEY(uid)
REFERENCES ex_table(id));

In the example above you can see that you can place constraints after a column definition or after a list of column definitions.

View Constraints

To view the constraints on a table you can issue the following query to tell you what table the constraints apply, the name of the constraint, the condition, and if there is a search condition:

SELECT table_name, constraint_name, constraint_type, search_condition
FROM user_constraints;

After determining what constraints are what and what they affect you can then enable them, disable them or completely drop them.

Disabling, Enabling and Dropping Constraints

For whatever reason, you may want to disable a constraint and later enable it again. Or you may even want to completely drop the constraint.

Syntax

ALTER TABLE tablename
DISABLE CONSTRAINT constraintname;

ALTER TABLE tablename
ENABLE CONSTRAINT constraintname;

ALTER TABLE tablename
DROP PRIMARY KEY | UNIQUE (columname);

or

ALTER TABLE tablename
DROP CONSTRAINT constraintname;

Example

ALTER TABLE exampletable
DISABLE CONSTRAINT exampletable_name_nn;

ALTER TABLE exampletable
ENABLE CONSTRAINT exampletable_name_nn;

ALTER TABLE exampletable
DROP PRIMAY KEY(id);

or

ALTER TABLE exampletable
DROP exampletable_name_nn;

When dropping a constraint there are some guidelines that should be noted:

  • The DROP clause varies on the type of constraint that is being dropped. You can use the PRIMARY KEY keywords because only one clause is allowed per table.
  • Deleting a UNIQUE constraint, you can name the column being affect because there is only one UNIQUE constraint that is attached to that column.
  • Any other constraint must be reference by name. If no name was given at the time that the constraint was created then Oracle assigned it a name. This is not a pretty name either.

A FOREIGN KEY constraint requires special attention.A FORIEGN KEY column is referencing a PRIMARY KEY in another table. If we try to drop the constraint when there are foreign keys, Oracle will error out. When droping a FOREIGN KEY, If you desire so you can delete the FOREIGN KEY along with the PRIMARY KEY:

ALTER TABLE exampletable
DROP PRIMARY KEY CASCADE;