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
Post a Comment