Sunday, July 11, 2010

CONSTRAINTS :----->>

CONSTRAINTS:

CONSTRAINTS MEAN restrictions. In ORACLE constraints prevent Invalid

data entry into tables.

following are the different types of CONSTRAINTS:-

Ø NOT NULL.

Ø UNIQUE.

Ø PRIMARY KEY.

Ø FOREIGN KEY.

Ø CHECK.

NOT NULL:

It does not allow null values into columns.

Constraint at column level:

Create table emp

( empno number (9) not null,

ename varchar(9)) ;

SQL> CREATE TABLE EMPLOYEE

2 (EMPNO NUMBER(10),

3 ENAME VARCHAR2(10));

Table created.

SQL> INSERT INTO EMPLOYEE

2 VALUES

3 (1,'PAVAN');

1 row created.

1 INSERT INTO EMPLOYEE

2 VALUES

3* (NULL,'KUMAR')

SQL> /

1 row created.

1 INSERT INTO EMPLOYEE

2 VALUES

3* (3,NULL)

SQL> /

1 row created.

SQL> SELECT * FROM EMPLOYEE;

EMPNO ENAME

---------- ----------

1 PAVAN

KUMAR

3

IN THE ABOVE OUTPUT ONCE EMPNO IS NULL FOR KUMAR AND ONCE ENAME IS NULL FOR EMPNO 3.

THIS KIND FOR DATA WRONG DATA WE HAVE TO STOP WHILE INSERTING DATA IN TO THE TABLE.

FOR THIS WE USE NOT NULL CONSTRAINT ON COLUMNS WHERE U WANT STOP NULL VALUES.

DROP TABLE EMPLOYEE;

Table dropped.

SQL> CREATE TABLE EMPLOYEE

2 (EMPNO NUMBER(10) NOT NULL,

3 ENAME VARCHAR2(10) NOT NULL);

Table created.

SQL> INSERT INTO EMPLOYEE

2 VALUES

3 (NULL,'KUMAR');

INSERT INTO EMPLOYEE

*

ERROR at line 1:

ORA-01400: cannot insert NULL into ("SCOTT"."EMPLOYEE"."EMPNO")

SQL> INSERT INTO EMPLOYEE

2 VALUES

3 (3,NULL);

INSERT INTO EMPLOYEE

*

ERROR at line 1:

ORA-01400: cannot insert NULL into ("SCOTT"."EMPLOYEE"."ENAME")

ABOVE WE KEPT NOT NULL FOR BOTH EMPNO,ENAME AND TRIED TO INSERT NULL VALUES IN TO BOTH COLUMNS BUT IT HAS NOT ACCEPTED.

IF YOU WANT TO MAKE ANY COLUMN TO ENTER VALUE FOR THAT COLUMN COMPLUSORY THEN WE HAVE TO KEEP NOT NULL FOR THAT COLUMN.

WE CANNOT KEEP NOT NULL AT THE TIME OF TABLE CREATION AT TABLE LEVEL.

WE CAN ADD CONSTRAINT NOT NULL AFTER CREATION OF TABLE.

UNIQUE:

It does not allow duplicate values in columns.

Constraint at column level:

Create table emp

( empno number (9) UNIQUE ,

ename varchar(9)) ;

Constraint at table level:

Create table emp

empno number (9) ,

ename varchar2(9),

constraint emp_uni UNIQUE ( empno) ;

SQL> DROP TABLE EMPLOYEE;

Table dropped.

SQL> CREATE TABLE EMPLOYEE

2 (EMPNO NUMBER(10),

3 ENAME VARCHAR2(10));

Table created.

SQL> INSERT INTO EMPLOYEE

2 VALUES

3 (1,'PAVAN');

1 row created.

1 INSERT INTO EMPLOYEE

2 VALUES

3* (1,'PAVAN')

SQL> /

1 row created.

1 INSERT INTO EMPLOYEE

2 VALUES

3* (2,'KUMAR')

SQL> /

1 row created.

1 INSERT INTO EMPLOYEE

2 VALUES

3* (3,'KUMAR')

SQL> /

1 row created.

1 INSERT INTO EMPLOYEE

2 VALUES

3* (1,'PAVAN')

SQL> /

1 row created.

1 INSERT INTO EMPLOYEE

2 VALUES

3* (1,'PAVAN')

SQL> /

1 row created.

SQL> SELECT * FROM EMPLOYEE;

EMPNO ENAME

---------- ----------

1 PAVAN

