PostgreSQL 테이블 생성

테이블 생성

테이블은 데이터를 담는 그릇으로써 반드시 생성해야만 데이터를 저장 할 수 있습니다.

 

테이블 생성시 컬럼의 제약 조건

제약조건명	설명
NOT NULL	해당 제약 조건이 있는 컬럼은 NULL이 저장될 수 없습니다.
UNIQUE	해당 제약 조건이 있는 컬럼의 값은 테이블 내에서 유일해야 합니다.(중복X)
PRIMARY KEY	해당 제약 조건이 있는 컬럼의 값은 테이블내에서 유일해야 하고 반드시 NOT NULL 이어야 합니다.
CHECK	해당 제약 조건이 있는 컬럼은 지정하는 조건에 맞는 값이 들어가야 합니다.
REFERENCES	해당 제약 조건이 있는 컬럼의 값은 참조하는 테이블의 특정 컬럼에 값이 존재해야 합니다.

 

실습

 

- ACCOUNT_TEST 테이블 생성

 

1. USER_ID는 기본키 이고 NOT NULL이면서 UNIQUE해야 합니다.

2. USERNAME은 UNIQUE하면서 NOT NULL 이어야 합니다.

3. PASSWORD 는 NOT NULL 이어야 합니다.

4. EMAIL 은 UNIQUE하면서 NOT NULL 이어야 합니다.

5. CRETAED_ON은 NOT NULL이어야 합니다.

6. LAST_LOGIN은 NULL 이어도 상관없습니다.

CREATE TABLE ACCOUNT_TEST
(
	USER_ID SERIAL PRIMARY KEY,
	USERNAME VARCHAR(50) UNIQUE NOT NULL,
	PASSWORD VARCHAR(50) NOT NULL,
	EMAIL VARCHAR(355) UNIQUE NOT NULL,
	CREATED_ON TIMESTAMP NOT NULL,
	LAST_LOGIN TIMESTAMP
);

 

- ROLE_TEST 테이블 생성

 

1. ROLE_ID는 기본키 이고 NOT NULL 이면서 UNIQUE해야 합니다.

2. ROLE_NAME은 UNIQUE하면서 NOT NULL 이어야 합니다.

CREATE TABLE ROLE_TEST
(
	ROLE_ID SERIAL PRIMARY KEY,
	ROLE_NAME VARCHAR(255) UNIQUE NOT NULL
);

 

- ACCOUNT_ROLE_TEST 테이블 생성

 

1. USER_ID는 NOT NULL 이어야 합니다.

2. ROLE_ID는 NOT NULL 이어야 합니다.

3. GRANT_DATE는 NULL이어도 상관 없습니다.

4. 기본키는 USER_ID , ROLE_ID 로 합니다.

5. ROLE_ID 컬럼은 ROLE_TEST 테이블의 ROLE_ID컬럼을 참조합니다.

6. ROLE_ID 컬럼은 ROLE_TEST 테이블의 ROLE_ID컬럼에 대한 삭제 혹은 변경시 아무것도 하지 않습니다.

7. USER_ID 컬럼은 ACCOUNT_TEST 테이블의 USER_ID컬럼을 참조합니다.

8. USER_ID 컬럼은 ACCOUNT_TEST 테이블의 USER_ID컬럼에 대한 삭제 혹은 변경시 아무것도 하지 않습니다.

CREATE TABLE ACCOUNT_ROLE_TEST
(
	USER_ID INTEGER NOT NULL,
	ROLE_ID INTEGER NOT NULL,
	GRANT_DATE TIMESTAMP WITHOUT TIME ZONE,
	PRIMARY KEY (USER_ID,ROLE_ID),
	CONSTRAINT ACCOUNT_ROLE_ROLE_ID_FKEY FOREIGN KEY (ROLE_ID)
	REFERENCES ROLE_TEST (ROLE_ID) MATCH SIMPLE
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT ACCOUNT_ROLE_USER_ID_FKEY FOREIGN KEY (USER_ID)
	REFERENCES ACCOUNT_TEST (USER_ID) MATCH SIMPLE ON
	UPDATE NO ACTION ON DELETE NO ACTION
);

 

조건상 PRIMARY KEY와 UNIQUE NOT NULL은 유일하면서 NOT NULL입니다.

