SQL - Constraints • Unique


--====================================================
SQL - Constraints      Unique
--====================================================

DROP TABLE XX_EMP_TL

CREATE TABLE XX_EMP_TL
(
  EMPNO     NUMBER(4),
  ENAME     VARCHAR2(100),
  MOBILE  NUMBER  Constraint MOBILE_UN UNIQUE , -- Column Level Constraints --
  EMAIL VARCHAR2(100),
  GENDER CHAR(1),  
  SAL       NUMBER(7),
  DEPTNO    NUMBER(2),
  CREATION_DATE Date Default sysdate,  
  Constraint EMAIL_UN UNIQUE ( EMAIL) -- Table Level Constraints -- 
)

select * from XX_EMP_TL

insert into  XX_EMP_TL (EMPNO, ENAME, MOBILE, EMAIL, GENDER, SAL, DEPTNO, CREATION_DATE)  Values ( 1000, 'Sachin','9898100100', 'sachin@gmail.com', 'M', 15000, 10, sysdate)

insert into  XX_EMP_TL (EMPNO, ENAME, MOBILE, EMAIL, GENDER, SAL, DEPTNO, CREATION_DATE)  Values ( 1000, 'Sachin','9898100101', 'sachin1@gmail.com', 'M', 15000, 10, sysdate) 

COMMIT;

select * from XX_EMP_TL

EMPNO
ENAME
MOBILE
EMAIL
GENDER
SAL
DEPTNO
CREATION_DATE
1000
Sachin
9898100100
sachin@gmail.com
M
15000
10
27-03-2020 14:00
1000
Sachin
9898100101
sachin1@gmail.com
M
15000
10
27-03-2020 14:01


ALTER TABLE XX_EMP_TL ADD Constraint XX_EMP_UN UNIQUE (EMPNO, ENAME)

select * from   XX_EMP_TL

UPDATE XX_EMP_TL SET ENAME = 'SACHIIN1' where MOBILE = 9898100101

COMMIT;

EMPNO
ENAME
MOBILE
EMAIL
GENDER
SAL
DEPTNO
CREATION_DATE
1000
Sachin
9898100100
sachin@gmail.com
M
15000
10
27-03-2020 14:00
1000
SACHIIN1
9898100101
sachin1@gmail.com
M
15000
10
27-03-2020 14:01


ALTER TABLE XX_EMP_TL ADD Constraint XX_EMP_UN UNIQUE (EMPNO, ENAME)

insert into  XX_EMP_TL (EMPNO, ENAME, MOBILE, EMAIL, GENDER, SAL, DEPTNO, CREATION_DATE)  Values ( 1000, 'Sachin3','9898100102', 'sachin2@gmail.com', 'M', 15000, 10, sysdate)

COMMIT;

EMPNO
ENAME
MOBILE
EMAIL
GENDER
SAL
DEPTNO
CREATION_DATE
1000
Sachin
9898100100
sachin@gmail.com
M
15000
10
27-03-2020 14:00
1000
SACHIIN1
9898100101
sachin1@gmail.com
M
15000
10
27-03-2020 14:01
1000
Sachin3
9898100102
sachin2@gmail.com
M
15000
10
27-03-2020 14:04


insert into  XX_EMP_TL (EMPNO, ENAME, MOBILE, EMAIL, GENDER, SAL, DEPTNO, CREATION_DATE)  Values ( '', '','', '', 'M', 15000, 10, sysdate)

select * from   XX_EMP_TL

EMPNO
ENAME
MOBILE
EMAIL
GENDER
SAL
DEPTNO
CREATION_DATE
1000
Sachin
9898100100
sachin@gmail.com
M
15000
10
27-03-2020 14:00
1000
SACHIIN1
9898100101
sachin1@gmail.com
M
15000
10
27-03-2020 14:01
1000
Sachin3
9898100102
sachin2@gmail.com
M
15000
10
27-03-2020 14:04
1000
Sachin4

sachin4@gmail.com
M
15000
10
27-03-2020 14:11
1000
Sachin5

sachin5@gmail.com
M
15000
10
27-03-2020 14:12
1000
Sachin6


M
15000
10
27-03-2020 14:12
1000
Sachin7


M
15000
10
27-03-2020 14:13
1000



M
15000
10
27-03-2020 14:14




M
15000
10
27-03-2020 14:15




M
15000
10
27-03-2020 14:15




M
15000
10
27-03-2020 14:15




M
15000
10
27-03-2020 14:15




M
15000
10
27-03-2020 14:15


DROP TABLE  APPS.XX_EMP_TL

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

SELECT * FROM ALL_CONSTRAINTS 
WHERE TABLE_NAME = 'XX_EMP_TL'

--=============
CONSTRAINT_NAME
CONSTRAINT_TYPE
TABLE_NAME
MOBILE_UN
U
XX_EMP_TL
EMAIL_UN
U
XX_EMP_TL
XX_EMP_UN
U
XX_EMP_TL
DEPT_CK
C
XX_EMP_TL

Comments

Popular posts from this blog

E-Text Report In Fusion | Types of E-Text reports

Supplier API's

How to pass default Parameter in cursor