1 PAVAN

2 KUMAR

3 KUMAR

1 PAVAN

1 PAVAN

6 rows selected.

HERE IF YOU WANT SELECT ONLY PAVAN'S RECORD WE HAVE TO QUERY LIKE THIS

SQL> SELECT * FROM EMPLOYEE WHERE ENAME='PAVAN';

EMPNO ENAME

---------- ----------

1 PAVAN

1 PAVAN

1 PAVAN

1 PAVAN

BUT WE WANT PARTICULAR PAVAN'S RECORD. HERE IT IS NOT POSSIBLE. THIS IS WRONG DATA

ENTERED IN TO THE TABLE.

IN REAL TIME WE HAVE SAME NAME EMPLOYEES IN AN ORAGANIZATION, THEN WHAT WE SHOULD DO IS WE GIVE EACH ONE ONE UNIQUE NUMBER THAT IS EMPLOYEE ID.

HERE ALSO WE MAKE EMPNO HAS UNIQUE, THAT IS EMPNO COLUMN SHOLD NOT ALLOW US TO ENTER DUPLICATE VALUES.

FOR DOING THAT WE USE UNIQUE CONSTRAINT.

SQL> DROP TABLE EMPLOYEE;

Table dropped.

SQL> CREATE TABLE EMPLOYEE

2 (EMPNO NUMBER(10) UNIQUE,

3 ENAME VARCHAR2(10));

Table created.

SQL> INSERT INTO EMPLOYEE

2 VALUES

3 (1,'PAVAN');

1 row created.

HERE 1 IS ASSIGNED TO PAVAN, IF FOR ANY EMPLOYEE IF U WANT TO GIVE EMPNO AS 1 IT WON'T ACCEPT.

SQL> INSERT INTO EMPLOYEE

2 VALUES

3 (1,'PAVAN');

INSERT INTO EMPLOYEE

*

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.SYS_C002751) violated

HERE IF YOU TRIES TO ASSIGN SAME NUMBER FOR PAVAN THAT IS WRONG BECAUSE WE KEPT UNIQUE CONSTRAINT ON EMPNO.

IT ACCEPTS IF YOU GIVE 2 FOR NEXT PAVAN.

1 INSERT INTO EMPLOYEE

2 VALUES

3* (2,'PAVAN')

SQL> /

1 row created.

HERE UNIQUE CONSTRAINT HAS PROBLEM. THAT IS IT ALLOWS NULL VALUES.

SQL> INSERT INTO EMPLOYEE

2 VALUES

3 (NULL,'PAVAN');

1 row created.

SQL> SELECT * FROM EMPLOYEE;

EMPNO ENAME

---------- ----------

1 PAVAN

2 PAVAN

PAVAN

SQL> THIS IS WRONG WITH UNIQUE.

TO DO ABOVE PROBLEM CORRECTLY WE GO FOR ANOTHER CONSTRAINT CALLED PRIMARY KEY.

WHICH ACTS AS BOTH NOT NULL AND UNIQUE.

PRIMARY KEY:

It does not allow either null values or duplicate values In to the columns ( it simultaneously functions both as “ not null “ and “ unique “ )

Constraint at column level:

create table emp

( empno number (9) primary key,

ename varchar2(9)) ;

constraint at table level:

create table emp

(empno number(9),

ename varchar2(9),

deptno number(9),

constraint emp_pk primary key (empno)) ;

SQL> DROP TABLE EMPLOYEE;

Table dropped.

SQL> CREATE TABLE EMPLOYEE

2 (EMPNO NUMBER(10) PRIMARY KEY,

3 ENAME VARCHAR2(10));

Table created.

SQL> INSERT INTO EMPLOYEE

2 VALUES

3 (1,'PAVAN');

1 row created.

1 INSERT INTO EMPLOYEE

2 VALUES

3* (1,'PAVAN')

SQL> /

INSERT INTO EMPLOYEE

*

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.SYS_C002752) violated

ABOVE IT ACTED AS UNIQUE CONSTRAINT THAT MEANS IT STOPPED DUPLICATE VALUES.

SQL> INSERT INTO EMPLOYEE

2 VALUES

3 (NULL,'PAVAN');

INSERT INTO EMPLOYEE

*

ERROR at line 1:

ORA-01400: cannot insert NULL into ("SCOTT"."EMPLOYEE"."EMPNO")

OUR PROBLEM SLOVED.

