2-2. SQL 제약조건

SQL 제약조건(Constraint)

제약조건(Constraint)는 테이블의 데이터 열을 강요하는 규칙들입니다.

이것은 테이블에 입력될 데이터 형식을 제한하기위해 쓰입니다.

이것은 데이터베이스에서 데이터의 정확성과 신뢰도를 보장합니다.

재약조건은 열(Column) 단계(level)나 테이블 단계에서 할 수 있습니다.

열 측면의 제약조건은 오직 한 열에만 적용됩니다.

반면, 테이블 단계 제약조건은 전체 테이블에 적용됩니다.

SQL에서 흔히 사용되는 제약조건은 다음과 같습니다.

* NOT NULL 제약조건: 한 열이 NULL 값을 가지지 못하도록 합니다.

* DEFAULT 제약조건: 한 열에 특정한 값이 없을 경우, 대체할 기본값(Default)을 설정합니다.

* UNIQUE 제약조건: 한 열에서 각기 다른 값만 가지도록 설정합니다. (중복된 값 불가)

* 기본키(PRIMARY Key) 제약조건: 데이터베이스 테이블에서 각각의 행/레코드가 유일하게 식별 가능한 값을 가지도록 합니다.

* 외래키(FOREIGN Key) 제약조건: 또 다른 어떤 데이터베이스 테이블에서 행/레코드가 유일하게 식별 가능한 값을 가지도록 합니다.

* CHECK 제약조건: CHECK 제약조건은 특정 조건(conditions)에 해당하는 값만 들어갈 수 있도록 해줍니다.

* INDEX: 데이터베이스에서 데이터를 매우 빠르게 생성하거나 검색할 때 사용합니다. 

1) NOT NULL 제약조건:

자동적으로 하나의 열(Column)은 NULL 값들을 가질 수 있습니다.

만약 어떤 열이 NULL값을 가지지 않도록 하고 싶다면, 이 열이 NULL값을 허용하지 않도록 명시하는 것과 같은 제약조건을 정의할 필요가 있습니다.

NULL은 데이터가 없다는 의미가 아니라 알 수 없는 데이터를 표현한 것입니다.

예제:

예를 들어, 다음과 같은 SQL은 다섯 개의 열을 가지며 그 중 ID, NAME, AGE는 NULL 값을 허용하지 않겠다고 명시한 CUSTOMERS라는 새로운 테이블을 생성합니다.

CREATE TABLE CUSTOMERS (

 

       ID       INT            NOT NULL,

 

       NAME     VARCHAR (20)   NOT NULL,

 

       AGE      INT            NOT NULL,

 

       ADDRESS  CHAR (25),

 

       SALARY   DECIMAL (18, 2),

 

       PRIMARY KEY (ID)

 

);

Colored by Color Scripter

cs

이미 CUSTOMERS 테이블이 만들어져 있다면, 오라클이나 MySQL에 다음과 같은 문장을 써서 SALARY 열에 NOT NULL 제약조건을 추가할 수도 있습니다.

ALTER TABLE CUSTOMERS
 
  MODIFY SALARY DECIMAL (18, 2) NOT NULL;

2) DEFAULT 제약조건:

DEFAULT 제약조건은 INSERT INTO 문에서 구체적인 값을 입력하지 않은 열에 기본 값을 부여해 줍니다.

예제:

예를 들어, 아래의 SQL문으로 5개의 열을 가지는 새로운 CUSTOMERS 테이블을 생성합니다.

여기서 SALARY 열은 기본 값(Default 값)으로 5000.0을 갖도록 설정했는데, 이는 INSERT INTO 문 이 열에 대한 값을 증명하지 못 하는 경우에 대비하기 위한 것입니다.

CREATE TABLE CUSTOMERS(
       ID      INT            NOT NULL,
       NAME    VARCHAR(20)    NOT NULL,
       AGE     INT            NOT NULL,
       ADDRESS CHAR(25),
       SALARY  DECIMAL(18, 2) DEFAULT 5000.0,
       PRIMARY KEY(ID)
);

 

CUSTOMERS 테이블이 이미 생성되어 있는 상태에서 SALARY 열에 DEFAULT 제약조건을 추가하기 위해서는 다음과 같은 문장을 작성하면 됩니다.

ALTER TABLE CUSTOMERS
   MODIFY SALARY DECIMAL(18, 2) DEFAULT 5000.0;

DEFAULT 제약조건 제거(Drop):

DEFAULT 제약조건을 없앨 때는 아래 SQL문을 사용합니다.

ALTER TABLE CUSTOMERS
   ALTER COLUMN SALARY DROP DEFAULT;

3) UNIQUE 제약조건:

UNIQUE 제약조건은 서로 다른 두 레코드의 특정한 열이 동일한 값을 가지는 것을 방지합니다.

예를 들면, CUSTOMERS 테이블에서 둘 또는 그 이상의 사람이 동일한 나이를 가지는 것을 막고싶을 때 사용할 수도 있습니다.

예제:

