제약조건, REFERENCES, 기타 제약조건

제약조건을 하기 위해서는 아래의 3가지를 알아야한다.

후보키	유일하게 식별할 수 있는 가능성을 가진 컬럼 즉 기본키로 지정할 수 있는 가능성을 가진 키 (예 : 순번, 회원번호 등등등...)
기본키 (PK:PRIMARY KEY)	후보키 중에서 결정을 했다면 그 상황에서 기준이 되는 것이 기본키  후보키 -> 기본키 즉 고유한 값이며 NULL과 중복 값을 입력할 수 없음 테이블의 여러 컬럼 중 단 1개만 지정 가능함.
대체키	기본키가 문제가 생겼을대 대처할 수 있는 키 후보키 중에서 기본키를 지정하고 남은 키
유니크키	절대로 중복이 될 수 없게 만드는 키  NULL은 허용하지만 중복은 허용하지 않음 하나의 테이블에서 여러개의 컬럼에 유니크키를 지정할 수 있다.
외래키 (FK : FORIEGN KEY)	다른 테이블의 PK를 사용할 수 있고 중복이 가능한 키 일반적으로 다른 테이블과 관계를 연결할 때 사용합니다. (관계형) NULL값을 허용합니다. 다만, 권장하지는 않습니다.(NOT NULL 이라는것도 있다는 점) 외래키에 입력되는 값은 연결되어 있는 기본키의 값을 검증한다!

예시 테이블

---

기본키의 역할 

예를들어 회원만 게시글을 작성할 수 있는 사이트가있는데 누가작성했는지 모릅니다.

그럴경우에 다시 다 식별번호를 가져와서 만들기는 너무 비효율적이겠죠. 

기존에있는 테이블에서 하나만 가지고와서 식별하면되겠죠?

---

🤷‍♂️ 지난 TABLE에 이어서

CREATE TABLE TBL_CAR(

CAR_NUMBER NUMBER(10),

CAR_NAME VARCHAR2(10),

CAR_BRAND VARCHAR2(10),

CAR_DATE DATE,

CAR_COLOR VARCHAR2(10),

CAR_PRICE NUMBER

);

 

SELECT * FROM TBL_CAR;

 

 

🤷‍♀️ 제약조건 추가

ALTER TABLE TBL_CAR (TBL_CAR에 추가함)

ADD CONSTRAINT PK_CAR (제약조건 PK_CAR라고 지정한 것을 추가)

PRIMARY KEY(CAR_NUMBER); (기본키 지정)

 

🤷‍♀️ 제약조건 활성화 / 비활성화

- 제약조건 활성화 / 비활성화 
ALTER TABLE TBL_MEMBERS
DISABLE //// ENABLE CONSTRAINT UK_ID(키 == 제약조건 이름명)

---

다른방법으로도 PK를 지정할 수 있는데 아예 지정하는 컬럼에 PRIMARY KEY를 적어주면됩니다.

 

--새로운 TABLE 생성 PRIMARY 만들기

CREATE TABLE TBL_CAR(

CAR_NUMBER NUMBER PRIMARY KEY,

CAR_NAME VARCHAR2(10),

CAR_BRAND VARCHAR2(10),

CAR_DATE DATE,

CAR_COLOR VARCHAR2(10),

CAR_PRICE NUMBER

);

 

---

또한 PK와 그 이름을 한번에 지정할수도 있는데 아래처럼 한번에 넣어주면 됩니다.

--새로운 TABLE 생성과 동시에 이름과 기본키 둘다 만들기

CREATE TABLE TBL_CAR(

CAR_NUMBER NUMBER CONSTRAINT PK_CAR PRIMARY KEY,

CAR_NAME VARCHAR2(10),

CAR_BRAND VARCHAR2(10),

CAR_DATE DATE,

CAR_COLOR VARCHAR2(10),

CAR_PRICE NUMBER

);

 

 

--새로운 TABLE 생성과 동시에 이름과 기본키 둘다 "하단에"만들기

CREATE TABLE TBL_CAR(

CAR_NUMBER NUMBER,

CAR_NAME VARCHAR2(10),

CAR_BRAND VARCHAR2(10),

CAR_DATE DATE,

CAR_COLOR VARCHAR2(10),

CAR_PRICE NUMBER,

CONSTRAINT PK_CAR PRIMARY KEY(CAR_NUMBER)

);

 

하단에 만들때는 꼭 이름(CAR_NUMBER)처럼 지정해줘야합니다

---

🤷‍♂️기본키 해제해보기

--기본키 해제해보기

ALTER TABLE TBL_CAR DROP CONSTRAINT PK_CAR;

---

 

🤷‍♂️ 실습

[기본테이블]

CREATE TABLE TBL_ANIMAL(
ANIMAL_NUMBER NUMBER,
ANIMAL_KIND VARCHAR2(1000),
ANIMAL_AGE NUMBER,
ANIMAL_FEED VARCHAR2(1000));

 

