SQL Constraints - not null, check, default and unique
Create a table with not null constraints
SQL> create table student(
2 name varchar2(10) not null,
3 marks number not null,
4 phone number not null);
Viewing Constraint Names for a table
SQL> select constraint_name from user_constraints where table_name='student';
Disabling a constraint using Constraint Name
SQL> alter table student modify constraint sys_c0024678 disable;
Output
Table altered.
Enabling a Constraint using Constraint Name
SQL> alter table student modify constraint sys_c0024678 enable;
Output
Table altered.
Dropping a Constraint using Constraint Name
SQL> alter table student drop constraint sys_c0024678;
Output
Table altered.
Creating table with constraint names
SQL> create table student(
2 name varchar2(10) constraint student_name_nn not null,
3 marks number constraint student_marks_nn not null,
4 phone number constraint student_phone_nn not null);
Output
Table created.
Viewing Constraint names for the table created
SQL> select constraint_name from user_constraints where table_name='STUDENT';
Output
CONSTRAINT_NAME
------------------------------
STUDENT_NAME_NN
STUDENT_MARKS_NN
STUDENT_PHONE_NN
Applying Check Constraint on Marks Column
SQL> create table student(
2 name varchar2(10) constraint student_name_nn not null,
3 marks number constraint student_marks_ck check (marks>=0 and marks<=100),
4 phone number constraint student_phone_nn not null);
Applying check constraint for location
SQL> create table test(
2 name varchar2(10),
3 location varchar2(10) constraint test_loc_ck check(location='vellore' or location='chennai'));
Table created.
SQL> insert into test values('satish','vellore');
1 row created.
SQL> insert into test values('satish','chennai');
1 row created.
Check constraint violated when an address other than chennai or vellore is inserted for the column
SQL> insert into test values('satish','mumbai');
insert into test values('satish','mumbai')
*
Output
ERROR at line 1:
ORA-02290: check constraint (SATISH.TEST_LOC_CK) violated
Using in operator for check constraint
SQL> create table test(
2 name varchar2(10),
3 location varchar2(10) constraint test_location_ck check(location in('vellore','chennai')));
Table created.
SQL> insert into test values('satish','chennai');
1 row created.
Check constraint violated when inserting a different address
SQL> insert into test values('satish','mumbai');
insert into test values('satish','mumbai')
*
Output
ERROR at line 1:
ORA-02290: check constraint (SATISH.TEST_LOCATION_CK)
violated
Table level check constraint on multiple mark columns
SQL> create table student(
2 name varchar2(10),
3 mark1 number,
4 mark2 number,
5 constraint student_ck check(mark1>=0 and mark1<=100 and mark2>=0 and mark2<=100));
Output
Table created.
Demo of Check Constraint Violation
SQL> insert into student values ('satish',24,-32);
insert into student values ('satish',24,-32)
*
ERROR at line 1:
ORA-02290: check constraint (SATISH.STUDENT_CK) violated
SQL> insert into student values ('satish',-24,32);
insert into student values ('satish',-24,32)
*
ERROR at line 1:
ORA-02290: check constraint (SATISH.STUDENT_CK) violated
Valid record that passes the check constraint
SQL> insert into student values ('satish',24,32);
Output
1 row created.
Applying unique key constraint
SQL> create table student
2 (name varchar2(10),
3 phone number constraint student_phone_uk unique);
Table created.
SQL> insert into student values('sat',111);
1 row created.
Unique violation on duplicate entry
SQL> insert into student values('ram',111);
insert into student values('ram',111)
*
Output
ERROR at line 1:
ORA-00001: unique constraint (SATISH.STUDENT_PHONE_UK) violated
Applying Unique key at the table level
SQL> create table student(
2 name varchar2(10),
3 phone1 number,
4 phone2 number,
5 constraint student_uk unique(phone1,phone2));
Table created.
SQL> insert into student values('ram',111,222);
1 row created.
SQL> insert into student values('sam',111,322);
1 row created.
SQL> insert into student values('sam',211,322);
1 row created.
SQL> select * from student;
Output
NAME PHONE1 PHONE2
---------- ---------- ----------
ram 111 222
sam 111 322
sam 211 322
Unique key violation on duplicate entry
SQL> insert into student values('ram',111,222);
insert into student values('ram',111,222)
*
Output
ERROR at line 1:
ORA-00001: unique constraint (SATISH.STUDENT_UK) violated
Default Constraint
Create a table with default constraint
SQL> create table test(
2 id number,
3 marks number default 100);
Table created.
SQL> insert into test(id) values (123);
1 row created.
SQL> select * from test;
ID MARKS
---------- ----------
123 100
SQL> insert into test values(1,54);
1 row created.
SQL> select * from test;
ID MARKS
---------- ----------
123 100
1 54
SQL> drop table test;
Table dropped.
Creating a table with default and check constraint on the same column
SQL> create table test(
2 id number,
3 marks number default 100 constraint test_marks_ck check(marks>0 and marks<=100));
Table created.
SQL> insert into test(id) values (1);
1 row created.
SQL> select * from test;
Output
ID MARKS
---------- ----------
1 100
Check Constraint violation
SQL> insert into test values (1,-54);
insert into test values (1,-54)
*
ERROR at line 1:
ORA-02290: check constraint (SATISH.TEST_MARKS_CK) violated
SQL> insert into test values (1,540);
insert into test values (1,540)
*
ERROR at line 1:
ORA-02290: check constraint (SATISH.TEST_MARKS_CK) violated
SQL> insert into test values (1,54);
1 row created.
SQL> select * from test;
Output
ID MARKS
---------- ----------
1 100
1 54