sql중급 / primary key, unique, not null, check, foreign key

104 데이터의 품질 높이기 1(PRIMARY KEY)

ㅇ제약(constraint)이 필요한 이유

"데이터의 품질을 높이기 위해서"

데이터 분석할 때 데이터 정제 작업에 거의 80%이상을 사용함. 데이터 정제 작업이 코딩을 못하면 노가다여서 인내를 가지고 작업해야하는데 이 작업이 코딩을 잘하면 쉽고 재밌게 할 수 있음.

근본적으로 데이터를 처음에 입력받을때부터 잘 정제해두면 나중에 정제 작업의 시간을 줄일 수가 있음.

ex) 우리반 테이블 입력 시 telecom 데이터를 입력할 때 skt,sk,SKT 등 다양하게 입력되었음 > 이 경우 sql이 복잡해지고, 복잡합 sql이라도 정제할 줄 모르면 잘못된 데이터 분석을 하게 됨. 따라서 처음부터 제약(constraint)을 이용해 sk로만 입력되게 할 수 있음.

 

ㅇ제약의 종류 5가지

1. primary key : 중복된 데이터와 null값이 입력되지 못하게 하는 제약

2. unique : 중복된 데이터를 입력하지 못하게 하는 제약

3. not null : null값을 입력하지 못하게 하는 제약

4. check : 정의한 데이터만 입력 또는 수정하게끔 하는 제약

5. foreign key : 참조하는 컬럼에 거는 제약

 

ㅇprimary key 제약 생성 예제

-- 테이블 생성시 제약을 걸면서 생성하는 방법
create table emp20
( empno   number(10) constraint emp20_empno_pk primary key,
  ename   varchar2(20) );

 

 

제약 이름은 의미있게 부여. 보통 '테이블명_컬럼명_제약' 으로 작성

위 코드의 제약을 풀어보면 emp20에 empno컬럼에 primary key제약을 건다는 뜻.

-- emp20 테이블에 데이터를 입력하시오(empno = 1122, ename = SCOTT)
insert into emp20(empno, ename)
  values (1122, 'SCOTT');
  
-- emp20 테이블에 아래의 데이터를 입력하시오(empno = null, ename = smith)
insert into emp20(empno, ename)
  values (null,'SMITH');
  -- 에러 : primary key제약이 걸린 컬럼에 null값 입력할 수 없음
  
-- emp20 테이블에 아래의 데이터를 입력하시오(empno = 1122, ename = allen)
insert into emp20(empno, ename)
  values (1122, 'ALLEN');
  -- 에러 : primary key제약이 걸린 컬럼에 중복된 데이터값을 입력할 수 없음

primary key제약으로 인해 emp20테이블의 empno에 중복된 데이터와 null값이 입력될 수 없다는게 보장됨.

-- emp20 테이블에 걸린 primary key제약을 확인하는 방법
select t.table_name, t.constraint_name, c.column_name, t.constraint_type
 from user_constraints t, user_cons_columns c
 where t.constraint_name = c.constraint_name
    and t.table_name = 'EMP20';

위의 코드를 통해 출력된 결과를 보고 emp20테이블 empno컬럼에 primary key제약이 걸려있음을 확인 > 따라서 empno컬럼에는 중복된 데이터가 없다는 것을 확신할 수 있음(where empno is not null 혹은 select distinct empno를 써줄 필요 없음)

 

문제561. 아래의 테이블을 생성하고 deptno에 primary key 제약을 걸어서 생성하시오

테이블명 : dept900, 컬렴명 : deptno / loc / dname

create table dept900
( deptno   number(10)  constraint dept900_deptno_pk primary key,
  loc      varchar2(20),
  dname    varchar2(20) );

문제562. dept테이블의 모든 데이터를 dept900테이블에 입력하시오

(데이터 엔지니어가 하는 일 중에 데이터 이행에 관련된 sql)

