[DB] Table 생성(Create)과 제약조건(Constraint) 추가 해보기 + ERD생성

scott_연습.sql

0.01MB

 

안녕하세요? 재삐 입니다.

Oracle DataBase에서

SCOTT 계정으로 간단한 학습을 해보겠습니다

 

(단축키 : 생성된 테이블명에 커서를 놓고 Shift + F4를 누르면 테이블 정보가 나옵니다.)

 

• 요구사항 기준은 이런데 제약조건은 추가로 몇 가지 더 넣을 것입니다.

기본 요구사항:
1. Customers, Orders, OrderDetails, Product 네 개의 테이블을 생성합니다.
2. Customers 테이블에는 CustomerID, CustomerName, Loc 컬럼이 있어야 합니다.
3. Orders 테이블에는 OrderID, CustomerID, OrderDate, Status 컬럼이 있어야 합니다.
4. OrderDetails 테이블에는 OrderDetailID, OrderID, ProductID, Quantity, Price 컬럼이 있어야 합니다.
5. Product 테이블에는 ProductID, ProductName, ,Price 컬럼이 있어야 합니다.
6. 모든 테이블은 적절한 데이터 타입을 가진 필요한 기본 키(primary key)와 외래 키(foreign key)를 설정해야 합니다.

7. Orders 테이블의 Status 컬럼은 'Pending', 'Completed', 'Cancelled' 중

    하나의 값을 가질 수 있도록 제약 조건을 설정합니다.

8. 각 테이블에 적어도 5개 이상의 행을 삽입하여 테스트 데이터를 준비합니다.

목차

[1] 테이블 생성
[2] 제약조건 PK, FK 추가
[3] 제약조건 NOT NULL 추가(수정)
[4] 제약조건 CHECK, UNIQUE, DEFAULT 추가
[5] 시퀀스 생성
[6] 샘플데이터 넣기 INSERT INTO
[7] 인덱스(INDEX) 생성
[8] 전체 코드
[9] ERD 생성

 

 

---

[1] 테이블 생성

• 테이블 생성하는 명령문 구조

create table 테이블명 (
 속성명1 속성타입1 제약조건1
 속성명2 속성타입2 제약조건2
 ...
);

---

[2] 제약조건 PK, FK 추가

• 기본키(PK) 제약조건 추가

alter table 테이블명 add constraint 제약조건이름 primary key(컬럼명);

• 외래키(FK) 제약조건 추가

alter table 테이블명 add constraint 제약조건이름 foreign key(컬럼명) references 참조할 테이블명;

---

[3] 제약조건 NOT NULL 추가(수정)

• NOT NULL 제약조건 수정

alter table 테이블명 modify 컬럼명 not null;

 

• NOT NULL 제약조건은 ALTER ~ ADD가 안되므로 MODIFY를 이용해서 수정
• 제약조건 이름을 추가하려면 CREATE TABLE 단계에서 CONSTRAINT 제약조건 이름 NOT NULL로 하면 된다.
• 테이블 생성시 not null 제약조건을 추가하면서 제약조건 이름을 넣는 방법 예시

create table 테이블명 (
  컬럼명           데이터타입      (constraint 제약조건 이름) 제약조건,
  customername     varchar2(10)    constraint name_nn not null
);

 

---

[4] 제약조건 CHECK, UNIQUE, DEFAULT 추가

 

• CHECK 제약조건 추가

alter table 테이블명 add constraint 제약조건이름 check 제약조건(select의 where문에 넣는거 생각, 서브쿼리도 가능)

 

• UNIQUE 제약조건 추가

alter table 테이블명 constraint 제약조건이름 unique(컬럼명)

 

• DEFAULT 제약조건 추가

alter table 테이블명 modify 컬럼명 default 데이터값

---

[5] 시퀀스 생성

해당 기능은 Oracle에서만 가능한걸로 알고 있다. (MySql은 다른 방법이 있으니 그것을 참조)

• 시퀀스(sequence) 생성

create sequence 시퀀스 이름
increment by n      		-- n만큼 증가 (기본값 1)
start with n                -- n에서 시작 (기본값 1)
maxvalue n | nomaxvalue
minvalue n | nominvalue
cycle | nocycle             -- cycle : maxvalue에 도달했을 때 start with에서 다시 시작
cache n | nocache           -- 메모리에 미리 할당해 놓은 수를 지정 (기본값 20)

• 간단한 사용 방법을 알아보자
• 시퀀스 번호 생성