여기서 아래 조건대로 맞춰보기

 

 - 1. 기본키 설정 : ANIMAL_NUMBER
 - 2. 컬럼 추가   : ANIMAL_GENDER
 - 3. 컬럼이름수정 : ANIMAL_NUMBER → 고유번호
 - 4. 컬럼 삭제   : ANIMAL_FEED 
 - 5. 컬럼  자료형 수정   : 고유번호 VARCHAR2(500)
 - 6. 기본키 삭제

---

--1.기본키 설정

CREATE TABLE TBL_ANIMAL(

ANIMAL_NUMBER NUMBER,

ANIMAL_KIND VARCHAR2(1000),

ANIMAL_AGE NUMBER,

ANIMAL_FEED VARCHAR2(1000))

;

 

--기본키 추가

ALTER TABLE TBL_ANIMAL ADD CONSTRAINT PK_ANIMAL_NUMBER

PRIMARY KEY(ANIMAL_NUMBER);

 

--컬럼추가

ALTER TABLE TBL_ANIMAL ADD (ANIMAL_GENDER CHAR(4));

ALTER TABLE TBL_ANIMAL ADD(ANIMAL_GENDER char(4));

 

-- 컬럼이름 수정

ALTER TABLE TBL_ANIMAL RENAME COLUMN ANIMAL_NUMBER TO 고유번호;

 

--컬럼 삭제

ALTER TABLE TBL_ANIMAL DROP COLUMN ANIMAL_FEED;

 

--컬럼 자료형 수정

ALTER TABLE TBL_ANIMAL MODIFY (고유번호 varchar2(500));

 

--기본키 해제해보기

ALTER TABLE TBL_ANIMAL DROP CONSTRAINT PK_ANIMAL_NUMBER;

 

- 제약조건 활성화 / 비활성화 

ALTER TABLE TBL_MEMBERS

DISABLE //// ENABLE CONSTRAINT UK_ID(키 == 제약조건 이름명);

 

- 동물 테이블 삭제

DROP TABLE TBL_ANIMAL;

---

-- 새로운 TABLE

CREATE TABLE TBL_PRODUCT(

PRODUCT_NUMBER NUMBER CONSTRAINT PK_PRODUCT PRIMARY KEY,

PRODUCT_NAME VARCHAR2(1000),

PRODUCT_PRICE NUMBER );

 

SELECT * FROM TBL_PRODUCT;

 

INSERT INTO TBL_PRODUCT VALUES(1, '갤럭시S25', 80000);

INSERT INTO TBL_PRODUCT VALUES(2, '갤럭시S24', 50000);

INSERT INTO TBL_PRODUCT VALUES(3, '갤럭시폴드7', 200000);

SELECT * FROM TBL_PRODUCT;

 

 

---

🤷‍♂️ TBL 연결 해보기

CREATE TABLE TBL_SCHOOL(

SCHOOL_NUMBER NUMBER,

SCHOOL_NAME VARCHAR2(1000),

CONSTRAINT PK_SCHOOL PRIMARY KEY(SCHOOL_NUMBER));

 

 

CREATE TABLE TBL_STUDENT(

STUDENT_NUMBER NUMBER,

STUDENT_NAME VARCHAR2(1000),

STUDENT_AGE NUMBER,

SCHOOL_NUMBER NUMBER,

CONSTRAINT PK_STUDNET PRIMARY KEY (STUDENT_NUMBER),

CONSTRAINT FK_STUDENT FOREIGN KEY (SCHOOL_NUMBER)

REFERENCES TBL_SCHOOL(SCHOOL_NUMBER));

 

INSERT INTO TBL_SCHOOL VALUES(1, 'DBMS대학교');

INSERT INTO TBL_SCHOOL VALUES(2, 'JAVA대학교');

INSERT INTO TBL_SCHOOL VALUES(3, 'PYTHON대학교');

 

SELECT * FROM TBL_SCHOOL;

 

-- 학생테이블쪽에 데이터 입력해보기

INSERT INTO TBL_STUDENT VALUES(1, '홍길동', 11,1);

INSERT INTO TBL_STUDENT VALUES(2, '김철수', 22,2);

INSERT INTO TBL_STUDENT VALUES(3, '이유리', 33,3);

SELECT * FROM TBL_STUDENT;

 

현재 이렇게 된 테이블이 만들어졌습니다.

 

학교번호는 중복이 가능하다고 했으니 한번 확인해보겠습니다.

 

-- 학생테이블쪽에 데이터 입력해보기

INSERT INTO TBL_STUDENT VALUES(1, '홍길동', 11,1);

INSERT INTO TBL_STUDENT VALUES(2, '김철수', 22,2);

INSERT INTO TBL_STUDENT VALUES(3, '이유리', 33,3);

