데이터베이스/데이터베이스 언어 SQL/ SQL을 이용한 데이터 정의
2024-06-01 16:07:44

SQL의 데이터 정의 기능

 SQL의 데이터 정의 기능은 테이블 생성, 생성된 테이블 구조의 변경, 테이블 제거로 분류할 수 있다.

 

 

SQL의 데이터 정의 기능

 

 

테이블의 생성

 새로운 테이블을 생성하려면 먼저 테이블의 이름과 테이블을 구성하는 속성의 이름을 의미있게 정해야 한다. 그런 다음 각 속성의 특성에 맞게 데이터 타입을 결정한다.

 

 다음 CREATE TABLE 문의 기본 형식에서 ❶은 테이블을 구성하는 각 속성의 이름과 데이터 타입, 기본적인 제약 사항을 정의한다. ❷는 기본키로 테이블에 하나만 존재할 수 있다. ❸은 대체키로 테이블에 여러 개 존재할 수 있다. ❹는 외래키로 테이블에 여러 개 존재할 수 있다. ❺는 데이터 무결성을 위한 제약조건으로 테이블에 여러 개 존재할 수 있다. 그리고 []로 표시한 항목은 생략이 가능하다.

 

 

 

 모든 SQL 문은 세미콜론(;)으로 문장 끝을 표시한다. 그리고 SQL 문에 사용되는 CREATE TABLE, NOT NULL과 같은 키워드는 대소문자를 구분하지 않는다. 실제로 DB를 구축할 때는 보통 테이블 이름과 속성 이름을 영어로 작성하지만 이 장에서는 좀 더 직관적으로 이해할 수 있도록 테이블과 속성을 한글 이름으로 작성한 예를 통해 설명한다.

 

속성의 정의

 CREATE TABLE 문으로 생성되는 테이블을 구성하는 속성은 기본적으로 널 값이 허용된다. 만약 널 값을 허용하지 않으려면 속성을 정의할 때 속성의 이름과 데이터 타입 다음에 NOT NULL 키워드를 포함해야 한다. 특히, 기본키를 구성하는 모든 속성은 널 값을 가질 수 없도록 반드시 NOT NULL 키워드를 표기한다.

 

속성의 데이터 타입

 

 

 기본키를 구성하는 속성이 아니더라도 값을 꼭 입력해야 된다고 판단되는 속성은 NOT NULL 키워드를 표기한다.

 

예) 고객아이디 VARCHAR(20) NOT NULL

-> 고객 테이블의 고객아이디 속성을 길이가 최대 20인 가변 길이의 문자열 데이터로 구성하고, 널 값을 허용하지 않음.

 

 

 속성에 기본 값을 지정해두지 않으면 사용자가 속성에 값을 입력하지 않았을 때 해당 속성에 널 값이 기본으로 저장된다. 하지만 DEFAULT 키워드를 사용해 기본 값을 명확히 지정해두면 이 기본 값이 저장된다.

 

예) 적립금 INT DEFAULT 0

-> 고객 테이블의 적립금 속성을 정수 데이터로 구성하고, 사용자가 적립금을 입력하지 않으면 0이 기본 저장되도록 함.

 

 DEFAULT 키워드로 기본 값을 지정할 때 숫자 데이터는 그대로 표현하고, 문자열이나 날짜 데이터는 작은따옴표로 묶어주어야 한다. 작은따옴표로 묶인 문자열은 대소문자를 구분한다.

 

키의 정의

 CREATE TABLE 문으로 테이블을 정의할 때는 기본키, 대체키, 외래키를 지정할 수 있다.

 

 기본키는 PRIMARY KEY 키워드를 사용해 지정한다. 기본키가 없어도 테이블을 정의할 수 있지만 각 튜플을 식별할 수 있는 기본키는 가능한 한 선택하는 것이 좋다. 모든 테이블에서 기본키는 반드시 하나만 지정할 수 있고, 여러 개의 속성으로 구성할 수도 있다.

 

예) PRIMARY KEY(고객아이디)

-> 고객아이디 속성을 기본키로 지정함

 

예) PRIMARY KEY(주문고객, 주문제품)

-> 주문고객, 주문제품 속성을 기본키로 지정함

 

 

 대체키는 UNIQUE 키워드를 사용해 지정한다. 대체키는 기본키와 같이 각 튜플을 유일하게 식별하는 특성이 있다. 대체키로 지정된 속성의 값은 테이블에서 중복되면 안 되고 유일성을 가져야 한다. 하지만 기본키로 지정된 속성과 달리 널 값을 가질 수 있다. 또한 대체키는 한 테이블에서 여러 개 지정할 수 있다.

 

