[Oracle] Query문을 작성해 보자! (1) - CONSTRAINT(제약조건) and ALTER(테이블 수정)

 

 

기본적인 SQL문을 작성해 보려고 한다. 기본이라 하면 데이터 추가, 업데이트, 삭제, 조회, 그룹별로 정리등? 이 있는거 같다. 대부분의 쿼리문이 기초적인 쿼리문을 바탕으로 작성되기 때문에 일단 먼저 기본 쿼리문부터 정리하고 추후에 어렵고 복잡한 쿼리문이 필요하면 그때마다 포스팅을 해야겠다.

 

[Query문 작성 기본규칙]

" 키워드는 대문자, 테이블과 컬럼명은 소문자이다."이게 딱 정해진건 아닌데, 지금은 많은 DB에서 색으로 키워드와 컬럼등을 구분해 준다. 하지만 옜날에는 그렇지가 않았기 때문에 가독성을 위해 대문자로 작성하던 것이 지금까지도 이어져 규칙처럼 쓰여지는거 같다. 소문자로해도 쿼리문이 잘 전달된다. 일종의 규칙인데 너무 얽매이지 말자. 트렌드라는게 언제든지 바뀔 수 있다고 생각하니깐.. 그러나 팀으로 일할때는 속해있는 조직 규칙을 따르는것이 좋다고 생각한다.

 

1. CREATE TABLE (테이블 생성)

 

데이터를 테이블에 추가하는 기능이다. "이 데이터를 테이블에 넣어주세요" 하고 말하는거지

 

★ 테이블 이름을 보면 mogacco라고 되어 있는데, 지금은 mogakco로 변경했다. 한글로 "모각코" 인데 무슨 뜻이냐하면 내가 매주 토요일마다 나가는 개발자 모임 이름이 모각코이다.  "(모)여서 (각)자 (코)딩" 의 줄임말이고,  모임에서 DB공부를 하고 있다가 테이블 생성을 해야했는데 딱히 떠오르는 이름이 없어, 모임 이름인 mogakco로 만들었다. 설명을 해야할것 같아서... 암튼 그렇다

 

 

[ 오라클에서 테이블 생성 ] 

예시1

예시2

=>  테이블 생성시 제약조건을 추가해서 만들 수 있다. CONSTRAINT는 제약조건을 의미하고 제약조건에 이름을 정해서 만들고 싶을 때 사용한다. 

 

CONSTRAINT PK_mogakco PRIMARY KEY(name)

= > 기본키를 만드는 쿼리문인데 앞에 보면 CONSTRAINT라고 쓰여있다. 기본키의 이름을 명시하기 위해 사용한다.

 

 

[ H2에서 테이블 생성 ]

 

 

=> 사실 처음 글을 쓸때는 H2 DB와 오라클의 테이블 생성 쿼리문이 다른 줄 알았다. H2에서 테이블을 만들 때 CONSTRAINT 를 적고 만들려고 하니 계속 에러가 뜨길래 CONSTRAINT를 안치고 만들어 봤는데 웬걸 테이블이 생성되었다. 그래서 두개가 다르구나... 하고 생각했었는데 계속 자료를 찾다보니 그냥 내가 잘 못만든거였다. 방식에 차이가 없는 것 같다.

 

 

1-1.  CONSTRAINT (제약조건) 

 

관계형 데이터베이스에서는 여러 테이블이 엮여있기 때문에 서로 겹치는 데이터가 들어가고 하면 안된다. 그래서 각각의 테이블에 제약조건을 추가하여 테이블에 맞지 않는 데이터들이 들어오는것을 막아야 한다. 즉, 제약 조건이란 데이터의 무결성을 지키기 위해 만들어진 제한된 조건을 의미한다. 쉽게 말해 테이블이나 속성에 부적절한 데이터가 들어오는 것을 사전에 차단하도록 정해 놓은 것이라 생각하면 된다.

 

그러면 여기서 왜 제약조건이 필요한지 기본키를 가지고 설명해본다.

 

사실 따로 설명할 필요없이 각각의 테이블에서 키본키는 필수다!

 

PK(기본키)를 통해 다른 테이블을 Join이 가능해질 뿐아니라 일관성 없는 데이터가 반복적으로 쌓일 수 있다. (테이블에는 중복된 데이터가 쌓여선 안된다.) 기본키가 존재함으로서 그냥 데이터베이스가 아니라 관계형 데이터베이스라고 말할 수 있는 것이다. 기본키를 명시해줌으로서 데이터 자체의 모호함을 방지하고, 한 테이블에서 다른 테이블을 참조하는 것이 용이해진다. 테이블에 고유식별id가 없다면 어떤식으로 참조해야할지...모르겠다.

 