HERE WE CAN GIVE NAME FOR CONSTRAINT,IF YOU DON'T GIVE NAME FOR CONSTRAINT SYSTEM WILL AUTOMATICALLY ASSIGNS A UNIQUE NUMBER CALLED SYS_UNIQUE NUMBER.

INSERT INTO EMPLOYEE

2 VALUES

3 (1,'PAVAN');

INSERT INTO EMPLOYEE

*

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.SYS_C002752) violated

SQL> DROP TABLE EMPLOYEE;

Table dropped.

SQL> CREATE TABLE EMPLOYEE

2 (EMPNO NUMBER(10) CONSTRAINT EMPLOYEE_PK PRIMARY KEY,

3 ENAME VARCHAR2(10));

Table created.

SQL> INSERT INTO EMPLOYEE

2 VALUES

3 (1,'PAVAN');

1 row created.

SQL> INSERT INTO EMPLOYEE

2 VALUES

3 (1,'PAVAN');

INSERT INTO EMPLOYEE

*

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.EMPLOYEE_PK) violated

we can also give primary key for 2 columns:

create table emp

(empno number(9),

ename varchar2(9),

deptno number(9),

constraint emp_pk1 primary key (empno,ename));

CHECK CONSTRAINT:

It is also called business rule constraint.

constraint at coloumn level:

create table bank

(Acc_no number(9),

name varchar2(9),

balance number(9) check(balance >=1000),

address varchar2(9));

constraint at table level:

create table bank

(Acc_no number(9),

name varchar2(9),

balance number(9),

address varchar2(9),

constraint bank_chek check(balance >=1000));

SQL> CREATE TABLE BANK

2 (ACC_NO NUMBER(10),

3 BALANCE NUMBER(10));

Table created.

SQL> INSERT INTO BANK

2 VALUES

3 (1,1000);

1 row created.

1 INSERT INTO BANK

2 VALUES

3* (2,2000)

SQL> /

1 row created.

1 INSERT INTO BANK

2 VALUES

3* (3,500)

SQL> /

1 row created.

HERE WE WANT STOP SOME ONE ENTERING BELOW 1000 IN BALANCE COLUMN, FOR THAT WE KEEP CHECK CONSTRAINT ON COLUMN BALANCE.

SQL> DROP TABLE BANK;

Table dropped.

SQL> CREATE TABLE BANK

2 (ACC_NO NUMBER(10),

3 BALANCE NUMBER(10) CHECK(BALANCE > 1000));

Table created.

SQL> INSERT INTO BANK

2 VALUES

3 (1,1000);

INSERT INTO BANK

*

ERROR at line 1:

ORA-02290: check constraint (SCOTT.SYS_C002758) violated

SQL> INSERT INTO BANK

2 VALUES

3 (2,2000);

1 row created.

SQL> INSERT INTO BANK

2 VALUES

3 (3,500);

INSERT INTO BANK

*

ERROR at line 1:

ORA-02290: check constraint (SCOTT.SYS_C002758) violated

HERE CHECK CONSTRAINT STOPED US FROM ENTERING BELOW 1000.

SQL> DROP TABLE BANK;

Table dropped.

SQL> CREATE TABLE BANK

2 (ACC_NO NUMBER(10),

3 BALANCE NUMBER(10),

4 CONSTRAINT BANK_CK CHECK(BALANCE > 1000));

Table created.

SQL> DROP TABLE BANK;

Table dropped.

SQL> CREATE TABLE BANK

2 (ACC_NO NUMBER(10),

3 BALANCE NUMBER(10));

Table created.

SQL> ALTER TABLE BANK

2 ADD CONSTRAINT BANK_CK CHECK(BALANCE > 1000);

Table altered.

Foreign Key:

Foreign key establishes relationships between tables. A foreign key is a coloumn whose values are derived from the primary key or unique key of some other table.

constraint at coloumn level:

create table emp

(empno number(9),

ename varchar2(9),

deptno number(9) references dept(deptno));

constraint at table level:

create table emp

(empno number(9),

ename varchar2(9),

deptno number(9),

constraint emp_fk foreign key(deptno) references dept(deptno));

Note: the deptno of dept table must have primary key or unique key.

SQL> CREATE TABLE EMP1

2 AS

3 SELECT * FROM EMP;

Table created.

SQL> CREATE TABLE DEPT1

2 AS

3 SELECT * FROM DEPT;

Table created.

SQL> SELECT * FROM EMP1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

HERE IF I ENTER IN TO EMP1 EMPLOYEES IN DEPTNO 50,60,70 LIKE BELOW