-- test 시퀀스를 만들고
create sequence test_seq;

-- .nextval을 이용해 생성한다
select test_seq.nextval from dual;
-- 추가로 이 생성 문장만 한 3번 실행 해 본다.

• 현재 시퀀스 번호 확인 (가장 마지막으로 생성된 시퀀스 확인)

-- .currval로 마지막으로 생성된 시퀀스 확인할 수 있다.
select test_seq.currval from dual;

---

[6] 샘플데이터 넣기 INSERT INTO

• INSERT의 명령문 구조

insert into 테이블명 (컬럼명) values (데이터값);
-- 1. 컬럼명을 적으면 순서에 맞게 데이터값을 넣으면 된다. (컬럼명 몇개 생략가능)

insert into 테이블명 values (데이터값)
-- 2. 컬럼명을 생략하고 데이터값 입력시 컬럼명에 따른 모든 데이터값을 입력해야 한다.

 

DDL, DML, DCL에 대한 지식이 있으면 DML인 INSERT문을 사용할 때 COMMIT과 ROLLBACK에 대한 개념을 생각해보자

 

---

[7] 인덱스(INDEX) 생성

• 인덱스 생성 명령문

create index 인덱스 이름 on 테이블명(컬럼명)

---

[8] 전체 코드

• .sql 파일로는 글 상단에 보시면 첨부해 두었습니다.

-- 참조 무결성 때문에 DROP 순서를 해당 순서로 지정
-- ☆테이블 삭제
drop table orderdetails;
drop table orders;
drop table product;
drop table customers;

-- ★테이블 생성
create table customers (
  customer_id      number(3),
  customername     varchar2(10),
  loc              varchar2(15),
  cdate            TIMESTAMP,
  udate            TIMESTAMP
);

create table orders(
  order_id         number(3),
  customer_id      number(3),
  orderdate        date,
  status           varchar2(10)
);

create table orderdetails(
  orderdetail_id   number(4),
  order_id         number(3),
  product_id       number(4),
  quantity         number(3),
  price            number(7)
);

create table product(
  product_id       number(4),
  productname      varchar2(30),
  price            number(7)
);


-- ★기본키(PK) 제약조건 추가
--alter table 테이블명 add constraint 제약조건이름 primary key(컬럼명);
alter table customers add constraint customers_pk primary key(customer_id);
alter table orders add constraint order_pk primary key(order_id);
alter table orderdetails add constraint orderdetails_pk primary key(orderdetail_id);
alter table product add constraint product_pk primary key(product_id);

-- ★외래키(FK) 제약조건 추가
--alter table 테이블명 add constraint 제약조건이름 foreign key(컬럼명) references 참조할테이블명;
alter table orders add constraint orders_fk foreign key(customer_id)
            references customers;
alter table orderdetails add constraint orderdetails_fk1 foreign key(order_id)
            references orders;
alter table orderdetails add constraint orderdetails_fk2 foreign key(product_id)
            references product;

-- ★NOT NULL 제약조건 추가(수정)
alter table customers modify customername not null;
alter table customers modify loc not null;

alter table orders modify customer_id not null;
alter table orders modify orderdate not null;
alter table orders modify status not null;

alter table orderdetails modify order_id not null;
alter table orderdetails modify product_id  not null;
alter table orderdetails modify quantity not null;
alter table orderdetails modify price not null;

alter table product modify productname not null;
alter table product modify price not null;


-- ★check 제약조건 추가
-- ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건 이름 CHECK 제약조건
alter table orders add constraint orders_ck
            check (status in ('PENDING', 'COMPLETED', 'CANCELLED'));

-- ★unique 제약조건 추가
-- ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건 이름 UNIQUE(컬럼명)
alter table product add constraint product_uk unique(productname);

-- ★DEFAULT 제약조건 추가
alter table customers modify cdate default sysdate;
alter table customers modify udate default sysdate;


-- ☆시퀀스 제거
drop sequence customers_customer_id;
drop sequence orders_order_id;
drop sequence od_orderdetails_id;
drop sequence product_product_id;
-- ★시퀀스 생성
create sequence customers_customer_id;
create sequence orders_order_id;
create sequence od_orderdetails_id;
create sequence product_product_id;

--drop sequence test_seq;
--create sequence test_seq;
--select test_seq.nextval from dual;
--select test_seq.currval from dual;


