Oracle: constraint

1) DATABASE CONSTRAINTS

=======================

Constraints : are the rules which can be implemented on the data.

Uses of Constraints: useful to maintain consistent and reliable data. so that

user should not modify incorrect data, if he tries to modify incorrect data

RDBMS will not allow the user to do it. before making any changes in data first

RDBMS will check the constraint i.e., it will check the rule, if it satisfies

then it will allow otherwise it will not allow.

There are five types of constraints

1. NOT NULL

2. CHECK

3. UNIQUE

4. PRIMARY KEY = NOT NULL + UNIQUE

5. FOREIGN KEY

Constraints can be defined at two levels

1. column level

2. table level

NOT NULL constraint can be defined only at the column level not at the table

level.

1) NOT NULL - do not accept nulls

DROP TABLE employee;

create table employee(empno number(4), ename varchar2(20) NOT NULL);

NOTE:tables should have minimum one column, without column, table cannot exist.

select * from employee;

INSERT INTO employee(empno,ename) VALUES(100, 'Dexter');

SELECT * FROM EMPLOYEE;

INSERT INTO employee(empno) VALUES(101);

INSERT INTO employee(empno, ename) VALUES(101, NULL);

SELECT * FROM EMPLOYEE;

INSERT INTO employee(ename) VALUES('Disoj');

select * from employee;

CREATE TABLE NN_TABLE(EMPNO NUMBER(4), ENAME VARCHAR2(20), NOT NULL(ENAME)); -- not valid

2) UNIQUE - no duplicates are accepted (we can have null(s) in unique column)

WHEN EVER WE DEFINE UNIQUE CONSTRAINT ON ANY COLUMN THEN ON THAT COLUMN UNIQUE

INDEX GETS CREATED AUTOMATICALLY.

UNIQUE CONSTRAINT WE CAN DEFINE BOTH AT COLUMN LEVEL AND TABLE LEVEL.

DROP TABLE PRODUCT;

create table product(pid number(4), pname varchar2(15) UNIQUE);

INSERT INTO product(pid, pname) VALUES(1001, 'Dexter iphone');

SELECT * FROM PRODUCT;

INSERT INTO product(pid, pname) VALUES(1002, 'Dexter iphone');

INSERT INTO product(pid, pname) VALUES(1002, 'dexter iphone');

INSERT INTO product(pid, pname) VALUES(1001, 'SAMSUNG');

SELECT * FROM PRODUCT;

INSERT INTO product(pid, pname) VALUES(1002, NULL);

INSERT INTO product(pid, pname) VALUES(1003, NULL);

select * from product;

-- example for defining constraint at the table level

DROP TABLE PRODUCT1;

create table product1(pid number(4), pname varchar2(15), price NUMBER(8,2),

UNIQUE(pid, pname));

Note: with table level constraint we can define contraint on multiple columns.

INSERT INTO product1 VALUES(1001, 'abc', 2000);

SELECT * FROM PRODUCT1;

INSERT INTO product1 VALUES(1002, 'abc', 2000);

SELECT * FROM PRODUCT1;

INSERT INTO product1 VALUES(1002, 'abc', 3000); -- invalid

SELECT * FROM PRODUCT1;

INSERT INTO product1 VALUES(1001, NULL, 2000);

SELECT * FROM PRODUCT1;

INSERT INTO product1 VALUES(1001, NULL, 2500);

INSERT INTO PRODUCT1 VALUES(NULL, NULL, 4000);

SELECT * FROM PRODUCT1;

INSERT INTO PRODUCT1 VALUES(NULL, NULL, 6000);

SELECT * FROM PRODUCT1;

INSERT INTO product1 VALUES(NULL, 'abc', 2000);

SELECT * FROM product1;

-- dt.20-feb-2014 --

drop table emp2014;

CREATE TABLE emp2014

(

empno NUMBER UNIQUE NOT NULL,

ename VARCHAR2(20) NOT NULL,

sal NUMBER(7,2) NOT NULL

);

INSERT INTO emp2014 VALUES(100, 'abc', 50000);

INSERT INTO emp2014 VALUES(100, 'xyz', 40000);

SELECT * FROM EMP2014;

INSERT INTO emp2014 VALUES(NULL, 'abc', 50000); -- invalid