insert into dept900(deptno, loc, dname)
select deptno, loc, dname
 from dept;
 
 --서브쿼리를 사용한 insert문( 컬럼순서 유의할 것 )

문제563. 아래의 데이터를 dept900테이블에 입력하시오(deptno = 50, dname = research, loc = seoul)

insert into dept900(deptno, dname, loc)
  values(50, 'research','seoul');

문제564. dept900 테이블에 걸린 제약이 뭐가 있는지 확인하시오( 중요 sql for data 분석가)

select t.table_name, t.constraint_name, c.column_name, t.constraint_type
 from user_constraints t, user_cons_columns c
 where t.constraint_name = c.constraint_name
    and t.table_name = 'DEPT900';

 

ㅇ제약을 거는 방법 2가지

1. 테이블 생성할 때 제약을 거는 방법

2. 만들어진 테이블에 제약을 거는 방법

 

-- dept테이블에 deptno에 primary key제약 걸기
alter table dept 
 add constraint dept_deptno_pk primary key(deptno);

dept테이블의 deptno컬럼에 primary key제약을 걸었기 때문에 이제 더이상 중복된 데이터와 null값이 입력안됨.

기존에 제약에 위반된 데이터가 있었다면 제약이 걸리지 않음 > 제약 걸기 전 데이터 정제를 해야함.

 

문제565. 사원 테이블 empno에 primary key제약을 거시오

alter table emp
 add constraint emp_empno_pk primary key(empno);

문제566. 이름이 KING인 사원의 사원번호를 null로 변경하시오

update emp
set empno = null
where ename = 'KING';
-- 에러 : primary key제약으로 인해 null값 입력 불가

 

 

ㅁSQL을 배우는 이유 

"데이터 분석을 위해"

> 데이터 추출, 과거 데이터를 가지고 미래를 예측(머신러닝)

 

문제567. 서울시에는 버거킹 매장이 몇개가 있는가? (2021년 9월 기준)

select count(*)
 from market
 where 상호명 like '%버거킹%';

문제568. 서울시에는 롯데리아 매장이 몇개가 있는가?

select count(*)
 from market
 where 상호명 like '%롯데리아%';

문제569. (점심시간 문제) 서울시에 맥도날드, 버거킹, 롯데리아 매장이 얼마나 있는지 카운트하여 매장수별로 순위를 부여하시오.

-- 코드1 : case when 사용
select hbg_name, count(*) as cnt, rank() over (order by count(*) desc) as 순위
  from (select case when 상호명 like  '%맥도날드%' then '맥도날드'
                         when 상호명 like  '%롯데리아%' then '롯데리아'
                         when 상호명 like  '%버거킹%' then '버거킹'
                         else 상호명 end as hbg_name
             from market ) 
   where hbg_name in ('맥도날드','롯데리아','버거킹')
   group by hbg_name ;

 
 
-- 코드2 : sum+case when, unpivot 사용
select 매장, 매장수, rank() over (order by 매장수 desc ) as 순위
 from (select sum(case when 상호명 like '%맥도날드%' then 1 else 0 end) as 맥도날드,
                 sum(case when 상호명 like '%롯데리아%' then 1 else 0 end) as 롯데리아,
                 sum(case when 상호명 like '%버거킹%' then 1 else 0 end) as 버거킹
             from market ) 
 unpivot ( 매장수 for 매장 in (맥도날드,롯데리아,버거킹) );

 

 

105 데이터의 품질 높이기 2(UNIQUE)

중복된 데이터를 허용하지 않게 하는 제약

1. 테이블을 생성할 때 제약을 걸면서 생성하는 방법

create table dept701
( deptno   number(10).
  loc      varchar2(20) constraint dept701_loc_un uniaue,
  dname    varchar2(20) );

2. 만들어진 테이블에 제약을 거는 방법

alter table dept
 add  constraint dept_loc_un unique(loc) ;

 

문제570. emp테이블에 ename에 unique제약을 거시오

alter table emp
  add constraint emp_ename_un unique(ename);

