Constants: Constraints are nothing but apply rules on columns in a table like
not to accept duplicate, null values, accept conditionally satisfied data etc.,

Types of constraints

1. NOT NULL : This is used to no to accept null values in a column (null is not equal to space)
2. UNIQUE : This is used to accept only unique records and not duplicate values
3. PRIMARY KEY : This column will accept not null and unique values only,
   and helps to build relation between tables like one-to-one, one-to-many, many-to-many
4. CHECK : This column will accept values conditionallyu satisified ex: sal>10000
5. DEFAULT : This is to assign default values when no values inserted
6. FOREIGN KEY : This is to refer the primary key column in a table when you want build the relation.

A constraint can be created at column or table level

Column level

Syntax: sql> CREATE TABLE TABLE_NAME
(Col1 data type constraint_name,
col2 data type
—
—
);

Example: Create employee table using constraints

SQL> create table employees_78
2 (eno number primary key,
3 ename varchar2(10) not null,
4 sal number check(sal>1000),
5 job varchar2(10),
6 doj date default sysdate
7 );

SQL> select * from employees_78;

no rows selected

SQL> insert into employees_78 values (&eno,’&ename’,&sal,’&job’,’&doj’);
Enter value for eno: 100
Enter value for ename: hemanth
Enter value for sal: 15000
Enter value for job: analyst
Enter value for doj: 10-mar-14
old 1: insert into employees_78 values (&eno,’&ename’,&sal,’&job’,’&doj’)
new 1: insert into employees_78 values (100,’hemanth’,15000,’analyst’,’10-mar-14′)

1 row created.

SQL> /
Enter value for eno: 100
Enter value for ename: krishna
Enter value for sal: 14000
Enter value for job: clerk
Enter value for doj: 10-oct-14
old 1: insert into employees_78 values (&eno,’&ename’,&sal,’&job’,’&doj’)
new 1: insert into employees_78 values (100,’krishna’,14000,’clerk’,’10-oct-14′)
insert into employees_78 values (100,’krishna’,14000,’clerk’,’10-oct-14′)
*
ERROR at line 1:
ORA-00001: unique constraint (APPS.SYS_C001107608) violated

SQL> /
Enter value for eno: 101
Enter value for ename:
Enter value for sal: 14000
Enter value for job: CLERK
Enter value for doj: 10-OCT-13
old 1: insert into employees_78 values (&eno,’&ename’,&sal,’&job’,’&doj’)
new 1: insert into employees_78 values (101,”,14000,’CLERK’,’10-OCT-13′)
insert into employees_78 values (101,”,14000,’CLERK’,’10-OCT-13′)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into (“APPS”.”EMPLOYEES_78″.”ENAME”)

SQL> /
Enter value for eno: 102
Enter value for ename: JOSEF
Enter value for sal: 900
Enter value for job: MANAGER
Enter value for doj: 10-OCT-12
old 1: insert into employees_78 values (&eno,’&ename’,&sal,’&job’,’&doj’)
new 1: insert into employees_78 values (102,’JOSEF’,900,’MANAGER’,’10-OCT-12′)
insert into employees_78 values (102,’JOSEF’,900,’MANAGER’,’10-OCT-12′)
*
ERROR at line 1:
ORA-02290: check constraint (APPS.SYS_C001107607) violated

SQL> /
Enter value for eno: 104
Enter value for ename: XYZ
Enter value for sal: 15000
Enter value for job: CLERK
Enter value for doj:
old 1: insert into employees_78 values (&eno,’&ename’,&sal,’&job’,’&doj’)
new 1: insert into employees_78 values (104,’XYZ’,15000,’CLERK’,”)

1 row created.

SQL> SELECT * FROM EMPLOYEES_78
2 ;

   ENO ENAME             SAL JOB        DOJ

---

   100 hemanth         15000 analyst    10-MAR-14
   104 XYZ             15000 CLERK

Using Default Constraint

SQL> insert into employees_78(eno,ename,sal,job) values (&eno,’&ename’,&sal,’&job’);

Enter value for eno: 900
Enter value for ename: venu
Enter value for sal: 15000
Enter value for job: manager
old 1: insert into employees_78(eno,ename,sal,job) values (&eno,’&ename’,&sal,’&job’)
new 1: insert into employees_78(eno,ename,sal,job) values (900,’venu’,15000,’manager’)

1 row created.

SQL> select * from employees_78
2 ;

   ENO ENAME             SAL JOB        DOJ

---

   100 hemanth         15000 analyst    10-MAR-14
   104 XYZ             15000 CLERK
   900 venu            15000 manager    08-MAY-15

Share this post: on Twitter on Facebook on Google+