CONSTRAINTS - ORACLE

CONSTRAINTS: constraint is an OBJECT.
------------
NOT NULL
PRIMARY KEY (NULL,Duplicates are not allowed. Referential integrity)
UNIQUE KEY (Null values are allowed)
FOREIGN KEY
CHECK

unique key: When ever user create unique key, oracle automatically it creates unique index.
It will check and wil not allow to enter duplicates.

Contraint are classified into two:
1. User named contrained.
2. System named Constrained. (if user dint specified constraint, then oracle will name it).

Creating a System Named Contraint:

create table t99(code number PRIMARY KEY,
name char(10) NOT NULL,
age number CHECK (age>20),
salary number UNIQUE);

select constraint_name, constraint_type from user_constraints
where table_name='t600' and owner='user30';

How to create foreigh key constraint?

create table master (dcode number PRIMARY KEY, dname char(30));
create table child1 (name char(30), dcode number REFERENCES master(dcode));

* The value user enter in child table should have associated primary key value.
* You cannot delete record from parent if there is any associated record in child.

create table child1(name char(10), dcode number REFERENCES master(dcode) ON DELETE CASCADE);

create table child1(name char(10), dcode number REFERENCES master(dcode) ON DELETE SET NULL);


Creating a User Named Contraint:

How to create a USER NAMED CONSTRAINT?
create table t601(code number CONSTRAINT code_pk PRIMARY KEY,Salary number CONSTRAINT sal_un UNIQUE);

select constraint_name, constraint_type from user_constraints
where table_name='t600' and owner='user30';

CONSTRAINT_NAME C
___________________________________
CODE_PK P
SAL_UN U

How to create a usernamed Foreign key constraint?
create table child2(dcode number CONSTRAINT f_key REFERENCES master(dcode));

TABLE LEVEL CONSTRAINT:

When user wants to create composite primary key, then we will go for table level constraint.
Eg. if you want to create primary key on two columns, then we go this option.

**IMP*** A NOT NULL connot be a table level constraint.
**IMP*** NOT NULL always coulmn level constraint.

create table t602(code number,salary number,age number, CONSTRAINT pk3 PRIMARY KEY(code));

How to go for table level foreign key constraint?
create table child3(dcode number,dname char(10),CONSTRAINT fk_key FOREIGN KEY(dcode) REFERENCES master(dcode));

Later how to add a new constraint?

alter table t700 ADD PRIMARY KEY(code);
alter table t700 ADD CONSTRAINT un_key UNIQUE(salary);

Exemption for NOT NULL: Since not null supported at column level not at table level
Use MODIFY
ALTER table child1 MODIFY name CONSTRAINT NN NOT NULL;
Alter table tablename DROP Constraint
alter table tablename DISABLE CONSTRAINT cont_key CASCADE;
alter table tablename ENABLE constraint

alter table test1 DROP (Column name) CASCADE CONSTRAINTS;
alter table test1 DROP (Col1,COl2,Col3) CASCADE CONSTRAINTS;

alter table t100 rename coulmn code to ecode;