can we use more than one constraint on perticuler column?

is it possible to gave more than one constraint on particular column?


Comments for can we use more than one constraint on perticuler column?

Average Rating starstarstarstarstar

Click here to add your own comments

Nov 07, 2016
Rating
starstarstarstarstar
Multiple constraints are allowed
by: John

It certainly is possible to have multiple constraints defined on a single column. In fact the syntax for defining constraints in Oracle allows you to define up to 6 different types of constraints on the same column and an infinite number of constraints in total. However, as always, just because you can do something it doesn't mean that it's a good idea to do it! Your database and application design should conform to the use of Occam's razor in that everything should be as simple as possible but no simpler.

Here is an example of a CREATE TABLE statement defining multiple (and overlapping) constraints on a column.


create table xxx (id number not null, constraint xxx_1 check (id > 100), constraint xxx_2 foreign key (id) references employees(employee_id), constraint xxx_3 primary key (id)
, constraint xxx_4 check (id < 1000), constraint xxx_5 foreign key (id) references employees(employee_id));


As you can see the column id in the table is defined as not null and as the primary key. The primary key constraint by definition ensures a column is not null. There are also 2 check constraints. Both checks could have been defined in the same constraint. Also there are 2 foreign key constraints both of which reference employee_id in the employees table.

See the Oracle Database Administrator's Guide and/or the SQL Language Reference for the constraint definition syntax< amd see our SQL tutorials for more help with SQL.

Click here to add your own comments

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