INSERT INTO emp2014 VALUES(101, 'abc', 50000);

SELECT * FROM emp2014;

SELECT * FROM user_constraints;

SELECT * FROM user_cons_columns;

3) PRIMARY KEY = Unique + not null ( no null value, no duplicate value),

only 1 primary key per table

DROP TABLE emp_pk;

CREATE TABLE emp_pk

(empno NUMBER(4) PRIMARY KEY, ename VARCHAR2(20)

);

INSERT INTO emp_pk VALUES(100, 'xyz');

SELECT * FROM EMP_PK;

INSERT INTO emp_pk(ename) VALUES('RAJ'); -- will give error

INSERT INTO emp_pk VALUES(NULL, 'akp'); -- will give error

SELECT * FROM EMP_PK;

INSERT INTO emp_pk VALUES(100, 'John');

SELECT * FROM EMP_PK;

INSERT INTO emp_pk VALUES(101, 'xyz');

SELECT * FROM EMP_PK;

drop table pk_demo;

CREATE TABLE PK_DEMO(EMPNO NUMBER(4), ENAME VARCHAR2(20), SAL NUMBER(8,2),

PRIMARY KEY(EMPNO, ENAME));

INSERT INTO PK_DEMO VALUES(100, 'SKP', 25000);

SELECT * FROM PK_DEMO;

INSERT INTO PK_DEMO VALUES(100, 'AKP', 50000);

INSERT INTO PK_DEMO VALUES(100,'SKP', 40000); -- gives error

INSERT INTO PK_DEMO VALUES(NULL, 'ISHU', 15000);

INSERT INTO PK_DEMO VALUES(102, NULL, 15000);

4) FOREIGN KEY - is a type of contraints when defined on any column it will

create logical relationship with other column, references primary key of same table

or another table, it can have those values which are there in the primary key column

or it can have nulls, it can have duplicate data.

DROP TABLE DEPT_REL9;

CREATE TABLE dept_rel9(deptid NUMBER(4) PRIMARY KEY, dname VARCHAR2(20));

drop table emp_rel9;

CREATE TABLE emp_rel9(empid NUMBER(4), ename VARCHAR2(20),

deptid NUMBER(4) REFERENCES dept_rel9(deptid));

INSERT INTO dept_rel9 VALUES(10, 'Marketing');

INSERT INTO dept_rel9 VALUES(20, 'Accounts');

SELECT * FROM DEPT_REL9;

desc emp_rel9;

INSERT INTO emp_rel9 VALUES(100, 'Abel', 30); -- gives error

INSERT INTO emp_rel9 VALUES(100, 'Abel', 20);

INSERT INTO EMP_REL9 VALUES(101, 'JOHN', NULL);

INSERT INTO EMP_REL9 VALUES(102, 'ISHU', 20);

SELECT * FROM EMP_REL9;

INSERT INTO emp_rel9 VALUES(102, 'Zlotkey', 50); -- GIVES ERROR

SELECT * FROM emp_rel9;

SELECT * FROM dept_rel9;

DELETE FROM dept_rel9 WHERE deptid=20;

DELETE FROM dept_rel9 WHERE deptid=10;

drop table emp_rel14;

CREATE TABLE emp_rel14(empid NUMBER(4), ename VARCHAR2(20), deptid NUMBER(4),

FOREIGN KEY(deptid) REFERENCES dept_rel(deptid));

select * from emp_rel14;

Constraint name will be given in "SYS_Cn" (n is number) format by default.

SELECT * FROM user_cons_columns;

select * from user_constraints;

DROP TABLE EMP_CONS_NAME;

CREATE TABLE emp_cons_name11

(

empno NUMBER(4) CONSTRAINT emp_cons_pk11 PRIMARY KEY,

ename VARCHAR2(20) CONSTRAINT nn111 NOT NULL,

mgr NUMBER(4) REFERENCES emp_cons_name11(empno),

sal NUMBER(7,2) CONSTRAINT nn211 NOT NULL,

deptno NUMBER(2) REFERENCES dept(deptno)

);

SELECT * FROM user_cons_columns WHERE TABLE_NAME='EMP_CONS_NAME11';

SELECT * FROM user_constraints;