다음의 SQL문으로  5개의 열로 구성된 CUSTOMERS라는 테이블을 생성합니다.

여기서, AGE 컬럼은 UNIQUE로 설정되었으므로 서로 다른 두 레코드가 같은 나이를 가질 수 없습니다.

CREATE TABLE CUSTOMERS(
       ID      INT              NOT NULL,
       NAME    VARCHAR(20)      NOT NULL,
       AGE     INT              NOT NULL UNIQUE,
       ADDRESS CHAR(25),
       SALARY  DECIMAL(18,2),
       PRIMARY KEY(ID)
);

CUSTOMERS 테이블이 이미 생성되어 있는 상태에서 AGE 열에 UNIQUE 제약조건을 추가하려면

다음과 같은 문장을 작성하면 됩니다.

ALTER TABLE CUSTOMERS
  MODIFY AGE INT NOT NULL UNIQUE;

또한 다음과 같은 구문을 사용할 수 있는데, 이 구문은 여러 열에 대한 제약조건 이름을 지정하는데 도움을 줍니다.

ALTER TABLE CUSTOMERS
  ADD CONSTRAINT myUniqueConstraint UNIQUE(AGE, SALARY);

UNIQUE 제약조건 제거:

UNIQUE 제약조건을 제거하기 위해서는 다음과 같은 SQL을 사용합니다.

ALTER TABLE CUSTOMERS
   DROP CONSTRAINT myUniqueConstraint;

MySQL을 사용하고 있다면, 다음 구문을 사용할 수 있습니다.

ALTER TABLE CUSTOMERS
   DROP INDEX myUniqueConstraint;

4) 기본 키 (PRIMARY Key):

기본 키는 데이터베이스 테이블에서 각 행 또는 레코드를 고유하게 식별하는 테이블의 필드(field)입니다. 

기본 키는 반드시 고유한 값을 가지며, 기본 키로 설정된 열은 NULL값을 가질 수 없습니다.

하나의 테이블은 오직 한 개의 기본 키만 가질 수 있는데, 기본 키는 단일 필드 외에도 여러 필드가 하나의 기본 키로 구성되기도 합니다.

다중 필드를 기본 키로 사용할 때, 이를 복합 키라고 합니다.

한 테이블에서 어떤 필드가 기본키로 정의되어 있다면, 그 필드의 값이 같은 레코드는 존재할 수 없습니다.

Note: 데이터베이스 테이블을 생성할동안 이 개념을 사용할 것입니다.

기본 키 생성:

여기에 CUSTOMERS 테이블에서 ID 속성(Attribute)을 기본 키로 정의하기 위한 구문이 있습니다.

CREATE TABLE CUSTOMERS(
       ID      INT         NOT NULL,
       NAME    VARCHAR(20) NOT NULL,
       AGE     INT         NOT NULL,
       ADDRESS CHAR(25),
       SALARY  DECIMAL(18, 2),
       PRIMARY KEY(ID)
);

CUSTOMERS 테이블이 이미 존재할 때, "ID" 컬럼에 기본 키 제약조건을 생성하기 위해서는 다음 SQL 구절을 사용합니다.

ALTER TABLE CUSTOMERS ADD PRIMARY KEY (ID);

NOTE: 기본 키를 추가하기 위해 ALTER TABLE 문장을 사용하고자 한다면,  기본 키로 설정하려는 컬럼은  반드시 NULL값을 가지지 못하도록 테이블이 처음 생성될 때 미리 명시되어 있어야 합니다.. 

여러 컬럼에 기본 키 제약조건을 정의하기 위해서는 다음 SQL 구문을 사용합니다.

 

CREATE TABLE CUSTOMERS(
       ID      INT             NOT NULL,
       NAME    VARCHAR(20)     NOT NULL,
       AGE     INT             NOT NULL,        
       ADDRESS CHAR(25),     
       SALARY  DECIMAL(18, 2),
       PRIMARY KEY (ID, NAME)
};

CUSTOMERS 테이블이 이미 존재하는 상태에서 "ID"와 "NAME" 컬럼에 기본 키 제약조건을 생석하고자 할 때, 다음 SQL 구문을 사용합니다.

ALTER TABLE CUSTOMERS 
      ADD CONSTRAINT PK_CUSTID PRIMARY KEY (ID, NAME);

기본 키 삭제:

이 구문을 사용하면 테이블에서 기본 키 제약조건을 제거할 수 있습니다.

ALTER TABLE CUSTOMERS DROP PRIMARY KEY;

5) 외래 키 (FOREIGN Key):

 외래 키는 두 테이블을 연결하기 위해 사용하는 키입니다. 외래 키는 때때로 참조 키(referencing key)라고도 부릅니다.

 외래 키는 다른 테이블의 기본 키와 일치하는 값을 가지는 하나의 컬럼 혹은 여러 컬럼의 조합입니다.

 두 테이블 사이의 관계는 테이블 중 하나의 기본 키와 두 번째 테이블의 외래 키를 연결하면서 생겨납니다.

 한 테이블이 어떤 필드에 정의된 기본 키를 가진다면 그 필드의 값이 같은 두 개의 레코드를 가질 수 없습니다. (외래 키는 중복 불가)

