[SQL] PRIMARY & FOREIGN KEY, UNIQUE, NOT NULL, NVL

• 제약조건 관련 키워드
• PRIMARY KEY: 해당 컬럼이 null이 아니고 유일한 값을 가져야 함
• NOT NULL: 해당 컬럼은 반드시 값이 입력되어야 함
• FOREIGN KEY: 다른 테이블의 특정 컬럼과 참조 관계를 설정함
• UNIQUE: 컬럼에 입력되는 값이 중복되면 안 됨
• DEFAULT: 컬럼에 값을 지정하지 않으면 기본값이 자동으로 입력됨
• CHECK (sex in ('M','F')): 컬럼 값이 지정된 값 중 하나만 허용되도록 제한

 

• 함수와 연산자
• SYSDATE: 현재 날짜와 시간을 반환 (입력일 기본값으로 사용)
• NVL(z.bunji, ''): bunji가 null이면 빈 문자열로 대체

/*
1. 아래 제약사항을 설정하여 const_work 테이블을 생성.
저장데이터 : 번호, 아이디, 이름, 주소seq, 전화번호, 나이, 성별, 입력일
-제약사항
-아이디는 null을 허용하지 않으면서 유일해야한다. - primary key
-이름은 반드시 입력 되어야한다. - not null
-주소seq는 null을 허용하지만 값이 입력될때에는 cp_zipcode 테이블에
seq컬럼에 존재하는 값으로만 입력 가능하다. - foreign key
-전화번호는 없을 수 도 있지만 있다면 유일. - unique
-나이는 컬럼명이 생략되어 레코드가 삽입되면
0이 기본 입력되어야한다. -default
-성별은 'M' 이나 'F'로만 입력가능 - check
-입력일은 컬럼명이 생략되어 레코드가 삽입되면
현재 날짜로 추가되어야한다. - default
*/
alter table cp_zipcode
add constraint pk_cp_zipcode PRIMARY KEY (seq);

create table const_work(
num number(10),
id varchar2(20),
name varchar2(20) not null,
seq number(5) null,
call varchar2(20) unique,
age number(10) default 0,
sex char(1) check (sex in ('M','F')),
input_date date default sysdate,
constraint pk_id primary key(id),
constraint fk_seq foreign key(seq) references cp_zipcode(seq)
);

/*
2. 위에 생성된 const_work에서 설정된 제약사항과 default를 검색해보세요.
*/
select column_name, data_default
from user_tab_columns
where table_name = 'CONST_WORK';

/*
3.위에서 생성된 const_work 테이블에서 제약사항이 성공하는 모든 경우와
실패하는 모든 경우의 쿼리문을 작성하세요.
*/

///**귀찮음**///
--insert into in const_worl vlaues(null); ~ 제약사항에 위배되는 것들.
/*
4. 위에 생성된 테이블에 아래와 같은 레코드를 추가
*/

INSERT INTO const_work (num, id, name, seq, call, sex, input_date)
VALUES (1, 'user01', '홍길동', 182, '010-1234-5678', 'M', SYSDATE);
INSERT INTO const_work (num, id, name, seq, call, sex, input_date)
VALUES (2, 'user02', '김철수', 300, '010-4321-9876', 'M', SYSDATE);
INSERT INTO const_work (num, id, name, seq, call, sex, input_date)
VALUES (3, 'user03', '박영수', 52000, '010-2222-4312', 'M', SYSDATE);
INSERT INTO const_work (num, id, name, seq, call, sex, input_date)
VALUES (4, 'user04', '이상호', 540, '010-4747-2662', 'M', SYSDATE);
COMMIT;

select * from const_work;

/*
5. const_work테이블에서 아이디,이름,전화번호,우편번호, 주소를 조회
단, 주소는 cp_zipcode테이블에 sido, gugun,dong, bunji 컬럼을 결합하여
하나로 출력 (scalar subquery로 사용해볼 것)
*/
select
    c.id,c.name,c.call phone_number,c.seq zipcode,
    (SELECT z.sido || ' ' || z.gugun || ' ' || z.dong || ' ' || nvl(z.bunji, '' )
     from cp_zipcode z
where z.seq = c.seq) address
from const_work c;