we cannot insert any value in the foreign key column which is not there

parent table.

you cannot delete data from parent table when there is a dependent record

in the child table.

drop table parent_table2;

CREATE TABLE parent_table2(sno NUMBER(4) PRIMARY KEY, sname VARCHAR2(20));

INSERT INTO parent_Table1 VALUES(100, 'san');

INSERT INTO parent_Table1 VALUES(101, 'akp');

drop table child_table11;

select * from parent_table1;

CREATE TABLE child_table11(sno NUMBER(4) REFERENCES parent_table1(sno),

marks NUMBER(4));

INSERT INTO child_table11 VALUES(100, 95);

INSERT INTO child_table11 VALUES(101, 98);

select * from child_table11;

INSERT INTO child_table11 VALUES(102, 98); -- cannot insert

INSERT INTO child_table11 VALUES(null, 98);

INSERT INTO child_table11 VALUES(101, 99);

select * from child_table11;

select * from parent_table1;

delete from parent_table1 where sno=100;

delete from parent_table1 where sno=101;

CREATE TABLE PARENT_TABLE11(SNO NUMBER(4) PRIMARY KEY, SNAME VARCHAR2(20));

INSERT INTO PARENT_TABLE11 VALUES(100, 'JOHN');

INSERT INTO PARENT_TABLE11 VALUES(101, 'ABEL');

drop table child_table11;

CREATE TABLE child_table11(sno NUMBER(4) REFERENCES parent_table11(sno)

ON DELETE CASCADE, marks NUMBER(4));

INSERT INTO child_table11 VALUES(100, 95);

INSERT INTO child_table11 VALUES(101, 98);

INSERT INTO child_table11 VALUES(101, 99);

SELECT * FROM parent_table11;

SELECT * FROM child_table11;

DELETE FROM parent_table11 WHERE sno=101;

SELECT * FROM parent_table11;

SELECT * FROM child_Table11;

DROP TABLE PARENT_TABLE31;

CREATE TABLE parent_table311(sno NUMBER(4) PRIMARY KEY, sname VARCHAR2(20));

INSERT INTO parent_Table311 VALUES(100, 'san');

INSERT INTO parent_Table311 VALUES(101, 'akp');

select * from parent_table311;

CREATE TABLE child_table311(sno NUMBER(4) REFERENCES parent_Table311(sno)

ON DELETE SET NULL, marks NUMBER(4));

INSERT INTO child_table311 VALUES(100, 95);

INSERT INTO child_table311 VALUES(101, 98);

SELECT * FROM parent_table311;

SELECT * FROM child_table311;

DELETE FROM parent_table311 WHERE sno=100;

SELECT * FROM parent_table311;

SELECT * FROM child_Table311;

parent record, child record

1) do not allow deleting parent record when child record found

2) ON DELETE CASCADE - when deleting parent record - delete all corresponding child records

3) ON DELETE SET NULL - when deleting parent record - set child record value as null

i) deptno number(2) references dept(deptno)

ii) deptno number(2) references dept(deptno) on delete cascade

iii) deptno number(2) references dept(deptno) on delete set null

5) DEFAULT - can set default value

drop table sales;

create table sales

(sid number(4) primary key,

pname varchar2(20) unique,

qty number(3) DEFAULT 1,

price number(5,2) not null,

saleamt number(7,2)

);

desc sales;

INSERT INTO sales(sid, pname, price) VALUES(1001, 'samsung123', 300);

SELECT * FROM sales;

INSERT INTO sales(sid, pname,price, qty) VALUES(1002, 'samsung4', 400, 5);

select * From sales;

6) CHECK - restricts values from a list

drop table student21;

create table student21(stuid NUMBER(4), sname VARCHAR2(20),

age NUMBER(4) CHECK(age >=15) CHECK(age <=30));

INSERT INTO student21 VALUES(100, 'ishu', 35); -- NOT ALLOWED

INSERT INTO student21 VALUES(100, 'ishu', 29);

INSERT INTO student21 VALUES(102, 'aksh', 30);

INSERT INTO student21 VALUES(103, 'prakash', 14);

SELECT * FROM student21;

Oracle

Tuesday, June 10, 2014

constraint

No comments:

Post a Comment