How to Manage and Control SQL Constraints

How to Manage and Control SQL Constraints :- 

--=======================================================================

How to Manage Constraints in Oracle SQL

--=======================================================================

DROP TABLE XX_EMP_TL

CREATE TABLE XX_EMP_TL

(

  EMPNO     NUMBER(4),

  ENAME     VARCHAR2(100) UNIQUE,

  HIREDATE  DATE ,

  DOB DATE,

  GENDER CHAR(1) , 

  SAL       NUMBER(7) ,

  DEPTNO    NUMBER(2),

  CREATION_DATE Date Default sysdate   

)

select * from XX_EMP_TL

insert into  XX_EMP_TL (EMPNO, ENAME, HIREDATE, DOB, GENDER, SAL, DEPTNO, CREATION_DATE)  Values ( 1000, 'Sachin','11-SEP-1985', '1-MAR-2020', 'M', 25000, 10, sysdate)

Commit;

select * from XX_EMP_TL

--===========

How to View my COnstraints

--==========

SELECT * FROM ALL_CONSTRAINTS 

WHERE TABLE_NAME = 'XX_EMP_TL'

--===========

How to View Columns on which Costraints created

--==========

select * from USER_CONS_COLUMNS

WHERE TABLE_NAME = 'XX_EMP_TL'

order by 2

--===========

How to ADD Costraints

--==========

ALTER TABLE XX_EMP_TL ADD Constraint SAL_CK CHECK ( SAL > 10000)

ALTER TABLE XX_EMP_TL ADD CONSTRAINT EMP_PKEY PRIMARY KEY (EMPNO)

ALTER TABLE XX_EMP_TL ADD CONSTRAINT XX_GEN_CK CHECK ( GENDER IN ('M','F') )

--===========

How to Modify Costraints

--==========

ALTER TABLE XX_EMP_TL MODIFY (GENDER  NOT NULL)

ALTER TABLE XX_EMP_TL MODIFY (SAL  Constraint SAL_NN NOT NULL )

ALTER TABLE XX_EMP_TL MODIFY (HIREDATE  NOT NULL)

--===========

How to Disable Costraints

--==========

SELECT * FROM ALL_CONSTRAINTS  WHERE TABLE_NAME = 'XX_EMP_TL'

select * from USER_CONS_COLUMNS WHERE TABLE_NAME = 'XX_EMP_TL'

AND COLUMN_NAME = 'SAL'

edit  XX_EMP_TL

insert into  XX_EMP_TL (EMPNO, ENAME, HIREDATE, DOB, GENDER, SAL, DEPTNO, CREATION_DATE)  Values ( 1001, 'MANOJ','11-SEP-1985', '1-MAR-2020', 'M','', 10, sysdate)

Commit;

ALTER TABLE XX_EMP_TL DISABLE CONSTRAINT SAL_NN

--===========

How to Enable Costraints

--==========

ALTER TABLE XX_EMP_TL ENABLE CONSTRAINT SAL_NN

--===========

How to Drop/Remove Costraints

--==========

select * from USER_CONS_COLUMNS WHERE TABLE_NAME = 'XX_EMP_TL'

AND COLUMN_NAME = 'SAL'

ALTER TABLE XX_EMP_TL  DROP CONSTRAINT SAL_NN