CONSTRAINT
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)); |
| Ex- 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)); |
| Ex- ALTER LEVEL | SQL> alter table student add check(marks>300);
SQL> alter table student add constraint ch check(marks>300); |
| UNIQUEThis is used to avoid duplicates but it allows 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)); |
| Ex- 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)); |
| Ex- ALTER LEVEL | SQL> alter table student add unique(no);
SQL> alter table student add constraint un unique(no); |
| PRIMARY KEY
1 This is used to avoid duplicates and nulls. This will work as combination of unique and not null. 2 Primary key always attached to the parent table. 3 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)); |
| Ex- 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)); |
| Ex- ALTER LEVEL | SQL> alter table student add primary key(no);
SQL> alter table student add constraint pk primary key(no); |
| FOREIGN KEY
1 This is used to reference the parent table primary key column which allows duplicates. 2 Foreign key always attached to the child table. 3 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)); |
| Ex- 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 cannot remove any parent record if the dependent childs exist. | |
Questions
 Visit Today : 52 |
 Visit Yesterday : 97 |
 This Month : 1999 |
 This Year : 7379 |