PRIMARY KEY.
SQL> create table student (rollno number(4) primary key,name varchar2(15) not null);
Table created.
SQL> insert into student values('&rollno','&name');
Enter value for rollno: 1
Enter value for name: reni
old 1: insert into student values('&rollno','&name')
new 1: insert into student values('1','reni')
1 row created.
NULL VALUE CONSTRAINT.
SQL> insert into student values('&rollno','&name');
Enter value for rollno: 2
Enter value for name:
old 1: insert into student values('&rollno','&name')
new 1: insert into student values('2','')
insert into student values('2','')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."STUDENT"."NAME")
DEFAULT CONSTRAIT.
SQL> create table employ (empno number(3),empname varchar2(15),married char(1)
default 'M');
Table created.
SQL> insert into employ(empno,empname)values(1,'reni');
1 row created.
SQL> select * from employ;
EMPNO EMPNAME M
---------- --------------- -
1 reni M
CHECK INTEGRITY.
SQL> create table emp (empname varchar2(15),empno number(3),salary number(5)check(salary>5000));
Table created.
SQL> insert into emp values('&empname','&empno','&salary');
Enter value for empname: siva
Enter value for empno: 3
Enter value for salary: 4000
old 1: insert into emp values('&empname','&empno','&salary')
new 1: insert into emp values('siva','3','4000')
insert into emp values('siva','3','4000')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C001296) violated
SQL> /
Enter value for empname: sona
Enter value for empno: 4
Enter value for salary: 7000
old 1: insert into emp values('&empname','&empno','&salary')
new 1: insert into emp values('sona','4','7000')
1 row created.
UNIQUE CONSTRAINT.
SQL> create table stud (name char(14),regno number constraint jj unique);
Table created.
SQL> insert into stud values('&name','®no');
Enter value for name: pavithra
Enter value for regno: 46
old 1: insert into stud values('&name','®no')
new 1: insert into stud values('pavithra','46')
1 row created.
SQL> /
Enter value for name: sindhu
Enter value for regno: 46
old 1: insert into stud values('&name','®no')
new 1: insert into stud values('sindhu','46')
insert into stud values('sindhu','46')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.JJ) violated
FOREIGN KEY.
SQL> create table nan (name char(14),age number(5),des char(9) primary key);
Table created.
SQL> create table raj (name char(12),des char references nan(des));
Table created.
SQL> insert into nan values ('&name','&age','&des');
Enter value for name: reni
Enter value for age: 23
Enter value for des: md
old 1: insert into nan values ('&name','&age','&des')
new 1: insert into nan values ('reni','23','md')
1 row created.
SQL> /
Enter value for name: manoj
Enter value for age: 24
Enter value for des: gm
old 1: insert into nan values ('&name','&age','&des')
new 1: insert into nan values ('manoj','24','gm')
1 row created.
SQL> /
Enter value for name: raj
Enter value for age: 21
Enter value for des:
old 1: insert into nan values ('&name','&age','&des')
new 1: insert into nan values ('raj','21','')
insert into nan values ('raj','21','')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."NAN"."DES")
SQL> /
Enter value for name: ram
Enter value for age: 21
Enter value for des: md
old 1: insert into nan values ('&name','&age','&des')
new 1: insert into nan values ('ram','21','md')
insert into nan values ('ram','21','md')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C001300) violated
SQL> insert into raj values('&name','&des');
Enter value for name: rani
Enter value for des: md
1 row created.
SQL> /
Enter value for name: dev
Enter value for des: gm
1 row created.
SQL> select * from nan;
NAME AGE DES
-------------- ---------- ---------
reni 23 md
manoj 24 gm
SQL> select * from raj;
NAME DES
-------------- ---------- ---------
rani md
dev gm
SQL> delete from nan where age=23;
1 row deleted.
SQL> delete from raj where des='md';
1 rows deleted.
SQL> select * from raj;
NAME DES
-------------- ---------- ---------
dev gm
SQL> select * from nan;
NAME AGE DES
-------------- ---------- ---------
manoj 24 gm
