Oracle Constraints

Oracle constraints are critical to the scalability, flexibility and integrity of your database data. Constraints apply specific rules to data, ensuring the data conforms to the requirements defined. There are a number of different kinds of constraints that you will be concerned with as a DBA.

There are a following types of Constraints

• NOT NULL: A column with this constraint will not allow NULL values.
• PRIMARY KEY: There can be only one primary key column in a table. This will only UNIQUE values. Does not allow NULL values.
• UNIQUE KEY: We can have any number of primary keys in a table. UNIQUE Constraint also would accept only UNIQUE values.
• FOREIGN KEY: A foreign key is a combination of columns with values based primary. It is also known as referential integrity constraint. Values that a foreign key can take are the values that are present in primary key.
• CHECK: This constraint defines a condition which need to be satisfied by the value entering into the table.

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 MY_STATUS table contained two examples of NOT NULL constraints being defined. For example, the PERSON_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 supplier MODIFY (supplier_id NOT NULL);

Primary Key Constraints

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.

Example:

CREATE TABLE supplier
(
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

If table is already created then you can use ALTER TABLE Command to add Constraint.

ALTER TABLE supplier ADD CONSTRAINT supplier_pk 
PRIMARY KEY (supplier_id);

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.

Example:

ALTER TABLE supplier ADD CONSTRAINT  uk_supplier_name
UNIQUE (supplier_name);

Foreign Key Constraints

A Foreign Key Constraint is used to enforce a relationship between two tables.

The referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table will generally reference a primary key in the parent table.

A foreign key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

Example:

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier(supplier_id)
);

ALTER TABLE products add CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id)  REFERENCES supplier(supplier_id);

Check Constraints

Oracle check constraint insures that updated or inserted values meet a specific condition. The Oracle check constraint check condition must return a TRUE or FALSE, much Like the WHERE clause. If the Oracle check constraint condition returns as TRUE when you use Oracle check constraint, the value is accepted by the constraint. If Oracle check constraint returns the condition as FALSE, the value is rejected. Below, we include an Oracle check constraint on the supplier_id column of the suppliers table that insures the value is BETWEEN 100 and 9999.

A check constraint can be defined in either an CREATE TABLE statement or an ALTER TABLE statement.

Example:

CREATE TABLE suppliers
(
  supplier_id numeric(4),
  supplier_name varchar2(50),
  CONSTRAINT check_supplier_id
  CHECK (supplier_id BETWEEN 100 and 9999)
);

By using ALTER TABLE statement.

ALTER TABLE suppliers add CONSTRAINT check_supplier_name
  CHECK (supplier_name IN ('IBM', 'Microsoft', 'NVIDIA'));

Drop a Check Constraints

ALTER TABLE suppliers
drop CONSTRAINT check_supplier_id;

Enable/Disable a Check Constraints

ALTER TABLE suppliers
enable CONSTRAINT check_supplier_id;

ALTER TABLE suppliers
disable CONSTRAINT check_supplier_id;