이렇듯 제약조건은 관계형  데이터베이스에서 중요한 요소이다. 그러면 제약조건의 종류에 대해서 몇가지 알아보자.

 

   ① PRIMARY KEY :  기본키 ->  고유식별ID라고 부르며, 데이터가 중복되지 않게 해준다

   ② FOREIGN KEY : 외래키 ->  두 테이블을 연결해주는 징검다리, 

   ③ NOT NULL : 낫 널 ->  이름그대로 null값이 들어갈 수 없다. 

   ④ DEFAULT : 기본값->  아무런 값도 들어가지 않았을때 기본값으로 설정한 값이 들어간다.

   ⑤ CHECK  : 체크 제약조건 ->  체크값으로 지정한 값만 들어갈 수 있다.

 

---

1-2. CONSTRAINT (제약조건) 

 

① 기본키 :  PK_mogakco가 기본키 이름이고, 뒤에 (name)이 기본키가 되는 컬럼명이다. 기본키를 만들게 되면  name컬럼의 값들은 고유식별ID가 됨으로써 테이블안에 다른 행들과  확실하게 구분되어진다. 

 

CONSTRAINT PK_mogakco PRIMARY KEY (name)

= PRIMARY KEY(name) : 제약조건명을 명시 안할거면 앞에 CONSTRAINT를 안붙여도 된다.

 

" CONSTRAINT [기본키 이름] PRIMARY KEY ([컬럼명]) "

 

 

② 외래키: FK_address가 외래키 이름이고, FOREIGN KEY 뒤에 있는 (name)이 외래키가 되는 컬럼명이다. 그리고REFERENCES 뒤에 있는 mogakco(name)은 참조하는 테이블의 컬럼명이다.  외래키를 만들게 되면 참조하는 mogakco 테이블은 부모테이블이 되고, address 테이블은 자식테이블이 되어 두 테이블간에 연결다리가 생긴다. 그래서 address 테이블에 데이터를 추가 할때는 부모테이블인  mogakco 테이블의 name값이 반드시 들어가야한다. (일치해야한다.)

 

CONSTRAINT FK_address FOREIGN KEY(name) REFERENCES  mogakco(name)

 

" CONSTRAINT [외래키 이름] FOREIGN KEY ([컬럼명]) REFERENCES [참조테이블(컬럼명)] "

 

[ 외래키 추가 정리 ]

1. 두 테이블의 컬럼명이 같을 경우 : 제약조건과 참조 테이블에 컬럼명을 명시 안해줘도 된다.

CREATE TABLE maddress(
name VARCHAR(20) FOREIGN KEY REFERENCES mogakco
);

CREATE TABLE maddress(
name VARCHAR(20) 
postcode NUMBER(20) 

CONSTRINT FK_maddress FOREIGN KEY REFERENCES mogakco
);
=> 위에 컬러명 바로 뒤에 붙여도 되고, 밑에 따로 명시해줘도 된다.

2. 두 테이블이 외래키로 묶여있으면 부모테이블에 있는 컬럼의 값은 삭제할 수가 없다. 삭제를 하려면 연결되어 있는 자식테이블 외래키를 전부 지우고 삭제를 해야한다. 그래서 부모테이블과 자식테이블의 값을 함께 지워주는 옵션 제공 쿼리문이 있다.

CREATE  TABLE maddress(
name VARCHAR(20)
postcode NUMBER(20)

CONSTRINT  FK_maddres s FOREIGN KEY  REFERENCES  mogakco
ON DELETE CASCADE
);

CREATE TABLE maddress(
name VARCHAR(20)
postcode NUMBER(20)

CONSTRINT  FK_maddress  FOREIGN  KEY  REFERENCES  mogakco
ON DELETE SET NULL
);

① ON DELETE CASCADE : 부모 테이블을 삭제할 수 있으며, 자식 테이블의 데이터도 같이 삭제된다.
② ON DELETE SET NULL : 부모 테이블을 삭제할 수 있으며, 자식 테이블의 값은 NULL이 된다.

 

③ NOT NULL : NULL값은 들어갈 수 없다. 반드시 무슨  값이든 들어가야 한다. 즉, null값은 허용하지 않는다. 보통 기본키는 NOT NULL을 명시해준다.

 