1 INSERT INTO EMP1

2 VALUES

3* (1,'PAVAN','MANAGER',7369,'01-DEC-81',1000,1000,50)

SQL> /

1 row created.

1 INSERT INTO EMP1

2 VALUES

3* (2,'KUMAR','MANAGER',7369,'01-DEC-81',1000,1000,60)

SQL> /

1 row created.

1 INSERT INTO EMP1

2 VALUES

3* (3,'GAYATRI','MANAGER',7369,'01-DEC-81',1000,1000,70)

SQL> /

1 row created.

SQL> SELECT * FROM EMP1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

1 PAVAN MANAGER 7369 01-DEC-81 1000 1000 50

2 KUMAR MANAGER 7369 01-DEC-81 1000 1000 60

3 GAYATRI MANAGER 7369 01-DEC-81 1000 1000 70

17 rows selected.

HERE IF U OBSERVE DEPTNO COLUMN IN EMP1 TABLE, IF ANY ONE ASKS WHAT IS THE NAME OF

DEPARTMENT NUMBER 20, U WILL QUERY DEPT1 TABLE LIKE THIS

SQL> SELECT * FROM DEPT;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

AND SAYS RESEARCH IS NAME, NOW U TELL ME WHAT IS THE NAME OF DEPARTMENT 50.

HERE CLEALY U CAN SAY THERE IS NO DEPARTMENT NO 50 EXISTING IN DEPT1 TABLE. THAT IS WE CANNOT FIND OUT THE DNAME FOR DEPTNO 50. THAT IS THIS IS WRONG INFO U ENTERED IN TO EMP1 TABLE. U HAVE TO STOP THIS BY USING FOREIGN KEY.

THAT IS YOU HAVE TO TELL ORACLE THAT BEFORE ACCEPTING A VALUE IN EMP1 TABLE DEPTNO COLUMNYOU PLEASE KINDLY SEARCH DEPT1 TABLE DEPTNO COLUMN IF ENTERED NUMBER IS THERE IN DEPTNO COLUMN OF DEPT TABLE U ACCEPT THAT NUMBER TO ENTER IN TO EMP1 TABLE DEPTNO COLUMN OTHERWISE RAISE ERROR.

HERE WE ASKING ORACLE TO REFER DEPT1 TABLE DEPTNO THAT IS WHY DEPT1 TABLE IS PARENT TABLE AND EMP1 IS CHILD TABLE.

FOR DOING ABOVE THING WE HAVE TO KEEP FOREIGN KEY FOR EMP1 TABLE DEPTNO AND PRIMARY KEY FOR DEPT1 TABLE DEPTNO COLUMN.

FIRST U HAVE TO KEEP PRIMARY KEY FOR DEPT1 TABLE DEPTNO AND THEN FOREIGN KEY FOR EMP TABLEDEPTNO LIKE BELOW.

SQL> ALTER TABLE DEPT1

2 ADD CONSTRAINT DEPT1_PK PRIMARY KEY(DEPTNO);

Table altered.

SQL> ALTER TABLE EMP1

2 ADD CONSTRAINT EMP1_FK FOREIGN KEY(DEPTNO) REFERENCES DEPT1(DEPTNO);

Table altered.

SQL> INSERT INTO EMP1

2 (EMPNO,DEPTNO)

3 VALUES

4 (1,50);

INSERT INTO EMP1

*

ERROR at line 1:

ORA-02291: integrity constraint (SCOTT.EMP1_FK) violated - parent key not found

1 INSERT INTO EMP1

2 (EMPNO,DEPTNO)

3 VALUES

4* (1,60)

SQL> /

INSERT INTO EMP1

*

ERROR at line 1:

ORA-02291: integrity constraint (SCOTT.EMP1_FK) violated - parent key not found

HERE IF YOU ENTER OTHER THAN DEPTNO THAT IS EXISTING IN DEPT TABLE WE GET ERROR.IT WON'T BE ENTERED IN TO EMP1 TABLE.

1 CREATE TABLE EMP1

2 (EMPNO NUMBER(10),

3 ENAME VARCHAR2(10),

4* DEPTNO NUMBER(10) REFERENCES DEPT1(DEPTNO))

SQL> /

Table created.

1 CREATE TABLE EMP11

2 (EMPNO NUMBER(10),

3 ENAME VARCHAR2(10),

4 DEPTNO NUMBER(10),

5* CONSTRAINT EMP2_FK FOREIGN KEY(DEPTNO) REFERENCES DEPT1(DEPTNO))