예제:

다음의 두 테이블의 구조에 대해 생각해봅시다.

CUSTOMERS 테이블:

CREATE TABLE CUSTOMERS(
       ID      INT         NOT NULL,
       NAME    VARCHAR(20) NOT NULL, 
       AGE     INT         NOT NULL,
       ADDRESS CHAR(25),
       SALARY  DECIMAL(18, 2),
       PRIMARY KEY(ID)
);

ORDERS 테이블:

CREATE TABLE ORDERS(
       ID          INT         NOT NULL,
       DATE        DATETIME, 
       CUSTOMER_ID INT references CUSTOMERS(ID),
       AMOUNT      double,
       PRIMARY KEY(ID)
);

 이미 ORDER 테이블이 생성되어 있고 아직 외래 키가 설정되어 있지 않은 경우, 테이블을 변경하여 외래 키를 지정하기 위해 이 구문을 사용합니다. 

ALTER TABLE ORDERS
      ADD FOREIGN KEY(Customer_ID) REFERENCES CUSTOMERS(ID);

외래 키 제약조건 삭제:

외래 키 제약조건을 삭제하기 위해서는 다음 SQL 구문을 사용하면 됩니다.

ALTER TABLE ORDERS
      DROP FOREIGN KEY;

6) CHECK 제약조건:

 CHECK 제약조건은 레코드에 입력되는 값에 조건을 걸 수 있도록 해줍니다.

이 조건이 거짓으로 판단될 경우, 그 레코드는 CHECK 제약조건에 위반되므로 테이블에 입력할 수 없습니다.

예제:

 예를 들어, 다음의 SQL은 5개의 컬럼을 가지는 새로운 CUSTOMERS 테이블을 생성합니다.

여기서, AGE 컬럼에 CHECK 제약조건을 추가하면 이 테이블은 18세 미만의 CUSTOMER는 가질 수 없게 됩니다.

CREATE TABLE CUSTOMERS(
       ID      INT         NOT NULL,
       NAME    VARCHAR(20) NOT NULL,
       AGE     INT         NOT NULL CHECK(AGE >= 18),
       ADDRESS CHAR(25),
       SALARY  DECIMAL(18, 2),
       PRIMARY KEY(ID)
);

 이미 CUSTOMERS 테이블이 생성되어 있는 상태에서 AGE 컬럼에 CHECK 제약조건을 추가하려면 다음과 유사한 문장을 작성하면 됩니다.

ALTER TABLE CUSTOMERS
      MODIFY AGE INT NOT NULL CHECK(AGE >= 18);

 또한 여러 컬럼에 제약조건을 정의하는 데 도움을 주는 다음의 구문을 사용할 수 있습니다. 

ALTER TABLE CUSTOMERS
      ADD CONSTRAINT myCheckConstraint CHECK(AGE >= 18);

CHECK 제약조건 삭제:

 CHECK 제약조건을 삭제하기 위해 다음 SQL을 사용합니다. 이 구문은 MySQL에서는 작동하지 않습니다.

ALTER TABLE CUSTOMERS
      DROP CONSTRAINT myCheckConstraint;

7) INDEX:

 INDEX는 데이터베이스로부터 매우 빠르게 데이터를 생성하고 검색하기 위해 사용합니다. 인덱스는 테이블의 단일 컬럼 또는 컬럼의 그룹을 사용함으로써 생성할 수 있습니다. 인덱스가 생성되면, 데이터를 정렬하기 전에 각 행마다 ROWID가 할당됩니다.

 적절한 인덱스의 사용은 대용량의 데이터베이스에서 좋은 성능을 발휘합니다. 하지만 인덱스를 생성할 때, 주의해야 할 것이 있는데 무슨 SQL을 사용하느냐에 따라 필드 선정이 달라질 수 있다는 것입니다.

예제:

 5개의 컬럼으로 구성된 CUSTOMERS 테이블을 생성하는 다음 SQL을 예로 들어봅시다.

CREATE TABEL CUSTOMERS(
       ID      INT         NOT NULL,
       NAME    VARCHAR(20) NOT NULL,
       AGE     INT         NOT NULL,
       ADDRESS CHAR(25),
       SALARY  DECIMAL(18, 2),
       PRIMARY KEY(ID)
);

 다음 문장으로 단일 또는 다중 컬럼 인덱스를 만들 수 있습니다.

CREATE INDEX index_name
       ON table_name(column1, column2, ...);

 다음은 특정 연령의 고객 검색을 최적화하기 위해 AGE 컬럼의 INDEX를 생성하는 SQL 구문입니다.

CREATE INDEX idx_age
       ON CUSTOMERS(AGE);

INDEX 제약조건 삭제:

 INDEX 제약조건을 삭제하고 싶을 때는 다음 SQL을 사용하면 됩니다.

ALTER TABLE CUSTOMERS
   DROP INDEX idx_age;