④ DEFAULT : 데이터에 값이 들어가지 않거나, null값으로 들어갈때 자동으로 기본값으로 지정한 값이 들어간다. 보기에는 '이름' 이라고 지정해놨기 때문에 아무런 값도 들어가지 않는다면 '이름'으로 저장된다.

 

name VARCHAR(20)  NOT NULL,

name VARCHAR(20) DEFAULT '이름',

 

⑤ CHECK : 체크 제약조건이다. 내가 지정한 값만 들어갈 수 있고, 그 외에 값이 들어가게 되면 에러가 뜬다. 쉽게 설명하면 성별에는 남자,여자 밖에 없기에  다른 값들은 들어갈 수가 없다. 그래서 CHECK값을 주어 남자,여자 말고 다른 값들이 들어가는 것을 방지하고자 하는 것이다.

 

CONSTRAINT CHK_mogakco CHECK(gender IN ('남자','여자'))

 

" CONSTRAINT [체크키 이름] CHECK ([컬럼명] IN('체크값1', '체크값2')) " 

 

* CHECK 값은 문자 뿐 아니라 숫자도 가능하다.

ex) age IN (age >= 1 AND age <=10 ) : 1~10까지의 숫자만 들어가도록 범위를 제약조건을 걸었다.

 

---

1-2. CONSTRAINT (제약조건) 추가, 삭제 

 

테이블을 만들고 나서도 제약조건을 추가할 수 있다. 혹시나 작업을 하는 도중에  제약조건이 필요하다고 생각되는 경우, DB구조가 바뀌거나 누락 되었을 경우에 쓸 수 있는 쿼리문이다. 만들어진 테이블 수정할때는 "ALTER" 을 쓴다.

 

* ALTER문은 데이터베이스에서 기존의 테이블을 유지하면서 원하는 부분만 수정할 수 있는 명령문이다.

 

ALTER TABLE mogakco ADD CONSTRAINT PK_mogacko PRIMARY KEY (name);

 

[ 제약조건 추가 ]

1. 기본키 : ALTER TABLE [테이블] ADD CONSTRAINT [기본키 이름] PRIMARY KEY ([컬럼명])
= ALTER TABLE [테이블] ADD PRIMARY KEY([컬럼명])

2. 외래키 : ALTER TABLE [테이블] ADD CONSTRAINT [외래키 이름] FOREIGN KEY ([컬럼명]) REFERENCES [참조 테이블] ([컬럼명]);

3. 체크키 : ALTER TABLE [테이블] ADD CONSTRAINT [체크키 이름] CHECK ([컬럼명] IN ('컬럼값1','컬럼값2'));

[ 제약조건 삭제 ]

1.삭제 : ALTER  TABLE  [테이블명] DROP  CONSTRAINT [제약조건명]
2. 테이블 + 제약조건 함께 삭제 : DROP TABLE [테이블] CASCADE CONSTRAINTS;

 

1-3. 그 외 ALTER 쿼리문 

 

1. 컬럼 추가 (ADD)

 

ALTER TABLE maddress ADD COLUMN datail VARCHAR(50);

 

" ALTER TABLE [테이블] ADD COLUMN [추가 컬럼명] [데이터 타입] "

---

 

2. 컬럼 타입 변경 (MODIFY)

 

ALTER TABLE maddress MODIFY COLUMN datail NUMBER(20);

 

" ALTER TABLE [테이블] MODIFY COLUMN [컬럼명] [새로운 데이터 타입] "

---

 

3. 컬럼 이름 변경 (REMAME)

 

ALTER TABLE maddress RENAME COLUMN name TO username;

 

" ALTER TABLE [테이블] RENAME COLUMN [변경할 컬럼] TO [새로운 컬럼 이름] "

---

 

4. 컬럼명 + 데이터 터입 변경 (CHANGE)

 

ALTER TABLE maddress CHANGE COLUMN name username VARCHAR(30);

 

" ALTER TABLE [테이블] CHANGE COLUMN [변경할 컬럼] [새로운 컬럼이름] [새로운 데이터 타입] "

---

 

5. 컬럼 삭제 (DROP)

 

ALTER TABLE maddress DROP COLUMN username;

 

" ALTER TABLE [테이블] DROP COLUMN [삭제할 컬럼] "

---

 

6. NOT NULL / DEFAULT 설정 (MODIFY)

 

ALTER TABLE maddress MODIFY username NOT NULL;

ALTER TABLE maddress MODIFY username VARCHER DEFAULT '기본값';

 

" ALTER TABLE [테이블] MODIFY [컬럼명]  NOT NULL;"

" ALTER TABLE [테이블] MODIFY [컬럼명] [데이터 타입] DEFAULT [기본값];"