SQL> /

Table created.

if you don’t give names to the constraints system will automatically give names ex: sys_unique number. It is always good practice to give names to constraints.

we can give constraints after the creation of the table also.

unique:

alter table emp

add constraint emp_uk unique(empno);

primary key:

alter table emp

add constraint emp_pk primary key(empno);

foreign key:

alter table emp

add constraint emp_fk FOREIGN key(deptno) references dept(deptno);

check:

alter table bank

add constraint BANK_ch check(balance >=1000) ;

VIEWING CONSTRAINTS:

YOU CAN SEE THE CONSTRAINTS THAT ARE CREATED ON TABLES IN THE ORACLE TABLE CALLED

USER_CONSTRAINTS

SELECT TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USER_CONSTRAINTS

WHERE TABLE_NAME=’EMP’;

IF YOU WANT TO SEE COLUMNS ASSOCIATED WITH CONSTRAINTS THEN YOU HAVE TO SEE IN

USER_CONS_COLUMNS

SELECT TABLE_NAME,CONSTRAINT_NAME,COLUMN_NAME FROM USER_CONS_COLUMNS

WHERE TABLE_NAME=’EMP’;

SQL> CREATE TABLE EMPLOYEE

2 (EMPNO NUMBER(10) CONSTRAINT EMPLOYEE_NN NOT NULL,

3 ENAME VARCHAR2(10) CONSTRAINT EMPLOYEE_UK UNIQUE,

4 JOB VARCHAR2(10) CONSTRAINT EMPLOYEE_PK PRIMARY KEY,

5 SAL NUMBER(10) CONSTRAINT EMPLOYEE_CK CHECK(SAL > 1000),

6 DEPTNO NUMBER(10) CONSTRAINT EMPLOYEE_FK REFERENCES DEPT(DEPTNO));

Table created.

1 SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME FROM USER_CONSTRAINTS

2* WHERE TABLE_NAME='EMPLOYEE'

SQL> /

CONSTRAINT_NAME C TABLE_NAME

------------------------------ - ------------------------------

EMPLOYEE_NN C EMPLOYEE

EMPLOYEE_CK C EMPLOYEE

EMPLOYEE_PK P EMPLOYEE

EMPLOYEE_UK U EMPLOYEE

EMPLOYEE_FK R EMPLOYEE

1 SELECT CONSTRAINT_NAME,COLUMN_NAME,TABLE_NAME FROM USER_CONS_COLUMNS

2* WHERE TABLE_NAME='EMPLOYEE'

SQL> /

CONSTRAINT_NAME

------------------------------

COLUMN_NAME

----------------------------------------------------------------------------------------------------

TABLE_NAME

------------------------------

EMPLOYEE_CK

SAL

EMPLOYEE

EMPLOYEE_FK

DEPTNO

EMPLOYEE

EMPLOYEE_NN

EMPNO

EMPLOYEE

EMPLOYEE_PK

JOB

EMPLOYEE

EMPLOYEE_UK

ENAME

EMPLOYEE

we can enable and disable constraints:

ALTER TABLE EMP

DISABLE CONSTRAINT EMP_PK;

ALTER TABLE EMP

DISABLE CONSTRAINT EMP_UK;

ALTER TABLE EMP

DISABLE CONSTRAINT EMP_FK;

ALTER TABLE BANK

DISABLE CONSTRAINT BANK_CH;

IF YOU ENTER CONSTRAINT AGAINST INFORMATION IN TO THE TABLE THEN YOU HAVE TO DELETE THAT INFORMATION THEN ONLY YOU CAN ENABLE THE CONSTRAINT.

ALTER TABLE EMP

ENABLE CONSTRAINT EMP_PK;

ALTER TABLE EMP

ENABLE CONSTRAINT EMP_UK;

ALTER TABLE EMP

ENABLE CONSTRAINT EMP_FK;

ALTER TABLE BANK

ENABLE CONSTRAINT BANK_CH;

SQL> CREATE TABLE EMPLOYEE

2 (EMPNO NUMBER(10),

3 ENAME VARCHAR2(10));

Table created.

SQL> ALTER TABLE EMPLOYEE

2 ADD CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EMPNO);

Table altered.

SQL> INSERT INTO EMPLOYEE

2 VALUES

3 (1,'PAVAN');

1 row created.

1 INSERT INTO EMPLOYEE

2 VALUES

3* (1,'KUMAR')