하지만 PRIMARY KEY는 해당 테이블의 기본값 이므로 서로 제약조건 같지만 엄연히 다른 역할을 합니다.

 

 

- 테이블 관계

 

하나의 ROLE_ID 는 여러개의 USER_ID를 가질 수 있고 , 한명의 USER_ID는 여러개의 ROLE_ID를 가질 수 있습니다.

그러하여 N : N (다 대 다) 관계가 형성 됩니다. 그러한 다대다 관계를 이어주는 테이블이 ACCOUNT_ROLE_TEST 테이블 입니다.

ERD

 

- ACCOUNT_TEST 테이블에 데이터 INSERT

INSERT INTO ACCOUNT_TEST VALUES (1,'이름','1234','DBMS@NAVER.COM',CURRENT_TIMESTAMP,NULL);
commit;

실습1

- ROLE_TEST 테이블에 데이터 INSERT

INSERT INTO ROLE_TEST VALUES (1,'DBA');
COMMIT;

실습2

- ACCOUNT_ROLE_TEST 데이터 INSERT

INSERT INTO ACCOUNT_ROLE_TEST VALUES (1,1,CURRENT_TIMESTAMP);
COMMIT;

실습3

 

- 참조 무결성 ERROR 테스트

1.ACCOUNT_ROLE_TEST 테이블에 데이터 INSERT

INSERT INTO ACCOUNT_ROLE_TEST VALUES (2,1,CURRENT_TIMESTAMP);

ACCOUNT_TEST 테이블에 USER_ID 2번이 없기 때문에 오류가 발생 합니다.

실습4

SQL Error [23503]: 오류: "account_role_test" 테이블에서 자료 추가, 갱신 작업이 "account_role_user_id_fkey" 참조키(foreign key) 제약 조건을 위배했습니다 
 Detail: (user_id)=(2) 키가 "account_test" 테이블에 없습니다.

 

INSERT INTO ACCOUNT_ROLE_TEST VALUES (1,2,CURRENT_TIMESTAMP);

ROLE_TEST 테이블에 ROLE_ID 2번이 없기 때문에 오류가 발생 합니다.

 

실습5

SQL Error [23503]: 오류: "account_role_test" 테이블에서 자료 추가, 갱신 작업이 "account_role_role_id_fkey" 참조키(foreign key) 제약 조건을 위배했습니다
  Detail: (role_id)=(2) 키가 "role_test" 테이블에 없습니다.

 

INSERT INTO ACCOUNT_ROLE_TEST VALUES (1,1,CURRENT_TIMESTAMP);

이미 존재하는 데이터가 있어 오류가 발생합니다.

실습5

SQL Error [23505]: 오류: 중복된 키 값이 "account_role_test_pkey" 고유 제약 조건을 위반함
  Detail: (user_id, role_id)=(1, 1) 키가 이미 있습니다.

 

 

2.ACCOUNT_TEST 테이블에 데이터 UPDATE,DELETE

UPDATE ACCOUNT_TEST 
SET USER_ID = 2 
WHERE USER_ID = 1;

이미 참조된 테이블이 존재하므로 ACCOUNT_TEST의 데이터를 수정할 수 없습니다.

실습6

SQL Error [23503]: 오류: "account_test" 테이블의 자료 갱신, 삭제 작업이 "account_role_user_id_fkey" 참조키(foreign key) 제약 조건 - "account_role_test" 테이블 - 을 위반했습니다
  Detail: (user_id)=(1) 키가 "account_role_test" 테이블에서 여전히 참조됩니다.

 

DELETE FROM ACCOUNT_TEST 
WHERE USER_ID = 1;

삭제 또한 이미 참조하고있는 테이블이 있어 삭제가 불가능합니다.

실습7

SQL Error [23503]: 오류: "account_test" 테이블의 자료 갱신, 삭제 작업이 "account_role_user_id_fkey" 참조키(foreign key) 제약 조건 - "account_role_test" 테이블 - 을 위반했습니다
  Detail: (user_id)=(1) 키가 "account_role_test" 테이블에서 여전히 참조됩니다.

 

하지만 업데이트 또는 삭제하는 방법이 없는것이 아니기 때문에 걱정 안하셔도 됩니다!