예) UNIQUE(고객이름)

-> 고객이름 속성을 대체키로 지정함

 

 

 외래키는 FOREIGN KEY 키워드를 사용해 지정한다. 외래키는 지정할 때는 출처를 분명히 밝혀야 한다. 즉, 외래키가 어떤 테이블의 무슨 속성을 참조하는지 REFERENCES 키워드 다음에 명확히 제시해야 한다. 이렇게 하면 참조되는 테이블에서 튜플을 함부로 삭제하거나 변경하지 못한다. 그리고 참조되는 테이블에서 튜플을 삭제하거나 변경할 때 처리하는 방법을 다양하게 선택할 수 있다.

 

 외래키를 포함한 테이블을 정의하는 CREATE TABLE 문을 작성할 때 이 중 하나를 지정하면 되는데, 별도로 지정하지 않으면 ON DELETE NO ACTION이 기본으로 선택된다.

 

❶ ON DELETE NO ACTION : 튜플을 삭제하지 못하게 한다.

❷ ON DELETE CASCADE : 관련 튜플을 함께 삭제한다.

❸ ON DELETE SET NULL : 관련 튜플의 외래키 값을 NULL로 변경한다.

❹ ON DELETE SET DEFAULT : 관련 튜플의 외래키 값을 미리 지정한 기본 값으로 변경한다.

 

 참조되는 테이블의 튜플이 변경될 때도 다음 네 가지 중 한 가지 방법으로 처리하도록 선택할 수 있다. 외래키를 포함한 테이블을 정의하는 CREATE TABLE 문을 작성할 때 네 가지 방법 중 하나를 지정하면 되는데, 지정하지 않으면 ON UPDATE NO ACTION이 기본으로 선택된다.

 

❶ ON UPDATE NO ACTION : 튜플을 변경하지 못하도록 한다.

❷ ON UPDATE CASCADE : 관련 튜플에서 외래키 값을 함께 변경한다.

❸ ON UPDATE SET NULL : 관련 튜플의 외래키 값을 NULL로 변경한다.

❹ ON UPDATE SET DEFAULT : 관련 튜플의 외래키 값을 미리 지정한 기본 값으로 변경한다.

 

예) FOREIGN KEY(소속부서)    REFERENCES    부서(부서번호)

      ON DELETE CASCADE ON UPDATE CASCADE

-> 소속부서 속성이 부서 테이블의 부서번호 속성을 참조하는 외래키이고 ON DELETE CASCADE와 ON UPDATE CASCADE 방법으로 처리함

 

예) FOREIGN KEY(소속부서)    REFERENCES    부서(부서번호)

-> 소속부서 속성이 부서 테이블의 부서번호 속성을 참조하는 외래키이고 ON DELETE NO ACTION과 ON UPDATE NO ACTION 처리 방법이 자동으로 선택됨

 

 

데이터 무결성 제약조건의 정의

 CREATE TABLE 문으로 테이블을 정의할 때 CHECK 키워드를 사용해 특정 속성에 대한 제약조건을 지정할 수 있다. 그러면 테이블에는 CHECK 키워드로 지정한 제약조건을 만족하는 튜플만 존재하게 된다. 테이블에 새로운 튜플을 삽입하거나 기존 튜플을 수정할 때도 이 제약 조건을 반드시 지켜야 한다.

 

예) CHECK(재고량 >= 0 AND 재고량 <= 10000)

-> 모든 제품의 재고량은 항상 0개 이상, 10,000개 이하로 유지되어야 한다는 데이터 무결성 제약조건

 

CHECK 키워드를 사용해 지정한 제약조건에 CONSTRAINTS 키워드와 함께 고유의 이름을 부여할 수도 있다. 제약조건을 여러 개 지정할 때 고유의 이름을 부여하면 테이블이 생성된 이후에 제약조건을 수정하거나 제거할 때 식별하기가 쉽다.

 

예) CONSTRAINTS CHK_CPY CHECK(제조업체 = '한빛제과')

-> 모든 제품의 제조업체로 한빛제과만 허용된다는 데이터 무결성 제약조건에 CHK_CPY이라는 고유의 이름을 부여함. 다른 테이블에는 CHK_CPY이라는 이름으로 정의된 제약조건이 있으면 안 됨

 

 

