SQL - Constraints • Not Null

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

SQL - Constraints  •    Not Null

Domain :- (Coulmn) :- Column Level Constarints

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

DROP TABLE XX_EMP_TL

CREATE TABLE XX_EMP_TL

(

  EMPNO     NUMBER(4),

  ENAME     VARCHAR2(100),

  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', '1-MAR-2020','11-SEP-1985','M', 25000, 10, sysdate)

Commit;

select * from XX_EMP_TL

insert into  XX_EMP_TL (EMPNO, ENAME)  Values ( 1001, 'Mohan' )

Commit;

select * from XX_EMP_TL

EMPNO	ENAME	HIREDATE	DOB	GENDER	SAL	DEPTNO	CREATION_DATE
1000	Sachin	11-Sep-85	01-Mar-20	M	25000	10	26-03-2020 22:13
1001	Mohan						26-03-2020 22:19

DROP TABLE  APPS.XX_EMP_TL

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

DROP TABLE XX_EMP_TL

CREATE TABLE XX_EMP_TL

(

  EMPNO     NUMBER(4),

  ENAME     VARCHAR2(100),

  HIREDATE  DATE,

  DOB DATE NOT NULL,

  GENDER CHAR(1) Constraint GENDER_NN NOT NULL, 

  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

insert into  XX_EMP_TL (EMPNO, ENA/ME)  Values ( 1001, 'Mohan' )

insert into  XX_EMP_TL (EMPNO, ENAME, DOB )  Values ( 1001, 'Mohan', '1-JAN-1980' )

insert into  XX_EMP_TL (EMPNO, ENAME, DOB , GENDER)  Values ( 1001, 'Mohan', '1-JAN-1980', 'M' )

COMMIT;

select * from XX_EMP_TL

ALTER TABLE XX_EMP_TL MODIFY (HIREDATE  NOT NULL)

DELETE from XX_EMP_TL where HIREDATE is null

ALTER TABLE XX_EMP_TL MODIFY (SAL  Constraint SAL_NN NOT NULL )

insert into  XX_EMP_TL (EMPNO, ENAME, DOB , GENDER, HIREDATE )  Values ( 1001, 'Mohan', '1-JAN-1980', 'M' ,'26-MAR-2020')

insert into  XX_EMP_TL (EMPNO, ENAME, DOB , GENDER, HIREDATE, SAL )  Values ( 1001, 'Mohan', '1-JAN-1980', 'M' ,'26-MAR-2020', 20000)

Commit;

select * from XX_EMP_TL

EMPNO	ENAME	HIREDATE	DOB	GENDER	SAL	DEPTNO	CREATION_DATE
1000	Sachin	11-Sep-85	01-Mar-20	M	25000	10	26-03-2020 22:13
1001	Mohan	26-Mar-20	01-Jan-80	M	20000		26-03-2020 22:19

DROP TABLE  APPS.XX_EMP_TL

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

SELECT * FROM ALL_CONSTRAINTS 

WHERE TABLE_NAME = 'XX_EMP_TL'

CONSTRAINT_NAME	CONSTRAINT_TYPE	TABLE_NAME	SEARCH_CONDITION_VC
SYS_C005381485	C	XX_EMP_TL	DOB IS NOT NULL
GENDER_NN	C	XX_EMP_TL	GENDER IS NOT NULL
SYS_C005381487	C	XX_EMP_TL	HIREDATE IS NOT NULL
SAL_NN	C	XX_EMP_TL	SAL IS NOT NULL