제약 조건 / ERD 사용

 

 

1. 고객테이블 생성

테이블 이름 : CUSTOMER

컬럼          이름 자료형          제약조건

id               varchar2(5)      PRIMARY KEY

pwd           varchar2(10)    NOT NULL

name         varchar2(20)    

 

컬럼레벨로 제약조건 설정

CREATE TABLE customer(

 id          varchar2(2) constraint customer_pk PRIMARY KEY,

pwd       varchar(10) NOT NULL,

name     varchar2(20)

);

 

 

INSERT INTO customer (id, pwd, name) VALUES ('id1', 'p1', 'n1');

INSERT INTO customer (id, pwd, name) VALUES ('id1', 'aaa', 'aaa'); -- 오류( id중복 등록시 pk중복)

 

INSERT INTO customer (id, pwd, name) VALUES ('id2', ' ', 'bbb'); --오류

INSERT INTO customer (id, pwd, name) VALUES ('id2', 'P2 ', '임지윤');

 

 

 

테이블용 딕셔너리 : user_tables

제약조건용 딕셔너리 : user_constraints

SELECT * FROM user_constraints WHERE table_name = 'CUSTOMER';

 

 

테이블 레벨로 제약조건 설정

2. 주문테이블 생성

테이블 이름 : ORDER_INFO

컬럼              이름 자료형          제약조건                기본값

order_no       number             PRIMARY KEY            null

order_id        varchar2(5)       FOREIGN KEY            null

order_dt        date                                                     sysdate

 

CREATE TABLE ORDER_INFO(

    order_no             number,

    order_id              varchar2(5),

    order_dt              date DEFAULT         sysdate,
    CONSTRAINT    order_info_pk           PRIMARY KEY(order_no),
    CONSTRAINT    order_id_fk               FOREIGN KEY(order_id)  REFERENCES customer(id)

);

 

INSERT INTO order_info(order_no, order_id) VALUES (1, 'id1' ); --OK

INSERT INTO order_info(order_no, order_id) VALUES (2, 'id1' ); --OK

INSERT INTO order_info(order_no, order_id) VALUES (3, 'XXX' ); --ERROR

 

PK는 NULL값 안되고, FK는 NULL값 저장 됨

 

INSERT INTO order_info(order_no, order_id) VALUES (4, NULL); --OK

DELETE order_info WHERE order_no =4;

 

FK(order_id)에 NOT NULL제약조건을 추가

ALTER TABLE order_info 

MODIFY order_id NOT NULL;

 

NOT NULL제약조건 추가한 뒤 아래의 코드는 에러

INSERT INFO order_info(order_no, order_id) VALUES (4,NULL); --ERROR

 

 

 

3. 주문 상세테이블 생성

테이블 이름 : ORDER_LINE

컬럼이름               자료형              제약조건                                             기본값

order_no               number            PRIMARY KEY, FOREIGN KEY            null

order_prod_no     varchar2(5)      PRIMARY KEY, FOREIGN KEY            null

order_quatity        number(3)        CHECK                     sysdate

 

 

CREATE TABLE order_line (
order_no NUMBER,
order_prod_no VARCHAR2(5), 
order_quantity NUMBER(2),
CONSTRAINT order_line_Pk PRIMARY KEY(order_no, order_prod_no), 
CONSTRAINT order_no_fk FOREIGN KEY(order_no) REFERENCES order_info(order_no), 
CONSTRAINT order_prod_no_fk FOREIGN KEY(order_prod_no)REFERENCES product(prod_no),
CONSTRAINT order_quantity_ck CHECK (order_quantity>0) 
);

 

INSERT INTO order_line (order_no, order_prod_no, order_quantity) VALUES (1, 'C0001', 3);

INSERT INTO order_line (order_no, order_prod_no, order_quantity) VALUES (1, 'C0002', 1);

INSERT INTO order_line (order_no, order_prod_no, order_quantity) VALUES (1, 'C0003', 2);

INSERT INTO order_line (order_no, order_prod_no, order_quantity) VALUES (2, 'C0001', 1);

 

INSERT INTO order_line (order_no, order_prod_no, order_quantity) VALUES (99999, 'C0001', 1); --  ERROR
FK에 위배됨

 

 

INSERT INTO order_line (order_no, order_prod_no, order_quantity) VALUES (1, 'xxxxx', 1); -- ERROR

INSERT INTO order_line (order_no, order_prod_no, order_quantity) VALUES (2, 'C0001', 0); -- ERROR

INSERT INTO order_line (order_no, order_prod_no, order_quantity) VALUES (2, 'D0001', NULL);

-- OK (order q'ty에 not null 제약조건이 없어서 null 가능)

DELETE order_line WHERE order_no=2 AND order_prod_no = 'D0001';

 

ALTER TABLE order_line 

MODIFY order_quantity NOT NULL; -- NOT NULL제약조건 추

                                                    

 

ERD

 

 

 

 

 

 

 

 

 

 

 

 

 

주문번호 발급방법

1) MAX함수 사용

 

--id2번고객이 'C0001'상품을 2개 주문한다

INSERT INTO order_info(order_no, order_id, order_dt) VALUES ( 

   SELECT NVL( MAX (order_no), 0) +1 FROM order_info , --1

     'id2',

   SYSDATE);

 

행이 1건도 없는 경우 MAX함수의 결과는 null이 나옴

 

2) SEQUENCE 객체 생성사용

 

(2-1) SEQUENCE 생성

CREATE SEQUENCE order_seq

START WITH 5 --일련번호 시작 값 (기본 값1)

INCREMENT BY 2 --증가치 (기본 값1)

MAXVALUE 20 -- 최대값

MINVALUE 1 --최소값

CYCLE -- 값순환 : 최대값 다음 최소값으로 순환

 NOCACHE ;

 

 

(2-2) 일련번호 값 얻기

SELECT order_seq.NEXTVAL FROM dual;

 

(2-3) 일련번호값 조회

SELECT order_seq.CURRVAL FROM dual;

 

(2-4) SEQUENCE삭제

DROP SEQUENCE order_seq;

 

(2-5) SEQUENCE생성

CREATE SEQUENCE order_seq

START WITH 3; -- 3부터 시작

 

 

--id2번고객이 'C0001'상품을 2개 주문한다

INSERT INTO order_info(order_no, order_id, order_dt) VALUES ( 

   order_seq.NEXTVAL,

     'id2',

   SYSDATE);

 

INSERT INTO order_line (order_no, order_prod_no, order_quantity) VALUES (

order_seq.CURRVAL,

'C0001',

2);

 

---

 

[고객입장]

회원가입/ 로그인/ 로그아웃

 

주문자 아이디에 해당하는 주문전체조회 (주문번호, 일자, 상품번호, 수량)

주문자 아이디에 해당하는 주문번호의 주문 내역 

 

 

 

[관리자입장]

로그인/ 로그아웃

전체 주문 조회 (주문번호, 일자, 주문자아이디, 주문자이름, 상품번호, 상품이름, 수량)

일자구간 조회

상품별 조회

주문자별 조회