-- ★샘플데이터 넣기 insert into
INSERT INTO customers (customer_id, customername, loc)
      VALUES (customers_customer_id.nextval, '홍길동1', '울산1');
INSERT INTO customers (customer_id, customername, loc)
      VALUES (customers_customer_id.nextval, '홍길동2', '울산1');
INSERT INTO customers (customer_id, customername, loc)
      VALUES (customers_customer_id.nextval, '홍길동3', '울산2');
INSERT INTO customers (customer_id, customername, loc)
      VALUES (customers_customer_id.nextval, '홍길동4', '울산2');
INSERT INTO customers (customer_id, customername, loc)
      VALUES (customers_customer_id.nextval, '홍길동5', '울산2');
-- customer_id에 동일 값을 주면 개체 무결성에 위배됨

select * from customers;

INSERT INTO orders (order_id, customer_id, orderdate, status)
      VALUES (orders_order_id.nextval, 1, TO_DATE('2024/1/1', 'YYYY/MM/DD'), 'PENDING');
INSERT INTO orders (order_id, customer_id, orderdate, status)
      VALUES (orders_order_id.nextval, 1, TO_DATE('2024/1/2', 'YYYY/MM/DD'), 'PENDING');
INSERT INTO orders (order_id, customer_id, orderdate, status)
      VALUES (orders_order_id.nextval, 2, TO_DATE('2024/1/1', 'YYYY/MM/DD'), 'COMPLETED');
INSERT INTO orders (order_id, customer_id, orderdate, status)
      VALUES (orders_order_id.nextval, 2, TO_DATE('2024/1/2', 'YYYY/MM/DD'), 'CANCELLED');
INSERT INTO orders (order_id, customer_id, orderdate, status)
      VALUES (orders_order_id.nextval, 2, TO_DATE('2024/1/3', 'YYYY/MM/DD'), 'PENDING');

select * from orders;

INSERT INTO product (product_id, productname, price)
      VALUES (product_product_id.nextval, '컴퓨터', 1000000);
INSERT INTO product (product_id, productname, price)
      VALUES (product_product_id.nextval, '프린트', 500000);
INSERT INTO product (product_id, productname, price)
      VALUES (product_product_id.nextval, '마우스', 30000);
INSERT INTO product (product_id, productname, price)
      VALUES (product_product_id.nextval, '키보드', 100000);
INSERT INTO product (product_id, productname, price)
      VALUES (product_product_id.nextval, '모니터', 400000);

select * from product;

-- 참조 무결성 때문에 orderdetails 테이블을 가장 마지막에 추가
INSERT INTO orderdetails (orderdetail_id, order_id, product_id, quantity, price)
      VALUES (od_orderdetails_id.nextval, 1, 1, 1, 1000000);
INSERT INTO orderdetails (orderdetail_id, order_id, product_id, quantity, price)
      VALUES (od_orderdetails_id.nextval, 2, 1, 1, 1000000);
INSERT INTO orderdetails (orderdetail_id, order_id, product_id, quantity, price)
      VALUES (od_orderdetails_id.nextval, 2, 2, 1, 500000);
INSERT INTO orderdetails (orderdetail_id, order_id, product_id, quantity, price)
      VALUES (od_orderdetails_id.nextval, 3, 3, 1, 30000);
INSERT INTO orderdetails (orderdetail_id, order_id, product_id, quantity, price)
      VALUES (od_orderdetails_id.nextval, 4, 4, 1, 100000);
INSERT INTO orderdetails (orderdetail_id, order_id, product_id, quantity, price)
      VALUES (od_orderdetails_id.nextval, 5, 5, 1, 400000);

select * from orderdetails;


---- ☆인덱스 제거
--drop index customers_customername_idx;
-- ★인덱스(INDEX) 생성
--create index 인덱스 이름 on 테이블명(컬럼명)
create index customers_customername_idx on customers(customername);

---

[9] ERD 생성

• 왼쪽 상단 [파일(F)] - [Data Modeler] - [임포트] - [데이터 딕셔너리]

• 데이터베이스에 접속할 접속 이름을 scott 선택 후 다음

• 스키마/데이터베이스 선택에서 C##SCOTT 선택 후 다음
• 임포트할 객체 선택에서 객체이름이 CUSTOMERS, ORDERS, ORDERDETAILS, PRODUCT 선택 후 다음

• 완료 후 ERD가 생성 된 것을 볼 수 있다.

 

---

Copyright ⓒ. JaePPy All rights reserved.

usopp1004@naver.com