SQL> /

INSERT INTO EMPLOYEE

*

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.EMPLOYEE_PK) violated

HERE CONSTRAINT STOPED US ENTERING IN TO TABLE 1 AGAIN FOR EMPNO COLUMN FOR KUMAR. HERE TABLE HAS NOT ACCEPTED 1 AGAIN FOR EMPNO COLUMN WHICH IS ALREADY ASSIGNED TO PAVAN.

NOW I WILL DISABLE PRIMARY KEY.

ALTER TABLE EMPLOYEE

DISABLE CONSTRAINT EMPLOYEE_PK;

Table altered.

SQL> INSERT INTO EMPLOYEE

2 VALUES

3 (1,'KUMAR');

1 row created.

1 INSERT INTO EMPLOYEE

2 VALUES

3* (1,'GAYATRI')

SQL> /

1 row created.

SQL> SELECT * FROM EMPLOYEE;

EMPNO ENAME

---------- ----------

1 PAVAN

1 KUMAR

1 GAYATRI

HERE WHEN EVER I DISABLED CONSTRAINT PRIMARY KEY ON TABLE IT ALLOWED WRONG INFO.

HERE IF I WANT TO ENABLE PRIMARY NOW LIKE THIS

SQL> ALTER TABLE EMPLOYEE

2 ENABLE CONSTRAINT EMPLOYEE_PK;

ALTER TABLE EMPLOYEE

*

ERROR at line 1:

ORA-02437: cannot validate (SCOTT.EMPLOYEE_PK) - primary key violated

HERE WHAT HAPPENED IS THE TABLE CONTAINED WRONG INFO UNLESS YOU DELETE THAT IT WON'T ALLOW YOU TO ENABLE PRIMARY KEY CONSTRAINT ON TABLE.

SQL> SELECT * FROM EMPLOYEE;

EMPNO ENAME

---------- ----------

1 PAVAN

1 KUMAR

1 GAYATRI

SQL> DELETE FROM EMPLOYEE

2 WHERE ENAME IN ('KUMAR','GAYATRI');

2 rows deleted.

SQL> ALTER TABLE EMPLOYEE

2 ENABLE CONSTRAINT EMPLOYEE_PK;

Table altered.

SQL> SELECT * FROM EMPLOYEE;

EMPNO ENAME

---------- ----------

1 PAVAN

SQL> INSERT INTO EMPLOYEE

2 VALUES

3 (1,'PAVAN');

INSERT INTO EMPLOYEE

*

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.EMPLOYEE_PK) violated

DROPING CONSTRAINTS:

ALTER TABLE EMP

DROP CONSTRAINT EMP_PK;

ALTER TABLE EMP

DROP CONSTRAINT EMP_UK;

ALTER TABLE EMP

DROP CONSTRAINT EMP_FK;

ALTER TABLE BANK

DROP CONSTRAINT BANK_CH;

FOREIGN KEY CONSTRAINT DEFINED WITH ON DELETE CASCADE :

GENRALLY IF CHILD TABLE HAS FOREIGN KEY CONSTRAINT,THEN PARENT TABLE DOES NOT ALLOW US TO DELETE THE ROW. BUT IF FKC IS GIVEN WITH ODC,THEN IF WE DELETE THE ROWS IN PARENT TABLE SIMULTANESOLY RESPECTIVE RECORDS IN THE CHILD TABLE ALSO DELETED.

ALTER TABLE EMP

ADD CONSTRAINT EMP_FK FOREIGN KEY( DEPTNO ) REFERENCES DEPT ( DEPTNO ) ON DELETE CASCADE ;

FOREIGN KEY CONSTRAINT DEFINED WITH ON DELETE SET NULL :

GENRALLY IF CHILD TABLE HAS FOREIGN KEY CONSTRAINT,THEN PARENT TABLE DOES NOT ALLOW US TO DELETE THE ROW. BUT IF FKC IS GIVEN WITH ON DELETE SET NULL, THEN IF WE DELETE THE ROWS IN PARENT TABLE SIMULTANESOLY RECORDS IN THE CHILD TABLE WILL NOT BE DELETED ONLY RESPECTIVE COLUMN WILL BE KEPT NULL.

ALTER TABLE EMP

ADD CONSTRAINT EMP_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO) ON DELETE SET NULL;

SQL> CREATE TABLE EMP100

2 AS

3 SELECT * FROM EMP;

Table created.

SQL> CREATE TABLE DEPT100

2 AS

