CONSTRAINTS
Constraints are categorized as follows.
Domain integrity
constraints
ü Not null
ü Check
Entity integrity
constraints
ü Unique
ü Primary key
Referential integrity
constraints
ü Foreign key
Constraints are always attached to a column not a table.
We can add constraints in three ways.
ü Column level -- along with the column definition
ü Table level -- after the table definition
ü Alter level -- using alter command
While adding constraints you need not specify the name but the
type only, oracle will internally name the constraint.
If you want to give a name to the constraint, you have to use the
constraint clause.
NOT NULL
This is used to avoid null values.
We can add this constraint in column level only.
Ex:
SQL> create table student(no
number(2) not null, name varchar(10), marks
number(3));
SQL> create table student(no
number(2) constraint nn not null, name varchar(10),
marks
number(3));
CHECK
This is used to insert the values based on specified condition.
We can add this constraint in all three levels.
Ex:
COLUMN LEVEL
SQL> create table student(no
number(2) , name varchar(10), marks number(3) check
(marks >
300));
SQL> create table student(no
number(2) , name varchar(10), marks number(3)
constraint
ch check(marks > 300));
TABLE LEVEL
SQL> create table student(no
number(2) , name varchar(10), marks number(3), check
(marks >
300));
SQL> create table student(no
number(2) , name varchar(10), marks number(3),
constraint ch
check(marks > 300));
ALTER LEVEL
SQL> alter table student add
check(marks>300);
SQL> alter table student add
constraint ch check(marks>300);
UNIQUE
This is used to avoid duplicates but it allow nulls.
We can add this constraint in all three levels.
Ex:
COLUMN LEVEL
SQL> create table student(no
number(2) unique, name varchar(10), marks
number(3));
SQL> create table student(no
number(2) constraint un unique, name
varchar(10),
marks
number(3));
TABLE LEVEL
SQL> create table student(no
number(2) , name varchar(10), marks number(3),
unique(no));
SQL> create table student(no
number(2) , name varchar(10), marks number(3),
constraint un
unique(no));
ALTER LEVEL
SQL> alter table student add unique(no);
SQL> alter table student add
constraint un unique(no);
PRIMARY KEY
Ø This is used to avoid
duplicates and nulls. This will work as combination of unique and not null.
Ø Primary key always
attached to the parent table.
Ø We can add this constraint
in all three levels.
Ex:
COLUMN LEVEL
SQL> create table student(no
number(2) primary key, name varchar(10), marks
number(3));
SQL> create table student(no number(2) constraint pk primary key, name varchar(10),
marks number(3));
TABLE LEVEL
SQL> create table student(no
number(2) , name varchar(10), marks number(3),
primary
key(no));
SQL> create table student(no
number(2) , name varchar(10), marks number(3),
constraint pk
primary key(no));
ALTER LEVEL
SQL> alter table student add
primary key(no);
SQL> alter table student add
constraint pk primary key(no);
FOREIGN KEY
Ø This is used to reference
the parent table primary key column which allows duplicates.
Ø Foreign key always
attached to the child table.
Ø We can add this constraint
in table and alter levels only.
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.
No comments:
Post a Comment