www.bullraider.com

Oracle constraint

AddThis Social Bookmark Button
  • An integrity constraint is a rule that restricts the values for one or more columns in a table or a view.
  • Use the constraint clause in a CREATETABLE  or ALTERTABLE statement to define an integrity constraint.
  • Constraint clauses can appear in CREATETABLE, ALTERTABLE, CREATEVIEW, or ALTERVIEWstatements.

To create a referential integrity constraint, the parent table must be in your own schema, or you must have the REFERENCES privilege on the columns of the referenced key in the parent table.
ORACLE supports five types of constraints :

  • UNIQUE : Ensures that there are no duplicate values for the column(s) specified.
  • NOT NULL : Prevents NULL values from being entered into the column. These types of constraints are defined on a single column.
  • CHECK : Checks whether the condition specified in the constraint is satisfied.
  • PRIMARY KEY : Uniquely identifies each row of the table and prevents NULL values. A table can have only one primary key constraint.
  • FOREIGN KEY: Establishes a parent-child relationship between tables by using common columns. The foreign key defined on a table refers to the primary key or unique key of another table.

UNIQUE CONSTRAINT

Example:
CREATE TABLE emp
(empno NUMBER(4) UNIQUE,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredateDATE,
salNUMBER(7,2),
) ;

Alternative way:
ALTER TABLE customers
ADD CONSTRAINT emp_UNIQUE (empno )

PRIMARYKEY CONSTRAINT
CREATE TABLE  location
( loc_id NUMBER(4)
CONSTRAINT  loc_id_pkPRIMARY KEY
, street_address VARCHAR2(40)
, postal_code VARCHAR2(12)
, city  VARCHAR2(30)
, country_id  CHAR(2)
) ;

Alternative way:
ALTER TABLE sales
ADD PRIMARY KEY (loc_id) ;
NOT NULL AND NULL  CONSTRAINT

CREATE TABLE  location
( loc_id NUMBER(4)
, street_address VARCHAR2(40)
, postal_code VARCHAR2(12)
, city  VARCHAR2(30)
, country_id  CHAR(2) NOT NULL
) ;

Alternative way:
ALTER TABLE locations
MODIFY (country_id
CONSTRAINT country_notnull NOT NULL);
CHECK CONSTRAINT
CREATE TABLE dept
(deptno NUMBER CONSTRAINT check_deptno
CHECK (deptnoBETWEEN 10 AND 70),
dnameVARCHAR2(9) CONSTRAINT check_dname
CHECK (dname= UPPER(dname)),
loc VARCHAR2(10) CONSTRAINT check_loc
CHECK (loc IN
('INDIA','AMERICA','UK')));

Another Example
CREATE TABLE emp
(empnoNUMBER(4),
enameVARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredateDATE,
salNUMBER(7,2),
commNUMBER(7,2),
deptnoNUMBER(2),
CHECK( (sal+ comm<= 15000)  And comm is NOT NULL );

FOREIGN_KEY  CONSTRAINT
CREATE TABLE emp
(empnoNUMBER(4),
enameVARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredateDATE,
salNUMBER(7,2),
commNUMBER(7,2),
deptno CONSTRAINT fk_deptno REFERENCES dept(deptno) );

Alternative way
CREATE TABLE emp
(empnoNUMBER(4),
enameVARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredateDATE,
salNUMBER(7,2),
commNUMBER(7,2),
deptno,
CONSTRAINT fk_deptno
FOREIGN KEY (deptno)
REFERENCES dept(deptno) );
ON DELETE Example
This statement creates the emp table, defines and enables two referential integrity constraints, and uses the ON DELETE clause:
CREATE TABLE emp
(empnoNUMBER(4) PRIMARY KEY,
enameVARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4) CONSTRAINT fk_mgr
REFERENCES empON DELETE SET NULL,
hiredateDATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptnoNUMBER(2) CONSTRAINT fk_deptno
REFERENCES dept(deptno)
ON DELETE CASCADE );
 The first ONDELETEclause, if manager number 1001 is deleted from the emptable, Oracle sets to null the value of mgr for all employees in the emp table who previously had manager 1001.
The second ONDELETE clause, Oracle cascades any deletion of a deptno value in the dept table to the deptno values of its dependent rows of the emp table
Enabling and Disabling Constraints
We cannot disable a primary key or unique key if foreign keys that are enable reference it.
To disable all the referenced foreign keys and the primary or unique key, specify CASCADE. Enabling primary key constraint later will not enable the foreign key.
ALTER TABLE emp DISABLE CONSTRAINT dept_foreignkey;
ALTER TABLE emp ENABLE CONSTRAINT uq_employee;
 


Powered by Bullraider.com