■제약(constraint)
* 테이블에 제약이 필요한 이유 ?
"데이터의 품질을 높이기 위해서
필요하다."
예:
잘못된 data 가 입력되지 못하도록
설정을 하는 것.
*제약의 종류(2권 82페이지)
1. primary key--->중복, null 입력되지 않게.
2. unique-------->중복 입력 안되게.
3. not null------->null 입력 안되게.
4. check--------->지정된 data 만 입력되게.
5. foreign key--->부모테이블의
부모키 데이터만
입력될 수 있게
참조할 때 사용하는 제약.
■PRIMARY KEY
중복, NULL 값 입력방지하도록 제약을 걸어준다.
부모키라고 한다.
문제372.
아래의 테이블을 생성하는데
사원번호에 primary key 제약을 걸어서
생성하시오
테이블명: emp10
컬럼명: empno <<<여기 primary key
ename
sal
create table emp10
(empno number(10) primary key,
ename varchar2(20) ,
sal number(10) );
문재373.
emp10 테이블에
아래의 데이터를 입력하시오!
사원번호 사원이름 월급
7788 scott 3000
7566 smith 2500
7788 allen 5000
insert into emp10(empno, ename, sal)
values(7788, 'scott', 3000);
insert into emp10(empno, ename, sal)
values(7566, 'smith', 2500);
insert into emp10(empno, ename, sal)
values(7788, 'allen', 5000);
primary key 제약으로
empno 중복 안 들어가짐.
■UNIQUE KEY
중복을 허용하지 않겠다는 제약이다.
문제374.
아래의 테이블을 생성하는데
ename 에 unique 제약을
걸어서 생성하시오
테이블명: emp20
컬럼명 : empno, ename, sal
create table emp20
(empno number(10),
ename varchar2(20) unique,
sal number(10) );
테이블명에 대고
F4 를 누르고
탭에서 제약조건 누르면
또는
select * from user_constraints
where table_name='EMP20' ;
*****꼭 대문자로 EMP20 을 써야 한다.
컬럼 확인으로 하면
select *
from user_cons_columns
where table_name='EMP20';
문제375.
emp20 테이블에 ename 에 null 을
중복해서 입력해보시오
(ename 에 unique 걸려있음)
insert into emp20(empno, ename, sal)
values(1111, null, 3000);
insert into emp20(empno, ename, sal)
values(2222, null, 4000);
***null은 알수 없는 값이라
unique 걸어놔도 중복으로 안 잡힌다.
■CHECK 제약
CHECK 에 걸어둔 조건만 입출력이 가능하다.
문제376.
아래의 테이블을 생성하는데
부서위치가 서울, 부산, 대전만
입력되도록 체크제약을 걸어서
생성하시오
테이블명: 50
컬럼명: empno
ename
sal
loc <<<여기에 서울,부산,대전만
입력되도록 제약건다.
create table emp50
(empno number(10), ename varchar2(20),
sal number(10),
loc varchar2(20) check (loc in( '서울', '부산', '대전') );
시도해보면
insert into emp50
values(2122, 'scott', 4000, '광주');
제약때문에 에러뜸.
문제377.
아래의 테이블을 생성하는데
loc 에 제약을 걸어서
생성하시오!
테이블명: dept 100
컬럼명: deptno
loc <<<---NEW YORK, DALLAS, CHICAGO,
BOSTON 만 입력되게
생성하시오!
dname
create table dept100
(deptno number(10),
loc varchar2(20) check(loc in('NEW YORK','DALLAS','CHICAGO','BOSTON')),
dname varchar2(20) );
문제378.
dept 테이블에 있는 모든 데이터를
dept 100 에 입력하시오
(deptno, loc, dname 순서로 컬럼생성)
insert into dept100(deptno, loc, dname)
select deptno, loc, dname
from dept;
원래 dept 컬럼 순서가
deptno dname loc 다.
근데 새로 뽑을 dept100 은
deptno, loc, dname 으로
뽑고 싶다.
그래서 답처럼 컬럼지정을 해주되
순서도 똑같이 지정해준다.
**컬럼순서가 동일해야 한다 !!!!!!!!!!!!
문제379.
dept100 테이블의 loc 컬럼의 길이를
현재의 길이의
두배로 변경하시오.
desc dept100;
alter table dept100
modify loc varchar2(26);
deptno 10 번의 loc 부서를
washington 로 바꾸시오
문제380.
아래의 테이블을 생성하시오
(지정값만 입력/수정 되게 하는
제약조건 이용해서)
테이블명: emp700
컬럼명: empno
ename
sal <<<---0~9000 사이의 데이터만
입력/수정 되도록
create table emp700
(empno number(10),
ename varchar2(20),
sal number(10) check(sal between 0 and 9000));
문제381.
아래의 테이블을 생성하시오
테이블명: emp800
컬럼명: empno
ename <<<----성씨가 김씨,이씨,박씨
만 입력되게
sal
create table emp800
(empno number(10),
ename varchar2(20) check
(substr(ename,1,1) in ('김','이','박')),
sal number(10) );
insert into emp800
values(1111, '김준구', 3000);
insert into emp800
values(2222,'장보겸', 3000);
**장씨는 안먹는 걸 알 수 있당
또는
create table emp800
(empno number(10),
ename varchar2(20) check
(ename like '김%' or
ename like '이%' or
ename like '박%' ),
sal number(10) );
■FOREIGN KEY 제약
emp --------------------------dept
(자식) (부모)
deptno deptno
10 10
20 20
20 30
10 40
30
:
:
outer join 을 써도 서로에게 없는 값까지
연결해서 출력할 수 있으나
성능이 되게 느려진다.
그냥 애초에
부모테이블 안에 있는 숫자만 입력되게끔
제약을 걸어놓는게 좋다.
※설명: 만약 제약이 없어서 emp 테이블에
deptno에 80번과 같이
dept 테이블의 deptno 에 없는 데이터가
들어오게 되면
나중에 join 할 때 outer join 을 사용해야한다.
그런데 outer join 을 사용하게 되면
equi join 일 때 보다는 더 성능이 나빠질 수 있기 때문에
특별한 sql 튜닝방법이 필요해진다.
emp900---------------------dept900
(자식) (부모)
deptno deptno
10 10
20 20
20 30
10 40
30
:
:
문제382.
dept 테이블과 똑같은 테이블 구조와 데이터를 갖는
dept900 테이블을 생성하는데
deptno 에 primary key 제약을 걸어서 생성하시오.
create table dept900
as select * from dept;
ALTER TABLE dept900
ADD CONSTRAINT DEPT900_deptno_pk primary KEY(deptno);
문제383.
emp 테이블과 똑같은 테이블 구조와 이터를 갖는 emp900 테이블을 생성하는데
deptno 에 foreign key 제약을 걸고
dept900 에 deptno를
참조하겠다라고 해서 만드시오
CREATE TABLE dept900
AS SELECT * FROM dept;
(똑같이 emp900 도)
ALTER TABLE dept900
ADD CONSTRAINT DEPT900_deptno_pk primary KEY(deptno);
(이렇게 p키(부모키) 제약 걸고)
alter table emp900
add constraint emp900_deptno_fk foreign key(deptno)
references dept900(deptno);
(이렇게 f키 걸어주면 됨.)
dept900_deptno_pk
emp900_deptno_fk
제약 이름이다
바로 이해되게 부서명_컬럼명_제약종류로
만들어준다.
insert into emp900(empno, ename, sal, deptno)
values(2919, 'jack', 4000, 90);
에러뜸! 부서번호 90 은 제약위반으로 안된다.
■이미 생성되어져 있는 테이블에
제약을 거는 방법
문제384.
사원테이블에 empno 에 primary key 제약을
걸어보시오.
alter table emp
add constraint emp_empno_pk primary key(empno);
***중복, null 안걸려 있어야 제약걸기 가능
***이제 empno 에는 중복, null 안 걸림.
>>그게 p key 기능 ㅋ
create table emp434
(empno number(10) constraint
emp434_empno_pk primary key,
ename varchar2(20) );
이렇게 테이블 생성할 때도
제약이름 만들어 줄 수 있다.
이름 안정해주면 오라클이 알아서
sys 어쩌구로 정해준다.
문제385.
우리반 테이블에 empno 에
primary key 제약을 거시오
alter table emp2
add constraint emp2_empno_pk primary key(empno);
문제386.
사원 테이블의 ename 에 unique 제약을 거시오
alter table emp
add constraint emp_ename_un unique(ename);
**만약 이름에 중복데이터 있으면 안된다.
문제387.
우리반 테이블에 ename 에 unique 제약을
거시오
alter table emp2
add constraint emp2_ename_un unique(ename);
안된다!!!!!!
오전에 이상엽 을 추가했었다.
중복되서 유니크 못 건다.
문제388.
이상엽 데이터를 하나 지우고 다시 제약을 거시오
delete from emp2
where empno = 28
alter table emp2
add constraint emp2_ename_un unique(ename);
문제389.
아래의 데이터를 입력하시오
(제약 지우고)
select * from user_constraints;
해서 제약 뭐 있나 확인하고
alter table emp2
drop constraint emp2_ename_un;
alter table emp2
drop constraint emp2_empno_pk;
insert into emp2
select * from emp2
where ename = '이상엽';
insert 가 행으로 값 넣는거.
alter 가 컬럼넣는거.
문제390.(이거 오늘의 마지막문제로
뒤에 있음)
이상엽 데이터 한 개를 지우시오
delete from emp2
where rownum = 28;
왜 안되지????????????
delete 하기 전에
select ename, rownum
from emp2
where rownum= 28;
이 값을 시도해보면
표시할 데이터가 없다고 나온다.
화면 캡처: 2018-10-29 오후 4:27
rownum 의 특징상
1번만 출력되고
나머지 번호는 출력할 수 없다.
문제391.
우리반 테이블에 나이컬럼에
제약을 거는데 20살~55살 까지만
입력/수정 될 수 있도록
check 제약을 거시오
alter table emp2
add constraint emp2_age_ch
check(age between 20
and
55);
문제392.
우리반 테이블에 통신사 컬럼에 check 제약을 거는데
sk, lg, kt, cj hello 만 입력/수정 되겠금
체크제약을 거시오
대문자 sk 바꿔놓고 시작
update EMP2
set telecom = 'sk'
where telecom='SK';
alter table emp2
add constraint emp2_telecom_ch
check(telecom in('sk', 'lg', 'kt', 'cj hello'));
유이수 텔레콤데이터를 대문자로 변경하려하면
update emp2
set telecom = 'SK'
where ename = '유이수';
오류뜬다.
위에 제약 걸 때 쓴 소문자 그대로 써야
입력/수정이 가능하다
(대소문자 가려야 한다)
문제393.
이메일을 입력/수정 할때에
@와 . 이 있어야 입력/수정 되게끔
check 제약을 거시오
alter table emp2
add constraint emp2_email_ch
check(email like '%@%'
or
email like '%.%') ;
또는
check(email like '%@%.%')
갱신시도
update emp2
set email = 'dddd'
where ename= '유이수';
응 안나와
문제394.(오늘의 마지막 문제)
문제390번을 해결하시오.
문제390.
이상엽 데이터 한 개를 지우시오
delete from emp2
where rownum = 28;
alter table emp2
add rownum2 number(10);
update emp2
set rownum2 = rownum;
delete emp2
where rownum2= 28;
***rownum 특성상 1이상부터 출력되지 않는다.
rownum 의 내용이 필요한데
출력가능한 컬럼이 필요한거다
rownum2 컬럼을 만들어주고
update 로 rownum 값 갱신한다음에
delete rownum2=28
하면 된다.
■rowid
해당 row 의 유니크한 물리적 주소
AAAE / dAABAAALCxAAA
file번호 + block번호+ row번호
예: king 꺼 지우기
delete emp
where rowid= 'AAAE+pAABAAALCxAAA';
rownumber 랑 다르게
전부 rowid 는 출력 된다아아
rowid 는 맨 끝 알파벳이 오름차순으로 정렬된다.(나름의 규칙)
문제391.
숫자 1부터 100까지를 출력하는
쿼리를 작성하시오
select rownum
from dual
connect by rownum < 101;
문제392.
위의 쿼리의 결과를
ctas(create table as select)로
number100 이라는
테이블로 생성하시오
create table number100
as
select rownum as rn
from dual
connect by rownum <101;
*에러떴는데 꼭 별칭(alias) 쓰라고
오류떴음.
문제392.
dept테이블과
number100 을 cross 조인해서
deptno, loc, dname을 출력 해보시오
(쿼리 짜는것 보다는
결과가 어떤 컬럼으로, 몇개가 나올지
예상해서 맞춰보기)
select d.deptno, d.loc, d.dname
from dept d cross join number100 n100;
cross join = 컴마 찍는거
where절 연결고리 안 주면
중복해서 출력한다.
문제393.
상엽이 데이터를 100건 중복해서
출력하시오
SELECT e.*
FROM emp2 e cross JOIN NUMBER100 b
WHERE ename = '이상엽';
문제394.
emp2 테이블에 상엽이 데이터를 100건 입력하시오!
insert into emp2
select e.*
from emp2 e cross join number100 b
where ename='이상엽';
insert into emp2
select e.*
from emp2 e cross join number100 b
where ename='장보겸';
insert into emp2
select e.*
from emp2 e cross join number100 b
where ename='이상엽';
총 이상엽200건, 장보겸100건 추가
현재 이상엽201건, 장보겸 101건
문제395.
중복된 상엽이 데이터를 지우고
한 건만 남겨두시오!
DELETE FROM (SELECT *
FROM EMP2
WHERE ename = '이상엽')
WHERE ROWID NOT IN (select min(rowid)
FROM EMP2
GROUP BY ename);
******
(SELECT *
FROM EMP2
WHERE ename = '이상엽')
----->장보겸 이라는 중복데이터 100건도 있어서 출력값 한정시켜버림.
********************************************
min(rowid) 로 하면
이상엽 데이터에서
제일 먼저 입력 된 걸로 지정가능.
그걸 group by 하면 중복된 건 안 뽑힘
select ename, min(rowid) FROM EMP2 GROUP BY ename;
화면 캡처: 2018-10-30 오전 10:17
■제약 삭제
*scott이 가지고 있는 테이블에 걸린
제약 리스트를 확인하시오
select * from user_cons_columns;
문제396.
emp900 테이블에 emp900_deptno_fk를
삭제하시오
alter table emp900
drop constraint emp900_deptno_fk;
삭제 후 확인
select * from user_cons_columns;
문제397.
scott 이 가지고 있는 모든 제약을
다 삭제하시오
(DUAL 테이블 쓰면 안나옴. 왜일까요?)
select 'alter table ' || table_name ||
' drop constraint ' || constraint_name || ';'
from user_cons_columns;
나온 결과를
다 긁어서
실행돌리면 지워진다는 소리임
'sql' 카테고리의 다른 글
| 13-1. DATABASE OBJECT(INDEX) (0) | 2019.03.31 |
|---|---|
| 13. DATABASE OBJECT(VIEW, SEQUENCE, SYNONYM) (0) | 2019.03.31 |
| 11-4. 데이터 조작 언어(계층형 질의문) (0) | 2019.03.31 |
| 11-3. 데이터 조작 언어(DCL문) (0) | 2019.03.31 |
| 11-2. 데이터 조작 언어(DDL문) (0) | 2019.03.30 |