문제571. market테이블 상가업소번호 컬럼에 primary key제약을 거시오

alter table market
 add constraint market_num_pk primary key(상가업소번호) ;

 

ㅁprimary key제약이나 unique제약을 컬럼에 걸면 그 컬럼에 자동으로 unique 인덱스가 생성됨.

- 인덱스를 생성하는 방법 2가지

1. 수동

create index emp_sal
on emp(sal);

2. 자동

primary key제약이나 unique제약을 걸면 제약과 함께 인덱스가 컬럼에 자동 생성.

-- 인덱스 목록 확인
select index_name, uniqueness
 from user_indexes
 where table_name = 'MARKET';
 
-- 인덱스를 타고 실행됨을 확인
explain plan for
 select *
 from market
 where 상가업소번호 = '25545908';
 
 select * from table(dbms_xplan.display);

 

 

106 데이터의 품질 높이기 3(NOT NULL)

null값을 입력하지 못하게 하는 제약

null값 데이터 => 결측치 

데이터 분석 시 데이터 전처리시에 반드시 확인해야하는 2가지 데이터

1. 결측치

2. 이상치

 

ㅇnot null 제약 거는 방법

1. 테이블 생성시 제약거는 방법

create table emp407
( empno   number(10)  constraint emp407_empno_nn not null,
  ename   varchar2(10) );

2. 만들어진 테이블에 제약거는 방법

alter table dept
  modify dname constraint dept_dname_nn not null;
  -- add가 아니라 modify를 사용하는 것에 유의

 

문제572. 우리반 테이블의 이메일 컬럼에 not null제약을 거시오

alter table emp14
  modify email constraint emp14_email_nn not null;

문제573. 우리반 테이블의 이메일 컬럼에 걸린 not null 제약을 삭제하시오

alter table emp14
 drop constraint emp14_email_nn;

 

ㅇ제약 삭제하는 방법

alter table 테이블명
 drop constraint 제약명;

 

문제574. emp테이블에 어떤 제약이 걸려있는지 조회하시오.

select t.table_name, t.constraint_name, c.column_name, t.constraint_type
 from user_constraints t, user_cons_columns c
 where t.constraint_name = c.constraint_name
    and t.table_name = 'EMP';

문제575. emp테이블에 걸린 모든 제약을 다 삭제하시오

alter table emp
 drop constraint emp_empno_pk ;
 
alter table emp
 drop constraint emp_ename_un ;
 
alter table emp
 drop constraint "BIN$sou9Dy0WQXqt3Tl3td2jHQ==$0" ;

 

 

107 데이터의 품질 높이기 4(CHECK)

사용자가 허용한 데이터만 입력 또는 수정되게끔 하는 제약

-- 월급이 0 ~ 9000사이의 데이터만 입력 또는 수정되게끔 check제약을 거시오
alter table emp
 add constraint emp_sal_ck check( sal between 0 and 9000 );
 
-- (확인) KING의 월급을 9500으로 변경하시오
update emp
 set sal = 9500
 where ename = 'KING';
 -- 에러 / check 제약조건 위배

문제576. 우리반 테이블에 이메일에 체크 제약을 거는데 이메일에 @가 포함되어 있지 않으면 에러가 나게끔 하시오

alter table emp14
 add constraint emp14_email_ck check( email like '%@%' );

문제577. emp14 테이블에 걸린 emp14_email_ck 제약을 삭제하시오

alter table emp14
 drop constraint emp14_email_ck;

문제578. 다시 emp14테이블에 email에 제약을 거는데 '@'과 '.'이 포함되어져야 입력이 가능하도록 하시오

alter table emp14
 add constraint emp14_email_ck check( email like '%@%.%' );

문제579. 우리반 테이블에 통신사 컬럼에 제약을 거는데 데이터가 sk, lg, kt만 입력 또는 수정되게끔 하시오

alter table emp14
 add constraint emp14_telecom_ck check( telecom in ('sk','lg','kt') );

 

 

