테이블 설계

CREATE TABLE customer (
  id VARCHAR2 (5),
  pwd VARCHAR2(5),
  name VARCHAR2(15)
);

-- 테이블 구조변경
-- 컬럼추가: zipcode CHAR(5)
ALTER TABLE customer
ADD zipcode CHAR(5);

-- 컬럼추가: addr VARCHAR2(10)
ALTER TABLE customer
ADD addr VARCHAR2(10);


-- 컬럼이름바꾸기: addr 컬럼 이름을 addr1로 바꾸기
ALTER TABLE customer
RENAME COLUMN addr TO addr1;

-- 컬럼 자료형 또는 길이변경하기: addr1컬럼의 길이를 30으로 변경
ALTER TABLE customer MODIFY addr1 VARCHAR2(30);
alter table customer drop primary key;

-- 제약 조건 설정하기 -> id컬럼에 pk 설정
-- 1.PRIMARY KEY: NOT NULL + UNIQUE

ALTER TABLE customer
ADD CONSTRAINT customer_id_pk PRIMARY KEY(id);

--2.NOT NULL 제약조건
ALTER TABLE customer MODIFY pwd NOT NULL;
select * from customer;

------------------------------------------------------------------------------
-- 3.Foreign key 제약조건 추가:  부모엔티티(customer)의 pk를 자식엔티티(order_info)에서 참조

-- 주문테이블생성: order_info

CREATE TABLE order_info (
  order_no NUMBER,
  order_id VARCHAR2(5), -- -> 부모엔티티 pk참조
  order_dt DATE
);

-- 주문자료추가
-- 정상적으로 insert 되지만 에러가 나오게해야한다. id999 고객은 존재하지 않기 때문에
INSERT INTO order_info (order_no, order_id, order_dt) VALUES (1, 'id999', SYSDATE);
select * from order_info;

-- id999값을 가진 튜플을 삭제하자
delete from order_info where order_no=1;
select * from order_info;

-- fk 제약조건 설정
alter table order_info ADD CONSTRAINT order_info_id_fk FOREIGN KEY (order_id)
REFERENCES customer(id);

-- 에러발생 (customer 테이블에는 id999라는 아이디를 가진 고객이 없으므로) 
INSERT INTO order_info (order_no, order_id, order_dt) VALUES (1, 'id999', SYSDATE);

--4. CHECK 제약조건: 값의 범위, 종류
-- 고객자료 추가: 비정상자료 -> 비번이 1자리인 경우
INSERT INTO customer (id, pwd, name) VALUES ('id2', 'p', 'n1');
select * from customer;
delete from customer where id = 'id2';
 
-- 비밀번호 2자리이 이상 되게만드는 제약
ALTER TABLE customer
ADD CONSTRAINT customer_pwd_ck CHECK (LENGTH(pwd) > 1);


-- 이렇게 한자리 비밀번호 insert 시도하면 에러 발생
INSERT INTO customer (id, pwd, name) VALUES ('id2', 'p', 'n1');

-- 상품테이블 제거 (같은 이름 테이블이 존재 할 수도 있으니)
DROP TABLE product;

-- 상품테이블 생성: product
CREATE TABLE product (
  prod_no VARCHAR2(5) CONSTRAINT product_no_pk PRIMARY KEY, -- 컬럼레벨 제약조건 설정
  prod_name VARCHAR2(50) NOT NULL, -- NOT NULL제약조건은 반드시 컬럼레벨로만 설정가능
  prod_price NUMBER(7),
  CONSTRAINT product_price_ck CHECK (prod_price >= 0) -- 테이블레벨 제약조건 (가격은 음수가 될 수 없으므로)
);

select * from product;

-- 주문상세테이블의 order_no컬럼에 PRIMARY KEY 제약조건 추가
ALTER TABLE order_info
ADD CONSTRAINT order_info_no_pk PRIMARY KEY(order_no);

select * from order_info;
 
-- 주문테이블 order_id에 NOT NULL 제약조건 추가
ALTER TABLE order_info
MODIFY order_id NOT NULL;

--------------------------------------------------------------------------------------------------------------------

-- 주문 상세테이블 생성: order_line
CREATE TABLE order_line (
  order_no NUMBER, -- order_info 테이블에도 존재하는 컬럼
  order_prod_no VARCHAR2(5) NOT NULL, -- 상품테이블에도 존재하는 컬럼
  order_quantity NUMBER(2) NOT NULL,
  -- 테이블레벨 제약조건 설정 
  CONSTRAINT order_line_no_prod_no_pk PRIMARY KEY (order_no, order_prod_no),
  CONSTRAINT order_line_order_no_fk FOREIGN KEY (order_no) REFERENCES order_info(order_no),
  CONSTRAINT order_line_prod_no_fk FOREIGN KEY (order_prod_no) REFERENCES product(prod_no),
  CONSTRAINT order_line_quantity_ck CHECK (order_quantity > 0 )
);


-- DEFAULT: 기본값 설정, 설정안하면 NULL이 기본값이 된다.
ALTER TABLE order_info MODIFY order_dt DEFAULT SYSDATE;

 
-- 데이터추가
INSERT INTO customer (id, pwd, name, zipcode, addr1)
VALUES ('id1', 'p1', 'n1', '12345', '6층');

select * from customer;
select * from order_line;