3 SELECT * FROM DEPT;

Table created.

SQL> ALTER TABLE DEPT100

2 ADD CONSTRAINT DEPT100_PK PRIMARY KEY(DEPTNO);

Table altered.

SQL> ALTER TABLE EMP100

2 ADD CONSTRAINT EMP100_FK FOREIGN KEY(DEPTNO) REFERENCES DEPT100(DEPTNO);

Table altered.

NOW IF YOU WANT TO DELETE FROM DEPT100 TABLE IT WON'T ALLOW YOU TO DELETE THE INFO BCOZ WHENEVER YOU WANT TO DELETE FROM TABLE IF IT ACCEPTS THE DELETION THERE ARE SOME PEOPLE WHO ARE WORKING IN EMP100 TABLE IN THAT PARTICULAR DEPARTMENT.

THEN FOR THOSE EMPLOYEE NO DEPARTMENT NAME.THE WHOLE CONCEPT OF FOREIGN KEY GOES WRONG.IF YOU WANT TO DELETE ANY INFO FROM DEPT100 TABLE YOU HAVE TO ALTER FOREIGN KEY ON EMP100 TABLE.

SQL> ALTER TABLE EMP100

2 DROP CONSTRAINT EMP100_FK;

Table altered.

SQL> ALTER TABLE EMP100

2 ADD CONSTRAINT EMP_FK FOREIGN KEY(DEPTNO) REFERENCES DEPT100(DEPTNO) ON DELETE CASCADE;

Table altered.

NOW IF YOU DELETE 10 IN DEPT100 TABLE THE EMPLOYEES WROKING IN DEPARTMENT 10 IN EMP100 TABLE ALSO DELETED THEN THERE IS NO PROBLEM.

SQL> SELECT * FROM DEPT100;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> DELETE FROM DEPT100

2 WHERE DEPTNO=10;

1 row deleted.

SQL> SELECT * FROM DEPT100;

DEPTNO DNAME LOC

---------- -------------- -------------

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> SELECT * FROM EMP100;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

11 rows selected.

1 ALTER TABLE EMP100

2* DROP CONSTRAINT EMP_FK

SQL> /

Table altered.

SQL> ALTER TABLE EMP100

2 ADD CONSTRAINT EMP_FK FOREIGN KEY(DEPTNO) REFERENCES DEPT100(DEPTNO) ON DELETE SET NULL;

Table altered.

SQL> SELECT * FROM DEPT100;

DEPTNO DNAME LOC

---------- -------------- -------------

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> DELETE FROM DEPT100

2 WHERE DEPTNO=20;

1 row deleted.

SQL> SELECT * FROM DEPT100;

DEPTNO DNAME LOC

---------- -------------- -------------

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> SELECT * FROM EMP100;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7788 SCOTT ANALYST 7566 19-APR-87 3000

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000

11 rows selected.

HERE ALL THE INFO OF 20 DEPARTMENT WAS NOT DELETED ONLY DEPTNO 20 WAS SET TO NULL.

ADDING NOT NULL AFTER CREATION OF THE TABLE:

ALTER TABLE EMP

MODIFY EMPNO NUMBER(10) CONSTRAINT EMP_NTN NOT NULL;

DROPING NOT NULL AFTER CREATION OF THE TABLE:

ALTER TABLE EMP

MODIFY EMPNO NUMBER(10) CONSTRAINT EMP_NTN NULL;

ADDING COLUMNS AFTER CREATION OF TABLE:

ALTER TABLE EMP ADD (ADDRESS VARCHAR2(10),CITY VARCHAR2(10));

SQL> CREATE TABLE EMP1

2 AS

3 SELECT * FROM EMP;

Table created.

SQL> DESC EMP1

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

EMPNO NUMBER(4)

ENAME VARCHAR2(10)

JOB VARCHAR2(9)

MGR NUMBER(4)

HIREDATE DATE

SAL NUMBER(7,2)

COMM NUMBER(7,2)

DEPTNO NUMBER(2)

SQL> ALTER TABLE EMP1

2 ADD ADDRESS VARCHAR2(10);

Table altered.

SQL> DESC EMP1

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

EMPNO NUMBER(4)

ENAME VARCHAR2(10)

JOB VARCHAR2(9)

MGR NUMBER(4)

HIREDATE DATE

SAL NUMBER(7,2)

COMM NUMBER(7,2)

DEPTNO NUMBER(2)

ADDRESS VARCHAR2(10)

SQL> ALTER TABLE EMP1

