Please tell me about constraints in oracle

by Vinod



What are the various types of constraints in oracle (table level and column level) and what is the difference?

There are 6 different types of constraints available in Oracle:
NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY (referential)
and REF. All but the NOT NULL constraint can be defined at either the table level (out of line) or the column level (in line).

NOT NULL constraints prevent column values being left undefined and so can only be defined at the column level.

Example:
CREATE TABLE customer(
customer_id NUMBER NOT NULL,
customer_name VARCHAR2(80)
customer_address VARCHAR2(2000)
)

When a column is defined as NOT NULL, a value must be supplied for that column in the insert and update statements (unless a default value is specified).

Therefore, using the above table definition, this SQL insert statement
INSERT INTO customer (customer_name) VALUES ('Yellow Pages');
would generate the Oracle exception
ORA-01400: cannot insert NULL into ("CUSTOMER"."CUSTOMER_ID")
and this update statement
UPDATE customer SET customer_id = NULL;

would generate the Oracle exception
ORA-01407: cannot update ("CUSTOMER"."CUSTOMER_ID") to NULL

The other constraints must be defined at the table level if they apply to more than one column but they can still be defined at the table level even if they only apply to one column (via a CREATE TABLE or ALTER TABLE statement).

CHECK constraints can be applied to one or more columns and are used to restrict the range of allowable values for those columns.

For example suppose we define a CUSTOMER table in our Oracle database and want to ensure that the total_orders column is always positive, we could define it as follows:

CREATE TABLE customer(
customer_id NUMBER NOT NULL,
customer_name VARCHAR2(80)
customer_address VARCHAR2(2000)
total_orders NUMBER CONSTRAINT orders_gt_zero CHECK (total_orders > 0)
)

UNIQUE and PRIMARY KEY constraints are similar in that they both enforce uniqueness (via an index) but there are some important differences to note.

A table can have only one primary key - which can span as many columns as you like - and all columns in the primary key must also be NOT NULL. If every column in the table is part of the primary key then you have an index-organised table.

There can be as none, one or many different unique columns on a table each with a different index or they can all be part of the same index. Example:

CREATE TABLE customer_orders(
order_no NUMBER ,
customer_id NUMBER,
customer_ref NUMBER UNIQUE
...
CONSTRAINT cust_orders_pk PRIMARY KEY (order_no NUMBER , customer_id NUMBER) )


FOREIGN KEY (referential) constraints are used to define the relationships between tables and another and to prevent orphaned records (to ensure for example that no orders are placed for non-existent customers).

Example:

CREATE TABLE customer_orders(
order_no NUMBER ,
customer_id NUMBER CONSTRAINT fk_custno REFERENCES customer(customer_id),
customer_ref NUMBER
...
)


A FOREIGN KEY constraint must refer to a unique or primary key column in the referenced table.

REF constraints apply to REF columns which reference an object in another object type or in a relational table and enable you to define a scope constraint, a rowid constraint, or a referential integrity constraint.

You might also be interested in this question about the differences between constraints and triggers and in our Oracle tutorials.

See our Oracle training page if you're interested in some formal training either on-line or in person.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.