SQL (시퀀스와 제약조건, 식별자, 비식별자)

시퀀스

시퀀스란 번호를 순차적으로 증가하는 숫자를 생성하는 객체이다.
MariaDB 의 경우 컬럼 자체에 auto_increment라는 번호를 자동으로 증가를 할 수 있는 옵션이 있으나 
오라클에서는 자동 증가 컬럼을 사용할 수가 없다. 
그러므로 오라클에서는 컬럼의 값을 증가시키기 위해서는 시퀀스를 사용하거나 MAX(컬럼) + 1 을 사용하여 번호를 증가시켜야 한다.

 

시퀀스 옵션

CREATE SEQUENCE 시퀀스명
[INCREMENT BY n]          <-- 시퀀스 실행 시 증가시킬 값
[START WITH n]            <-- 시퀀스의 시작값 (MINVALUE와 같거나 커야 한다)
[MINVALUE n | NOMINVALUE] <-- 시퀀스가 시작되는 최솟값
[MAXVALUE n | NOMAXVALUE] <-- 시퀀스가 끝나는 최댓값
[NOCYCLE | CYCLE] <-- NOCYCLE (반복안함), CYCLE(시퀀스의 최댓값에 도달 시 최솟값 1부터 다시 시작)
[NOCACHE | CACHE] <-- NOCACHE(사용안함), CACHE(값을 미리 메모리에 할당해서 속도가 빠르며, 동시 사용자가 많을 경우 유리, 옵션을 사용하지 않으면 기본값은 20이다.)
[NOORDER | ORDER] <-- NOORDER(사용안함), ORDER(요청 순서로 값을 생성하여 발생 순서를 보장하지만 조금의 시스템 부하가 있음)

*하지만 실무에서는 전체옵션을 거의 다 사용하는 경우는 없으며 증가값, 반복, maxvalue정도만 사용하는 경우가 많다*

 

시퀀스

--시퀀스 생성--
create sequence number_seq;

--생성된 시퀀스 조회--
select * from user_sequences; --생성된 시퀀스 목록 출력--

create table test_sqe(
no number,
name varchar2(10)
);

현재는 옵션을 따로 사용하지 않고 시퀀스를 만든 상태이다. 이 경우 기타옵션은 자동으로 설정된다.

 

--내용삽입--
insert into test_sqe values (number_seq.nextval, '김한주');
insert into test_sqe values (number_seq.nextval, '유다빈');
insert into test_sqe values (number_seq.nextval, '나상현');
insert into test_sqe values (number_seq.nextval, '한로로');

--테이블 조회--
select * from test_sqe;
--NO에 자동적으로 번호가 들어감--

따로 번호를 number타입으로 삽입하지 않아도 자동적으로 숫자가 하나씩 1로 증가하며 연속적인 번호가 생성된다.

--현재 생성된 시퀀스 조회--
select number_seq.currval from dual;

--다음 시퀀스 조회--
select number_seq.nextval from dual; --조회할때마다 다음 시퀀스로 넘어감--

--시퀀스 삭제--
drop sequence number_seq;

--시퀀스 옵션--
create sequence number_seq1
increment by 3 --시퀀스에서는 쉼표사용X--
maxvalue 20;

select number_seq1.nextval from dual;

--시퀀스 수정-
alter sequence number_seq1 
cache 5
cycle;

cycle을 설정하여 max값이 20에 도달하면 그대로 시퀀스가 끝나는게 아니라 다시 min값으로 되돌아가도록 설정했다.

 

---

 

제약조건

테이블을 생성할때 테이블에 문제가 되는 데이터가 입력되지 않도록 컬럼별로 지정한 조건이다.

제약조건을 사용하게 되면 불필요한 데이터의 입력을 방지하고 테이블에 입력된 데이터들의 관리가 수월해질 수 있도록 한다.

 

제약조건 종류

• primary key(기본키)
• foreign kry(외래키)
• check
• unique
• not null

 

primary key(기본키)

--primary key(기본키)--
create table table_pk(
no number,
constraint no_pk1 primary key(no)
);

insert into table_pk values(); --not null제약조건때문에 에러--
insert into table_pk values(1);

--pk값을 나중에 추가할때 alter 사용--
alter table table_pk add constraint no_pk primary key(no);

primary key는 주식별자로, 테이블안에서 유일하게 구분되는 속성을 말한다. pk로 지정이 되고 나면 자동적으로 not null과 nique속성이 함께 부여되기때문에 절대로 null이 입력될 수 없고 중복또한 될 수 없다.

 

 

foreign kry(외래키)

--foregin key(자식 부모 관계)--
create table parent (
user_no number,
constraint user_nopk primary key(user_no),
name varchar2(10)
);

insert into parent values(1, '이고도');
insert into parent values(2, '박문치');
insert into parent values(3, '김뜻돌');

select * from parent;

--자식테이블 만들기--
create table son(
no number
constraint no_son_fk references parent(user_no),
join_date varchar2(10)
);

insert into son values(1, '2024-10-11');
insert into son values(2, '2024-11-01');
insert into son values(3, '2024-1-03');
insert into son values(4, '2024,05-05'); --에러!--
--부모테이블에 4가 없기때문--

외래키를 설정할때는 반드시 부모테이블이 존재해야한다. 부모테이블이 가지고있는 pk의 값을 자식테이블에서 상속받아야하기때문이다. 외래키, 즉 상속관계가 성립되면 자식테이블은 상속받은 키값에는 부모테이블이 가지고있는 값만 삽입가능하며 이외의 값이 삽입되면 에러가 발생하여 값이 입력되지 않는다

 