2 ADD (ADDRESS1 VARCHAR2(10), ADDRESS2 VARCHAR2(10));

Table altered.

SQL> DESC EMP1

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

EMPNO NUMBER(4)

ENAME VARCHAR2(10)

JOB VARCHAR2(9)

MGR NUMBER(4)

HIREDATE DATE

SAL NUMBER(7,2)

COMM NUMBER(7,2)

DEPTNO NUMBER(2)

ADDRESS VARCHAR2(10)

ADDRESS1 VARCHAR2(10)

ADDRESS2 VARCHAR2(10)

MODIFYING DATATYPES OF COLUMNS:

ALTER TABLE EMP

MODIFY EMPNO NUMBER(30);

WE CANNOT DECREASE DATATYPE OF COLUMNS IF DATA EXSITS.

ALTER TABLE EMP

MODIFY EMPNO NUMBER(1);

SQL> ALTER TABLE EMP1

2 MODIFY EMPNO NUMBER(20);

Table altered.

SQL> DESC EMP1

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

EMPNO NUMBER(20)

ENAME VARCHAR2(10)

JOB VARCHAR2(9)

MGR NUMBER(4)

HIREDATE DATE

SAL NUMBER(7,2)

COMM NUMBER(7,2)

DEPTNO NUMBER(2)

1 ALTER TABLE EMP1

2* MODIFY EMPNO NUMBER(4)

SQL> /

MODIFY EMPNO NUMBER(4)

*

ERROR at line 2:

ORA-01440: column to be modified must be empty to decrease precision or scale

DROPING COLUMNS:

SQL> ALTER TABLE EMP1

2 DROP COLUMN ADDRESS;

Table altered.

SQL> ALTER TABLE EMP1

2 DROP COLUMN ADDRESS1;

Table altered.

SQL> ALTER TABLE EMP1

2 DROP COLUMN ADDRESS2;

Table altered.

SQL> DESC EMP1

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

EMPNO NUMBER(4)

ENAME VARCHAR2(10)

JOB VARCHAR2(9)

MGR NUMBER(4)

HIREDATE DATE

SAL NUMBER(7,2)

COMM NUMBER(7,2)

DEPTNO NUMBER(2)

GROUP FUNCTIONS:

SELECT MAX(SAL), MIN(SAL),AVG(SAL),SUM(SAL),STDDEV(SAL),VARIANCE(SAL) FROM EMP;

SELECT COUNT ( * ) FROM EMP;SELECT COUNT ( DISTINCT DEPTNO ) FROM EMP;

DUAL TABLE:

DUAL IS A SYTEM TABLE.

WHICH WILL HAVE ONE COLUMN AND ONE ROW.

select * from dual;

select sysdate from dual;

select 20*20 from dual;

SELECT USER FROM DUAL;

SELECT USERID FROM DUAL;

1* SELECT MAX(SAL), MIN(SAL), AVG(SAL) , SUM(SAL) FROM EMP

SQL> /

MAX(SAL) MIN(SAL) AVG(SAL) SUM(SAL)

---------- ---------- ---------- ----------

5000 800 2073.21429 29025

SQL> SELECT JOB FROM EMP;

JOB

---------

CLERK

SALESMAN

SALESMAN

MANAGER

SALESMAN

MANAGER

MANAGER

ANALYST

PRESIDENT

SALESMAN

CLERK

CLERK

ANALYST

CLERK

14 rows selected.

SQL> SELECT DISTINCT(JOB) FROM EMP;

JOB

---------

ANALYST

CLERK

MANAGER

PRESIDENT

SALESMAN

SQL> SELECT COUNT(*) FROM EMP;

COUNT(*)

----------

14

SQL> SELECT COUNT(DISTINCT(JOB)) FROM EMP;

COUNT(DISTINCT(JOB))

--------------------

5

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE

---------

19-JUL-05

SQL> SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP

---------------------------------------------------------------------------

19-JUL-05 12.08.11.000001 AM +05:30

SQL> SELECT USER FROM DUAL;

USER

------------------------------

SCOTT

SQL> SELECT UID FROM DUAL;

UID

----------

66

SQL> SELECT * FROM DUAL;

D

-

X

SQL> DESC DUAL;

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

DUMMY VARCHAR2(1)

DUAL IS A SYSTEM TABLE.

SQL> SELECT 23* 23 FROM DUAL;

23*23

----------

529

SQL> SELECT 2*2 FROM DUAL;

2*2

----------

4