테이블 생성의 예

 

 예제 1

 고객 테이블은 고객아이디, 고객이름, 나이, 등급, 직업, 적립금 속성으로 구성되고, 고객아이디 속성이 기본키다. 고객이름과 등급 속성은 값을 반드시 입력해야 하고, 적립금 속성은 값을 입력하지 않으면 0이 기본으로 입력되도록 고객 테이블을 생성해보자.

#작성 코드
CREATE TABLE 고객 (
    고객아이디 VARCHAR(20) NOT NULL,
    고객이름 VARCHAR(20) NOT NULL,
    나이 INT,
    등급 VARCHAR(20) NOT NULL,
    직업 VARCHAR(20),
    적립금 INT,
    PRIMARY KEY(고객아이디),
);
#정답코드
CREATE TABLE 고객 (
    고객아이디 VARCHAR(20) NOT NULL,
    고객이름 VARCHAR(10) NOT NULL,
    나이 INT,
    등급 VARCHAR(10) NOT NULL,
    직업 VARCHAR(20),
    적립금 INT DEFAULT 0,
    PRIMARY KEY(고객아이디)
);

 

 DEFAULT 키워드를 사용해 기본 값을 지정할 수 있음을 기억하자.

 

 

예제 2

 제품 테이블은 제품번호, 제품명, 재고량, 단가, 제조업체 속성으로 구성되고, 제품번호 속성이 기본키다. 재고량이 항상 0개 이상 10,000개 이하를 유지하도록 제품 테이블을 생성해보자.

#작성코드
CREATE TABLE 제품 (
    제품번호 INT NOT NULL,
    제품명 VARCHAR(20),
    재고량 INT,
    단가 INT,
    제조업체 VARCHAR(20),
    PRIMARY KEY(제품번호)
);
#정답코드
CREATE TABLE 제품 (
    제품번호 CHAR(3) NOT NULL,
    제품명 VARCHAR(20),
    재고량 INT,
    단가 INT,
    제조업체 VARCHAR(20),
    PRIMARY KEY(제품번호),
    CHECK (재고량 >= 0 AND 재고량 <= 10000)
);

 

 CHECK 키워드를 사용해 특정 속성에 대한 제약조건을 지정할 수 있음을 기억하자.

 

 

예제 3

 주문 테이블은 주문번호, 주문고객, 주문제품, 수량, 배송지, 주문일자 속성으로 구성되고, 주문번호 속성이 기본키다. 주문고객 속성이 고객 테이블의 고객아이디 속성을 참조하는 외래키이고, 주문제품 속성이 제품 테이블의 제품번호 속성을 참조하는 외래키가 되도록 주문 테이블을 생성해보자.

#작성코드
CREATE TABLE 주문 (
    주문번호 VARCHAR(20) NOT NULL,
    주문고객 VARCHAR(20),
    주문제품 VARCHAR(10),
    수량 INT,
    배송지 VARCHAR(20),
    주문일자 VARCHAR(20),
    PRIMARY KEY(주문번호),
    FORIGEN KEY(주문고객)
);
#정답코드
CREATE TABLE 주문 (
    주문번호 CHAR(3) NOT NULL,
    주문고객 VARCHAR(20),
    주문제품 CHAR(3),
    수량 INT,
    배송지 VARCHAR(30),
    주문일자 DATE,
    PRIMARY KEY(주문번호),
    FORIGEN KEY(주문고객) REFERENCES 고객(고객아이디),
    FORIGEN KEY(주문제품) REFERENCES 제품(제품번호)
);

 

 날짜의 경우 데이터 타입으로 DATE 를 사용한다. 참조한다는 뜻을 가진 REFERENCES 키워드를 기억하자.

 

 

예제 4

 배송업체 테이블은 업체번호, 업체명, 주소, 전화번호 속성으로 구성되고 업체번호 속성이 기본키다. 배송업체 테이블을 생성해보자.

#작성코드
CREATE TABLE 배송업체 (
    업체번호 CHAR(3) NOT NULL,
    업체명 VARCHAR(20),
    주소 VARCHAR(30),
    전화번호 VARCHAR(20),
    PRIMARY KEY(업체번호)
);
#정답코드
CREATE TABLE 배송업체 (
    업체번호 CHAR(3) NOT NULL,
    업체명 VARCHAR(20),
    주소 VARCHAR(100),
    전화번호 VARCHAR(20),
    PRIMARY KEY(업체번호)
);

 

 

테이블의 변경

 테이블은 ALTER TABLE 문으로 변경할 수 있다. ALTER TABLE 문을 이용해 새로운 속성 추가, 기존 속성 삭제, 새로운 제약조건 추가, 기존 제약조건 삭제 등이 가능하다.

 

