Ex:
TABLE LEVEL
SQL> create table emp(empno number(2),
ename varchar(10), deptno number(2),
primary key(empno), foreign key(deptno) references dept(deptno));
SQL> create table emp(empno number(2),
ename varchar(10), deptno number(2),
constraint pk primary key(empno), constraint fk foreign key(deptno)
references
dept(deptno));
ALTER LEVEL
SQL> alter table emp add foreign
key(deptno) references dept(deptno);
SQL> alter table emp add constraint fk foreign key(deptno)
references dept(deptno);
Once
the primary key and foreign key relationship has been created then you can not
remove any parent record if the dependent childs exists.
USING
ON DELTE CASCADE
By
using this clause you can remove the parent record even it childs exists.
Because when ever you remove parent record oracle
automatically removes all its dependent records from child table, if this
clause is present while creating foreign key constraint.
Ex:
TABLE LEVEL
SQL> create table emp(empno number(2),
ename varchar(10), deptno number(2),
primary key(empno), foreign key(deptno) references dept(deptno) on
delete
cascade);
SQL> create table emp(empno number(2),
ename varchar(10), deptno number(2),
constraint pk primary key(empno), constraint fk foreign key(deptno)
references
dept(deptno) on delete cascade);
ALTER LEVEL
SQL> alter table emp add foreign
key(deptno) references dept(deptno) on delete
cascade;
SQL>
alter table emp add constraint fk foreign key(deptno) references dept(deptno)
on
delete cascade;
COMPOSITE
KEYS
A
composite key can be defined on a combination of columns.
We
can define composite keys on entity integrity and referential integrity
constraints.
Composite
key can be defined in table and alter levels only.
Ex:
UNIQUE (TABLE LEVEL)
SQL> create table student(no number(2)
, name varchar(10), marks number(3),
unique(no,name));
SQL> create table student(no number(2)
, name varchar(10), marks number(3),
constraint un unique(no,name));
UNIQUE (ALTER LEVEL)
SQL> alter table student add
unique(no,name);
SQL> alter table student add constraint
un unique(no,name);
PRIMARY KEY (TABLE LEVEL)
SQL> create table student(no number(2)
, name varchar(10), marks number(3),
primary key(no,name));
SQL> create table student(no number(2)
, name varchar(10), marks number(3),
constraint pk primary
key(no,name));
PRIMARY KEY (ALTER LEVEL)
SQL> alter table student add primary
key(no,anme);
SQL> alter table student add constraint
pk primary key(no,name);
FOREIGN KEY (TABLE LEVEL)
SQL> create table emp(empno number(2),
ename varchar(10), deptno number(2),
dname
varchar(10), primary key(empno), foreign key(deptno,dname) references
dept(deptno,dname));
SQL>
create table emp(empno number(2), ename varchar(10), deptno number(2),
dname
varchar(10), constraint pk primary key(empno), constraint fk foreign
key(deptno,dname) references
dept(deptno,dname));
FOREIGN KEY (ALTER LEVEL)
SQL> alter table emp add foreign
key(deptno,dname) references dept(deptno,dname);
SQL>
alter table emp add constraint fk foreign key(deptno,dname) references
dept(deptno,dname);
DEFERRABLE
CONSTRAINTS
Each
constraint has two additional attributes to support deferred checking of
constraints.
1 Deferred initially immediate
2 Deferred initially deferred
Deferred
initially immediate checks for constraint violation at the time of insert.
Deferred
initially deferred checks for constraint violation at the time of commit.
Ex:
SQL> create table student(no number(2), name varchar(10), marks
number(3),
constraint un unique(no) deferred
initially immediate);
SQL> create table student(no number(2), name varchar(10), marks
number(3),
constraint un unique(no) deferred
initially deferred);
SQL> alter table student add constraint un unique(no) deferrable
initially deferred;
SQL> set constraints all immediate;
This will enable all the constraints
violations at the time of inserting.
SQL> set constraints all deferred;
This will enable all the constraints violations at the time of
commit.
OPERATIONS
WITH CONSTRAINTS
Possible
operations with constraints as follows.
3 Enable
4 Disable
5 Enforce
6 Drop
ENABLE
This
will enable the constraint. Before enable, the constraint will check the
existing data.
Ex:
SQL> alter table student enable constraint un;
DISABLE
This
will disable the constraint.
Ex:
SQL> alter table student enable constraint un;
ENFORCE
This
will enforce the constraint rather than enable for future inserts or updates.
This
will not check for existing data while enforcing data.
Ex:
SQL> alter table student enforce constraint un;
DROP
This
will remove the constraint.
Ex:
SQL> alter table student drop constraint un;
Once the table is dropped, constraints
automatically will drop.
CASE AND DEFAULT
CASE
Case is similar to decode but easier to understand while
going through coding
Ex:
SQL> Select sal,
Case sal
When 500 then ‘low’
When 5000 then ‘high’
Else ‘medium’
End case
From emp;
SAL CASE
----- --------
500 low
2500 medium
2000 medium
3500 medium
3000 medium
5000 high
4000 medium
5000 high
1800 medium
1200 medium
2000 medium
2700 medium
2200 medium
3200 medium
DEFAULT
Default can be considered as a substitute
behavior of not null constraint when applied to new rows being entered
into the table.
When you define a column with the default keyword
followed by a value, you are actually telling the database that, on insert if a
row was not assigned a value for this column, use the default value that you
have specified.
Default is applied only during insertion of new rows.
Ex:
SQL> create table student(no number(2)
default 11,name varchar(2));
SQL> insert into student values(1,'a');
SQL> insert into student(name)
values('b');
SQL> select * from student;
NO NAME
------ ---------
1 a
11 b
SQL> insert into student values(null,
‘c’);
SQL> select * from student;
NO NAME
------ ---------
1 a
11 b
C
-- Default can not override nulls.
No comments:
Post a Comment