---

7. 컬럼 순서 변경

 

데이터베이스 작업을 하다보면 새로 생긴 컬럼을 추가해야 하는 경우가 종종 생긴다. ADD COLUMN을 써서 컬럼을 추가하는건 알겠는데 위치가 내가 원하는 위치가 아니다. 새로운 컬럼을 추가하게되면 컬럼의 순서 끝에 추가되기 때문에 데이터를 조회할 시, 한눈에 알아보기가 조금 어려울 수 있다. 그래서 컬럼을 내가 원하는 위치에 넣을 수 있는 방법에 대하여 적어보려 한다.

 

[기존 테이블]

 

테이블을 보면 뒤늦게 추가된 REGION 컬럼이 당당하게 NULL값으로 맨 끝에 위치한 것을 볼 수있다. 근데 내가 원하는건 저 위치가 아니라 AGE 컬럼 바로 뒤에 오게 하고싶다. 이 때 쓸수 있는 방법이 2가지가 있는데 오라클 12c 이상에서 적용 가능한 방법과 11g 이하에서 적용 가능한 방법이 있다. (아마 12c 이상에서는 둘 다 적용 가능할것으로 본다.)

 

 

 

7-1. 컬럼 순서 변경(오라클 12c 이상)

 

                                                    
-- 우선 뒤로 보낼 컬럼을 숨김 처리한다. 
ALTER TABLE 테이블명 MODIFY COL_C INVISIBLE;
ALTER TABLE 테이블명 MODIFY COL_D INVISIBLE;

-- 추가된 컬럼을 원하는 자리에 위치시키고 다시 순서대로 보이도록 한다.
ALTER TABLE 테이블명 MODIFY COL_C VISIBLE;
ALTER TABLE 테이블명 MODIFY COL_D VISIBLE;

 

ⓛ REGION 컬럼 앞에 있는 REGDATE 날짜 컬럼을 먼저 숨긴다.

 

 

② 그 후 다시 REGDATE 컬럼을 보이게 하면 REGION컬럼이 AGE컬럼 뒤에 위치하는 것을 볼 수있다.

 

 

(사실 내가 쓰는 오라클 버전이 11g 이하 버전인지 INVISIBLE 쿼리문 실행이 안되었다. 그래서 임의로 값들의 위치를 정해 SELECT한 결과들인데... 아마도 이런 느낌으로 컬럼의 순서를 조정할 수 있지 않나 싶다.) 

 

 

7-2. 컬럼 순서 변경(오라클 11g 이하)

 

 

ⓛ 먼저 REGION 컬럼을 추가하자.(그림은 위와 똑같다)

 

 

② 새로운 테이블을 만들면서 컬럼의 위치를 수동으로 정해주자.

 

                    
-- 새로운 테이블 생성 -> 기존 테이블에서 컬럼 위치를 조정
 
CREATE TABLE MEMBER4 AS
  SELECT no,id, pw, name, nickname, age, region, regdate
  FROM MEMBER3;

 

③ 기존 테이블은 지우고, 새로운 테이블의 이름을 다시 기존 테이블 이름으로 변경

 

-- 기존 테이블은 지우고
 
DROP TABLE MEMBER3;
 
-- 새로만든 테이블의 이름을 기존 테이블명으로 변경
 
ALTER TABLE MEMBER4 RENAME TO MEMBER3;

 

이렇게 하면 위에있는 최종 결과물과 똑같은 테이블을 만들 수 있다.  새로운 테이블을 만들어서 하는 방법이라 손이 2번 가는 것 같지만 역시나 존시나 방법은 다있다.

 

※ 한가지 문제점을 발견했다. 이렇게 새로운 테이블을 생성시 기존의 기본키와 시퀀스값들이 다 사라진채 테이블이 생성된다... 그래서 추가로 설정을 해줘야한다. 그냥 제일 최신버전으로 쓰는게 제일 좋은 것 같다 .

 

 

 원래는 기본 쿼리문을 정리하려고 했늗데 제약조건을 설명하다가 글이 길어진거 같다. 그래도 제약조건은 관계형 데이터베이스에서 중요한 역할을 하기 때문에 이번 포스팅을 통해 공부가 되었다고 생각한다. 자 그럼 다음 글 부터는 정말 기본 쿼리문을 작성해보자. 

 

 

[도움받은 사이트]

https://studyingazae.tistory.com/12

https://eatnows.tistory.com/49

https://hajoung56.tistory.com/49

https://mine-it-record.tistory.com/39