새로운 속성의 추가

 테이블에 새로운 속성을 추가하는 ALTER TABLE 문의 기본 형식은 다음과 같다.

ALTER TABLE 테이블_이름
    ADD 속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본_값];

 

예제 5

 예제 1에서 생성한 고객 테이블에 가입날짜 속성을 추가해보자.

#작성코드
ALTER TABLE 고객
    ADD 가입날짜 DATE [NOT NULL];
#정답코드
ALTER TABLE 고객 ADD 가입날짜 DATE;

 

 [NOT NULL] 과 [DEFAULT 기본_값] 은 주어진 조건이 없으면 사용하지 않아도 된다.

 

 

기존 속성의 삭제

 테이블의 기존 속성을 삭제하는 ALTER TABKE 문의 기본 형식은 다음과 같다.

ALTER TABLE 테이블_이름 DROP COLUMN 속성_이름;

 

 만약 삭제할 속성과 관련된 제약조건이 존재하거나 이 속성을 참조하는 다른 속성이 존재하는 경우에는 속성을 삭제할 수 없다. 관련된 제약조건이나 참조하는 다른 속성을 먼저 삭제한 후 해당 속성을 삭제해야 한다.

 

예제 6

 예제 1에서 생성한 고객 테이블의 가입날짜 속성을 삭제해보자.

#작성코드 / 정답코드와 동일
ALTER TABLE 고객 DROP COLUMN 가입날짜 ;

 

 

새로운 제약조건의 추가

 테이블에 새로운 제약조건을 추가하는 ALTER TABLE 문의 기본 형식은 다음과 같다.

ALTER TABLE 테이블_이름 ADD CONSTRAINT 제약조건_이름 제약조건_내용 ;


예제 7

 고객 테이블에 20세 이상의 고객만 가입할 수 있다는 데이터 무결성 제약조건을 추가해보자.

#작성코드
ALTER TABLE 고객 ADD CONSTRAINT AGE_LIMIT AGE >= 20 ;
#정답코드
ALTER TABLE 고객 ADD CONSTRAINT CHK_AGE CHECK(나이 >= 20) ;

 

 제약조건을 지정할 때 CHECK 키워드를 사용한다는 것을 다시금 기억하자.

 

 

기존 제약조건의 삭제

 테이블의 기존 제약조건을 삭제하는 ALTER TABLE 문의 기본 형식은 다음과 같다.

ALTER TABLE 테이블_이름 DROP CONSTRAINT 제약조건_이름 ;

 

예제 8

 예제 7에서 추가한 고객 테이블에 20세 이상의 고객만 가입할 수 있다는 데이터 무결성 제약조건을 삭제해보자.

#작성코드 / 정답코드와 동일
ALTER TABLE 고객 DROP CONSTRAINT CHK_AGE ;

 

 

테이블의 삭제

 CREATE TABLE 문으로 생성한 테이블은 DROP TABLE 명령어로 삭제할 수 있다. 테이블을 삭제하는 DROP TABLE 문의 기본 형식은 다음과 같다.

DROP TABLE 테이블_이름 ;

 

 삭제할 테이블을 참조하는 테이블이 있다면 삭제가 수행되지 않는다. 따라서 삭제하고자 하는 테이블을 참조하는 외래키 제약조건을 먼저 삭제해야 한다.

 

예제 9

 배송업체 테이블을 삭제해보자.

#작성코드 / 정답코드와 동일
DROP TABLE 배송업체 ;

 

 예제 9번에서 배송업체 테이블을 참조하는 테이블이 없기에 문제없이 삭제된다. 하지만 고객 테이블은 주문 테이블이 참조하고 있어 삭제가 수행되지 않는다.

 

테이블 생성 CREATE TABLE

 

테이블 변경 ALTER TABLE

 

테이블 제거 DROP TABLE

 

널 값을 가질 수 없다 NOT NULL

 

기본 값 지정 DEFAULT

 

기본키 지정 PRIMARY KEY

 

외래키 지정 FOREIGN KEY

 

속성 참조 REFERENCES

 

제약조건 지정 CHECK

 

제약조건 고유의 이름 부여 CONSTRAINTS

'데이터베이스 > 데이터베이스 언어 SQL' 카테고리의 다른 글

삽입 SQL  (0) 2024.06.06
뷰  (0) 2024.06.06
SQL을 이용한 데이터 조작  (0) 2024.06.02
SQL의 소개  (0) 2024.06.01

티스토리툴바