INSERT INTO TBL_STUDENT VALUES(4, '보라돌이', 33,3);

SELECT * FROM TBL_STUDENT;

 

 

 

INSERT INTO TBL_STUDENT VALUES(5, '뽀오', 77,4);

이라고하면 에러가 뜹니다.

SQL Error [2291] [23000]: ORA-02291: integrity constraint (HR.FK_STUDENT) violated - parent key not found

 

지금 학생테이블 기준으로 학교테이블(부모)이 되는데 지금 4같은경우는 학교테이블에서 가져올 수 없기때문에

에러가 나는것이 맞습니다.

INSERT INTO TBL_STUDENT VALUES(5, '뽀오', 77,2);

이렇게 2로 수정을 하게되면 잘 들어가는것을 확인할 수 있습니다.

 

 

-- 외래키 삭제(해지)

ALTER TABLE TBL_STUDENT DROP CONSTRAINT FK_STUDENT;

 

이제 부모 자식간의 연결이 끊기게된것입니다.

엔티티관계도나 상세정보에 들어가면 연결이 해제된것을 볼 수 있습니다.

---

 

🤷‍♂️ 기타 제약조건

 

- 유니크키 지정

컬럼명 자료형(용량) CONSTRAINT 유니크키 이름 UNIQUE

 

-기본 값 지정

DEFAULT 값 NOT NULL

 

-사전에 지정 된 값 지정

CHECK(컬럼명 = 값1 OR 컬럼명 = 값2....)

 

---

제약조건 실습

CREATE TABLE TBL_COLLEGIAN(

COLLEGIAN_NUMBER NUMBER CONSTRAINT PK_COLLEGIAN PRIMARY KEY,

COLLEGIAN_ID varchar2(1000) CONSTRAINT UK_COLLEGIAN UNIQUE,

COLLEGIAN_NAME varchar2(1000),

COLLEGIAN_MAJOR varchar2(1000),

COLLEGIAN_GENDER CHAR(1) DEFAULT 'W' NOT NULL

CHECK(COLLEGIAN_GENDER = 'M' OR COLLEGIAN_GENDER = 'W'));

 

1. 회원테이블은 TBL_MEMBERS / 물건구매 테이블은 TBL_PURCHASE이다.

2. 회원테이블은 번호, 이름, 아이디, 성별이 표기된다.

3. 물건구매 테이블은 번호, 구매물품(공 종류), 주소, 구매자가 표기된다.

4. 성별은 반드시 성별(M,W)을 입력해야 한다. (CHECK제약조건을 이용)

5. 회원 테이블과 물건구매 테이블은 PK(기본키)를 기본적으로 설정

6. FK(외래키)를 지정 후 테이블간의 관계를 설정
<회원테이블의 기본키(번호) <---> 구매테이블의 외래키(구매자)와 연결)>

7. 구매물품은 지정하지 않는 경우 축구공이 선택

8. 기타 제약조건을 설정.(UK를 설정한다)

 

 ↓ 아래는 위와 관련된 답이다.

CREATE TABLE TBL_MEMBERS(
TBL_MEMBER NUMBER CONSTRAINT PK_NUMBER PRIMARY KEY,
TBL_NAME VARCHAR2(1000),
TBL_ID VARCHAR2(1000) CONSTRAINT UK_ID UNIQUE,
TBL_GENDER CHAR(2) NOT NULL
CHECK(TBL_GENDER = 'M' OR TBL_GENDER = 'W'));


CREATE TABLE TBL_PURCHASE(
TBL_NUMBER NUMBER,
TBL_BALL VARCHAR2(1000) DEFAULT '축구공' NOT NULL,
TBL_ADDRESS VARCHAR2(1000),
TBL_BUYER NUMBER,
CONSTRAINT PK_NUMBER2 PRIMARY KEY (TBL_NUMBER),
CONSTRAINT FK_BUYER FOREIGN KEY (TBL_BUYER)
REFERENCES TBL_MEMBERS(TBL_MEMBER));

 

엔티티 관계도

 

까지보면 엔티티로 어떻게 연결되어있는지까지 확인해볼 수 있습니다.지금 보면 TBL_MEMBERS와 PURCHASE에서 <회원테이블의 기본키(번호) <---> 구매테이블의 외래키(구매자)와 연결)> 가 레퍼런스되어있으니까 서로 연결되어있는것을 직관적으로 볼 수 있습니다.

 

상세설명으로 보면 위처럼 되어있는것을 확인할 수 있습니다.

 

여기서 BUYER로 연결되어있는것은 외래키라서 하는것이고

 

기본키 <--> 외래키를 연결할때는 컬럼의 자료형이 반드시 동일해야한다는점을 알아두셔야합니다.

ex). 연결하는 기본키의 자료형이 숫자형인데 외래키의 컬럼명 자료형이 문자형이면 연결이 되지 않습니다!!!