CONSTRAINTS:
---------------------
Constraints
is rule or restriction which is imposed on table data.
we
can create constraints on new table using CREATE command.
For
existing table in database we can create constraints through ALTER command.
constraints
are used to enforce business rules on data.
CATAGORIES
OF THE CONSTRAINTS:
---------------------------------------------------
Domain
integrity constraints:
------------------------------
1.NOT
NULL
2.CHECK
Entity
integrity constraints:
-----------------------------
1.UNIQUE
2.PRIMARY
KEY
Referential
integrity constraints:
----------------------------------
1.FORIEGN
KEY
Different
ways to add constraints:
----------------------------------------
1.COLUMN
LEVEL --along with the column definition
2.TABLE LEVEL
--after the table different
3.ALTER LEVEL
--using ALTER command
Constraint
names:
--------------------
1.constraint
name is unique to the owner.
2.if
a constraint name is not specified ,the oracle server assign a unique name with
the format
SYS_C00N
3.A
suggested format for naming a constraint is
TABLENAME_CONSTRAINTTYPE_COLNAME
EMP_NN_SAL
USER
CONSTRAINTS: information about the columns in constraints owned by the user.
-----------------
SELECT
CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION
FROM
USER_CONSTRAINTS
WHERE
TABLE_NAME=<table_name>;
ALL
CONSTRAINTS: constraints definitions on
accessible table.
--------------------------
SELECT
CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION
FROM
ALL_CONSTRAINTS
WHERE
TABLE_NAME=<table_name>;
DBA
CONSTRAINTS: defintions on all tables.
---------------------------
SELECT
CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION
FROM
DBA_CONSTRAINTS
WHERE
TABLE_NAME=<table_name>;
NOT
NULL CONSTRAINT:
----------------------------------
‘NOT
NULL’ constraints are used to avoid null values for a specific column.
We
can add ‘NOT NULL’ constraint at column level only.
Columns
without the ‘NOT NULL’ constraint allow NULL values. (i.e. if no value is
specified then NULL is the default) .
If
Constraint name is not specified then system will generate a unique name for
the constraint.
CONSTRAINT
WITHOUT NAME:
--------------------------------------------
CREATE TABLE emp (empno NUMBER NOT NULL,
ename
VARCHAR2 (100),
sal
NUMBER (5));
If we
want to give a name to the constraint, we have to use the constraint clause.
CONSTRAINT
WITH NAME:
---------------------
SQL>
Create TABLE emp (empno NUMBER
CONSTRAINT emp_nn NOT NULL,
ename
VARCHAR2 (100),
sal
NUMBER (5));
By
using below query we can know the name of the constraint attached to a
particular a table.
SQL>
SELECT constraint_name,
constraint_type,
search_condition
FROM user_constraints
WHERE table_name=’EMP’;[/sql]
TEST
CONSTRAINT:
---------------------------
Suppose
if we try to insert null values into table column which has NOTNULL constraint
then system will raise an error.
SQL>
INSERT INTO emp (empno, ename, sal)
VALUES (null,’MARK’, 2000);
Output:
ERROR
at line 1:
ORA-01400:
cannot INSERT NULL into ("EMP"."EMPNO")
ALTER
LEVEL FOR NOT NULL CONSTRAINTS:
-------------------------------------
ALTER
TABLE table_name
MODIFY column_name datatype[(size)]
NOT NULL;
CHECK
CONSTRAINT:
-------------------------------
we
can defined CHECK CONSTRAINT in three levels
column
level
table level
alter level
column
level: without Constraint Name
---------------------------------------
create
table sample
(sno number(4),
sname
varchar2(30),
salary
number(5) CHECK (salary<5000)
);
With
Constraint Name:
---------------------
create
table sample
(sno
number(4),
sname
varchar2(30),
salary number(5) CONSTRAINT SAMPLE_C_SALARY
CHECK (salary<4000)
);
Table
level: without Constraint Name
------------------------------------
create
table sample
(sno
number(5),
sname varchar2(30),
salary number(5),
CHECK (salary<5000)
);
With
Constraint Name:
---------------------
create
table sample
(sno
number(5),
sname varchar2(30),
salary number(5),
CONSTRAINT sample_ck_salary CHECK
(salary<5000)
);
alter level:
------------
alter table sample ADD CONSTRAINT CHECK
sample_ck_salary CHECK (salary<5000);
alter table sammple DROP CONSTRAINT CHECK
sample_ck_salary CHECK (salary<5000);
UNIQUE CONSTRAINT: Unique constraint a void
duplicate data .but it allows null values.
-------------------------------
we can defined UNIQUE CONSTRAINT in three
levels
column level
table
level
alter level
column
level :without constraint name
-------------------------------------
create
table sample
(
sno number(5) UNIQUE,
sname
varchar2(30),
dob
date
);
with
constraint name:
---------------------
create
table sample
(sno number(5) CONSTRAINT sample_u_sno UNIQUE,
sname
varchar2(30),
dob
date
);
table level: without constraint name
-------------------------------------
create table sample
( sno
number(5),
sname
varchar2(30),
dob
date,
UNIQUE(sno)
);
with constraint name:
-------------------------
create table sample
( sno
number(5),
sname varchar2(30),
dob
date,
CONSTRAINT sample_u_sno UNIQUE(sno)
);
Alter level:
------------
alter table sample ADD CONSTRAINT sample_u_sno
UNIQUE(sno);
alter table sample DROP CONSTRAINT
sample_u_sno UNIQUE(sno);
PRIMARY KEY CONSTRAINT: primary key is avoid
duplicates and null values.
-----------------
-----------------------
column level
table
level
alter
level
column level: without constraint name
--------------
create table sample
( sno
number(5) PRIMARY KEY,
sname varchar2(30),
dob
date
);
with constraint name:
---------------------
create table sample
( sno
number(5) CONSTRAINT sample_pk_sno PRIMARY KEY,
sname varchar2(30),
dob
date
);
table level: without constraint name
-----------------------------------
create table sample
( sno
number(5),
sname varchar2(30),
dob
date,
PRIMARY KEY (sno)
);
with constraint name:
---------------------
create table sample
( sno
number(5),
sname varchar2(30),
dob
date,
CONSTRAINT sample_pk_sno PRIMARY
KEY(sno)
);
COMPOSITE
KEY:
------------------------
composite key defined in table level
only.defined primary key on more than one column. we can take a maximum
32 columns in composite primary key.
create table sample
(sno
number(5),
sname varchar2(30),
dob
date,
PRIMARY KEY (sno,sname)
);
alter level:
------------
constraint without name:
------------------------
alter table sample ADD PRIMARY KEY (sno);
constraint with name:
---------------------
alter table sample ADD CONSTRAINT
sample_pk_sno PRIMARY KEY (sno);
FORIEGN KEY CONSTRAINT:
----------------
-----------------------
foreign keys are used to refer the parent
table primary key column which does not allow duplicates.
for a parent child relationship across table
foreign key is always attached to the child table.
column level
table
level
alter
level
**to demonstrate the foreign key constraint we
need parent child relationship between tables.thats why first
we create parent table/master table as a dept
make deptno column as primary key**
create table dept
( deptno
number(3) PRIMARY KEY,
dname
varchar2(30),
------this table is parent table/master table----
loc varchar2(30)
);
column level : without constraint name:
---------------------------------------
create table emp
(empno
number(3),
ename
varchar2(30),
sal
number(5),
deptno
number(4) REFERENCES dept(deptno)
);
with constraint name:
---------------------
create table emp
(empno
number(3),
ename
varchar2(30),
sal
number(5),
deptno
number(4) CONSTRAINT emp_fk_deptno
REFERENCES dept(deptno)
);
table level: without constraint name
--------------------------------------
create
table emp
(empno
number(3),
ename
varchar2(30),
sal
number(5),
deptno
number(4),
FORIEGN KEY(deptno) REFERENCES dept(deptno)
);
with constraint name:
---------------------
create table emp
(empno
number(3),
ename
varchar2(30),
sal
number(5),
deptno
number(4),
CONSTRAINT emp_fk_deptno FORIEGN KEY(deptno)
REFERENCES dept(deptno)
);
Alter level: without constraint name
-------------------------------------
alter table emp ADD FORIEGN KEY(deptno)
REFERENCES dept(deptno);
with constraint name:
---------------------
alter
table emp ADD CONSTRAINT emp_fk_deptno FORIEGN KEY(deptno) REFERENCES
dept(deptno);
ON
DELETE CASCADE:
===================
on
delete cascade is extension to Foreign key constraint.
1.if
a parent child relationship existing tables than we will not able to delete the
parent record
when
child exist for it.
2.By
using ‘ON DELETE CASCADE’ clause we can remove the parent record even it Childs
exists.
Because
with ‘ON DELETE CASCADE’ whenever you remove parent record system automatically
removes
all its dependent records from child table.
column
level
table level
alter level
parent
table:
-------------
create
table dept
(
deptno number(5) PRIMARY KEY,
dname
varchar2(30),
loc
varchar2(30)
);
column
level: without constraint name:
-------------------------------------
child
table:
-------------
create
table emp
(empno number(4),
ename
varchar2(30),
sal
number(3),
deptno
number(3) REFERENCES dept(deptno) ON DELETE CASCADE
);
with constraint name:
---------------------
create table emp
( empno
number(4),
ename
varchar2(30),
sal
number(3),
deptno
number(4) CONSTRAINT emp_fk_deptno REFERENCES dept(deptno) ON DELETE
CASCADE
);
table
level:
------------
without
constraint name:
------------------------
create table emp
( empno
number(4),
ename
varchar2(30),
sal
number(3),
deptno
number(4),
FORIEGN KEY (deptno) REFERENCES dept(deptno)
ON DELETE CASCADE
);
with constraint name:
---------------------
create table emp
( empno
number(4),
ename
varchar2(30),
sal
number(3),
deptno
number(4),
CONSTRAINT emp_fk_deptno FORIEGN KEY
(deptno) REFERENCES dept(deptno) ON DELETE CASCADE
);
alter level:
------------
without constraint name:
------------------------
alter table emp ADD FORIEGN KEY (deptno)
REFERENCES dept(deptno) ON DELETE CASCADE;
with constraint name:
---------------------
alter table emp ADD CONSTRAINT emp_fk_deptno
FORIEGN KEY (deptno)
REFERENCES dept(deptno) ON DELETE CASCADE;
good material
ReplyDelete