108 데이터의 품질 높이기 5(FOREIGN KEY)

dept테이블 deptno에 primary key제약을 걸고 emp테이블의 deptno에 foreign key제약을 걸면 앞으로 emp 테이블 deptno컬럼에 데이터를 입력할 때 dept테이블에 존재하는 부서번호에 대한 데이터만 입력 또는 수정할 수 있게 됨.

그리고 dept테이블의 데이터를 지울 때 emp테이블에 관련된 부서번호가 있다면 dept테이블의 해당 부서번호는 지워지지 않음(에러). 자식테이블인 emp테이블에 해당 부서번호가 없어야지만 지워짐.

-- emp테이블과 dept테이블 다시 생성
-- dept테이블의 deptno에 primary key제약 거시오
alter table dept
 add constraint dept_deptno_pk primary key(deptno);
 
-- emp테이블 deptno에 foreign key제약을 걸면서 dept테이블의 deptno를 참조하게끔 하시오
alter table emp
  add constraint emp_deptno_fk foreign key(deptno) references dept(deptno);
  
-- (확인) king의 부서번호를 70번으로 변경하시오
update emp
 set deptno = 70
 where ename = 'KING';
 -- 에러 / 제약조건 위배 : 부모 키 없음
 
-- (확인) 다음의 데이터를 사원 테이블에 입력하시오 (사원번호:3929 / 사원이름:JACK / 월급:3000 / 부서번호:90 )
insert into emp(empno, ename, sal, deptno)
 values (3929, 'JACK',3000,90);
 -- 에러 / 제약조건 위배 : 부모 키 없음
 
-- (확인) 부서테이블에서 10번 부서번호의 행을 삭제하시오
delete from dept
 where deptno = 10;
 -- 에러 / 제약조건 위배 : 자식 레이블 발견
 
-- emp테이블의 emp_deptno_fk 제약을 삭제하시오
alter table emp
 drop constraint emp_deptno_fk;
 
-- dept테이블의 dept_deptno_pk 제약을 삭제하시오
alter table dept
 drop constraint dept_deptno_pk;

자식 테이블의 제약을 먼저 삭제해야 부모 테이블의 제약을 삭제할 수 있음

foreign key 삭제 > primary key 삭제 가능

 

문제580. 다시 dept 테이블 deptno컬럼에 primary key 제약을 걸고 emp 테이블 deptno에 foreign key 제약을 걸면서 dept 테이블의 deptno를 참조하겠다라고 하세요

alter table dept
 add constraint dept_deptno_pk primary key(deptno);
 
alter table emp
  add constraint emp_deptno_fk foreign key(deptno) references dept(deptno);

문제581. dept 테이블의 deptno에 걸린 primary key제약을 삭제하시오

alter table dept
 drop constraint dept_deptno_pk;
 -- 에러 / 외부 키에 의해 참조됨

자식키 제약이 있어 삭제할 수 없음. 삭제하려면 자식키 제약을 먼저 삭제하고 부모키 제약을 drop하거나 아니면 다음의 방법으로 한번에 다 삭제하면 됨

alter table dept
 drop constraint dept_deptno_pk cascade;

 

 

문제582. market테이블에서 상호명에 떡볶이가 포함된 모든 컬럼들을 출력하시오

select *
 from market
 where 상호명 like '%떡볶이%';

문제583. 상호명에 떡볶이를 포함하고 상권업종소분류명이 떡볶이전문인 상호명과 건수를 출력하는데 건수가 높은것부터 출력하시오

select 상호명, count(*)
 from market
 where 상호명 like '%떡볶이%'
   and 상권업종소분류명 = '떡볶이전문'
 group by 상호명
 order by 2 desc;

 

문제584. 2017년도 서울시 소상공인 데이터를 가지고 테이블을 구성하시오 

문제585. (오늘의 마지막 문제) 2017년도와 2021년도의 서울지역의 떡볶이 업종의 변화를 출력하고 간단하게 해석하시오