--부모테이블의 값 삭제하기--
delete parent where user_no=1; --자식테이블이 키를 참조하고있기때문에 삭제불가--
insert into parent values(4, '허회경'); --데이터값 삽입은 상관없음--

--자식테이블의 값 삭제하기--
delete son where no=3;
select * from son; --문제없이 삭제가능--

insert into son values(5, '2024-03-14'); --삽입은 제약이 걸림--
insert into son values(4, '2024-06-08'); --부모키에 존재하는 값만 가능--

부모테이블에게서 키를 상속받는 경우, 부모테이블은 자식테이블에게 상속해주고 있는 키는 함부로 삭제할 수 없으며, 새로운 값의 삽입만 가능하다. 자식테이블의 경우 삭제는 자유롭게 가능하나, 삽입의 경우 부모테이블이 가지고있는 값과 동일한 경우만 삽입이 가능하다.

 

상속을 받는 자식테이블과 부모테이블의 모델을 확인해보면 이렇게 관계도가 그려진다. 본 쿼리문의 경우 자식이 부모의 키를 상속받기는 하나, 상속받은 키를 식별자로 사용하지 않고있기때문에 비식별관계이다.

 

 

check

--check(삽입 값 제약)--
create table table_check(
name varchar2(10),
gender char(1)
constraint gender_check check(gender in ('M','F'))
);

insert into table_check values('한로로','M');
insert into table_check values('허회경', 'M');
insert into table_check values('김제형', 'F');
insert into table_check values('이무이', 'B'); --check제약조건때문에 삽입X--

select * from table_check;

--alter로 추가하기--
alter table add constraint gender_check check(gender in('M','F'));

check는 말 그대로 행에 값이 삽입될때 제약이 걸린 값을 한번더 확인하여 제약을 걸어둔 값과 동일한 경우에만 삽입이 되도록 한다. 그렇기때문에 맨 마지막행에서 제약조건과 다른 'B'는 삽입자체가 되지 않는다.

 

 

 

not null과 unique

--not null과 unique--
create table test_null(
no number
constraint no_uk unique,  --괄호사용X--
name varchar2(10)
constraint name_notnull not null --괄호사용X--
);

insert into test_null values(1, '전자양');
insert into test_null values(2, '심규선');
insert into test_null values(2, '하현상'); --unique키는 중복값이 들어갈수없음--
insert into test_null values('', '하현상');--unique키는 null허용--
insert into test_null values(3, ''); --not null제약조건때문에 삽입X--
insert into test_null values(4, '전자향');
insert into test_null values(5, '전자양'); --not null은 중복값허용 null만 아니면됨-

select * from test_null;

--alter--
alter table test_null modify name not null;
--null은 속성이기때문에 modify로 바꿔줌--

unique는 고유값으로, 중복을 허용하지 않는다는 것을 뜻한다. pk도 마찬가지로 중복값을 허용하지 않지만 이 둘의 가장 큰 차이점은 unique는 null값이 들어갈 수 있지만 pk는 유일한 식별자이기때문에 null이 삽입될 수 없다는 점에 있다.

 

not null은 null을 허용하지 않겠다는 의미로, 무조건 어떠한 값이든 일단 값이 들어가야한다. not null의 경우 중복값이 삽입될 수 있다.

 

 

실습

전체 제약조건을 사용하여 테이블 만들기

--사원관리 테이블 생성--

--부모 테이블--
create table dept_info(
deptno number,
constraint deptno_pk primary key(deptno),
dname varchar2(10) 
constraint dname not null,
member number
);

insert into dept_info values(10, '총무부', 4);
insert into dept_info values(20, '개발부', 10);
insert into dept_info values(30, '기획부', 5);

select * from dept_info;

--자식테이블 생성--
create table emp_info(
empno number,
constraint empno_pk primary key(empno),
ename varchar2(100) 
constraint ename unique,
job varchar2(100)
constraint job not null,
sal number,
gender varchar(5)
constraint gender_ckeck check(gender in ('여', '남')),
deptno number
constraint deptno_fk references dept_info(deptno)
);

insert into emp_info values(9812, '박소은', '개발자', 3000, '여', 20 );
insert into emp_info values(3024, '한민정', '개발자', 3000, '여', 20);
insert into emp_info values(3462, '김한주', '기획자', 2000, '남', 30);
insert into emp_info values(1032, '권원희', '총무', 5000, '여', 10);

select * from emp_info;

 

---

 

학습일기

시퀀스는 따로 숫자를 입력하지 않아도 자동적으로 연속적인 숫자가 입력되는것이 굉장히 편리한데, 가끔 현재 시퀀스 번호를 확인하려다가 curval가 아닌 nextval를 사용해서 연속적인 숫자값이 깨지는 실수를 하게되서 이 부분만 주의하면 될것같다. 제약조건의 경우는 실습을 하면서 실무에서는 테이블을 만들때 거의 기본적으로 무조건 설정하는 부분이기때문에, 어떤 걸 pk로 할지, 외래키를 설정해야한다면 부모테이블에서 어떤걸 상속받을것인지, 주식별자의 관계를 띌 것인지 아닌지 등등 구체적으로 설정해주어야한다는것을 느꼈다. 앞으로 코드 공부할때 테이블을 직접생성해야하는경우 로직구성부터 시작해서 보다 탄탄하게 모델을 구성한다음에 테이블 모델을 설계해야겠다..!