DDL 예제

판매 전표, 제품, 전표 상세에 대한 총 3개의 테이블을 만들어 본다.

 

개념적 설계 (어떤 값들을 저장해야 하는가?)

→ 논리적 설계 (추출했을 때 어떤 데이터형으로 받아올 것인가?)

→ 물리적 설계 (실제 메모리 크기는 어떻게 지정할 것인가?)

 

CREATE, CONSTRAINT

 

기본적인 구조를 생성한다.

--판매전표 / 전표번호(PK), 판매일자, 고객명, 총액
CREATE TABLE receipt(
	id varchar2(12), --20220628-001 이런느낌~
	rdate DATE,
	client_name varchar2(34),
	total_price NUMBER
)

--제품 / 제품번호(PK), 제품명, 제품단가
CREATE TABLE product(
	id varchar2(12),
	name varchar2(100),
	price NUMBER )

--전표상세 / 전표번호(PK/FK), 제품번호(FK), 수량, 단가, 금액
CREATE TABLE r_detail(
	rid varchar2(12),
	pid varchar2(12),
	quantity NUMBER,
	price NUMBER,
	total_price NUMBER
	)

 

NOT NULL과 DEFAULT를 먼저 지정한다.

--판매전표 / 전표번호(PK), 판매일자, 고객명, 총액
CREATE TABLE receipt(
	id varchar2(12), --20220628-001 이런느낌~
	rdate DATE CONSTRAINT receipt_rdate_nn NOT NULL,
	cname varchar2(34) CONSTRAINT receipt_cname_nn NOT NULL,
	total NUMBER)

--제품 / 제품번호(PK), 제품명, 제품단가
CREATE TABLE product(
	id varchar2(12),
	name varchar2(100) CONSTRAINT product_name_nn NOT NULL,
	price NUMBER)

--전표상세 / 전표번호(PK/FK), 제품번호(FK), 수량, 단가, 금액
CREATE TABLE detail(
	rid varchar2(12),
	pid varchar2(12),
	quantity NUMBER CONSTRAINT detail_quantity_nn NOT NULL,
	price NUMBER CONSTRAINT detail_price_nn NOT NULL,
	total NUMBER)
	
--COMMIT;

 

PRIMARY KEY와 FOREIGN KEY를 지정한다.

--판매전표 / 전표번호(PK), 판매일자, 고객명, 총액
CREATE TABLE receipt(
	id varchar2(12), --20220628-001 이런느낌~
	rdate DATE CONSTRAINT receipt_rdate_nn NOT NULL,
	cname varchar2(34) CONSTRAINT receipt_cname_nn NOT NULL,
	total NUMBER,
	
	CONSTRAINT receipt_id_pk PRIMARY KEY(id),
	CONSTRAINT receipt_total_ck CHECK(total>0)
)

--제품 / 제품번호(PK), 제품명, 제품단가
CREATE TABLE product(
	id varchar2(12),
	name varchar2(100) CONSTRAINT product_name_nn NOT NULL,
	price NUMBER,
	
	CONSTRAINT product_id_pk PRIMARY KEY(id),
	CONSTRAINT product_price_ck CHECK(price>0))

--전표상세 / 전표번호(PK/FK), 제품번호(FK), 수량, 단가, 금액
CREATE TABLE detail(
	rid varchar2(12),
	pid varchar2(12),
	quantity NUMBER CONSTRAINT detail_quantity_nn NOT NULL,
	price NUMBER CONSTRAINT detail_price_nn NOT NULL,
	total NUMBER,
	
	CONSTRAINT detail_rid_pk PRIMARY KEY(rid),
	CONSTRAINT detail_rid_fk FOREIGN KEY(rid) REFERENCES receipt(id),
	CONSTRAINT detail_pid_fk FOREIGN KEY(pid) REFERENCES product(id),
	CONSTRAINT detail_total_ck CHECK(total>0)
	)
	
--COMMIT;

 

완성~

 

 

DROP TABLE

 

참조 테이블이 있는 데이터는 지울 때 하위 데이터를 먼저 지워야 한다.

→ 참조 데이터가 먼저 사라지면 에러가 발생하기 때문~

DROP TABLE detail --detail을 가장 먼저 지워야 한다.
DROP TABLE product
DROP TABLE receipt

 

user_constraints 테이블을 조회하면 테이블의 각 컬럼에 지정된 제약조건을 확인할 수 있다.

SELECT * 
FROM user_constraints 
WHERE table_name IN ('RECEIPT','PRODUCT','DETAIL')

 

 

ALTER

 

1. 컬럼 추가/수정/삭제

--컬럼 추가
ALTER TABLE table_name ADD column_name dataType [constraint]
--컬럼 수정
ALTER TABLE table_name MODIFY column_name dataType [constraint]
--컬럼 삭제
ALTER TABLE table_name DROP COLUMN column_name

2. 제약 조건 추가/삭제

--제약 조건 추가
ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint(column_name)
--제약 조건 삭제
ALTER TABLE table_name DROP constraint_name

 

일단 아무런 제약조건 없이 테이블을 생성한 이후에 ALTER를 이용해서 제약조건을 부여할 수도 있다.

--판매전표 / 전표번호(PK), 판매일자, 고객명, 총액
CREATE TABLE receipt(
	id varchar2(12), --20220628-001 이런느낌~
	rdate DATE,
	cname varchar2(34),
	total NUMBER
)

--제품 / 제품번호(PK), 제품명, 제품단가
CREATE TABLE product(
	id varchar2(12),
	name varchar2(100),
	price NUMBER
)

--전표상세 / 전표번호(PK/FK), 제품번호(FK), 수량, 단가, 금액
CREATE TABLE detail(
	rid varchar2(12),
	pid varchar2(12),
	quantity NUMBER,
	price NUMBER,
	total NUMBER
	)

--PRIMARY KEY 추가
ALTER TABLE receipt ADD CONSTRAINT receipt_id_pk PRIMARY KEY(id)

--NOT NULL 추가(ADD가 아니라 MODIFY!!!)
ALTER TABLE receipt MODIFY rdate DATE CONSTRAINT receipt_rdate_nn NOT NULL
ALTER TABLE receipt MODIFY cname varchar2(34) CONSTRAINT receipt_cname_nn NOT NULL

--CHECK 추가
ALTER TABLE receipt ADD CONSTRAINT receipt_total_ck CHECK(total>0)

--FOREIGN KEY 추가
ALTER TABLE detail ADD CONSTRAINT detail_rid_fk FOREIGN KEY(rid) REFERENCES receipt(id)

--UNIQUE 추가
ALTER TABLE detail ADD CONSTRAINT detail_rid_uk UNIQUE(rid)

 

이미 부여한 제약조건을 삭제하고 싶을 때는

제약조건을 부여할 당시 지정한 이름으로 간편하게 삭제할 수 있다.

--제약조건 제거(DROP)
ALTER TABLE detail DROP CONSTRAINT detail_rid_uk

 

* 주의할 점

- NOT NULL 제약조건을 추가할 때는 ADD가 아니라 MODIFY

- FOREIGN KEY 지정을 위해서는 PRIMARY KEY로 지정된 컬럼을 참조해야 한다.

(= 참조할 컬럼을 먼저 PRIMARY KEY로 지정해야 한다.)