무결성 제약조건 예제

**테이블 : CUSTOMER
컬럼 : ID VARCHAR2(5),
          PWD VARCHAR2(5),
          NAME VARCHAR2(5)
제약조건 : ID - PK, PWD - NOT NULL

--테이블 만들기
CREATE TABLE CUSTOMER(
    ID varchar2(5),
    PWD VARCHAR2(5),
    NAME VARCHAR2(5)
);

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

ALTER TABLE customer
MODIFY pwd NOT NULL;

**테이블 : PRODUCT
컬럼 : prod_no VARCHAR2(5),
 prod_name VARCHAR2(20),
 prod_price NUMBER(6),
 prod_mf_dt DATE, --제조일자
 prod_detail VARCHAR2(4000)
제약조건 : prod_no - PK, prod_name - NOT NULL, prod_price - CHECK >= 0

CHECK 있어도 NULL값 저장 가능 

CREATE TABLE PRODUCT(
    prod_no varchar2(5),
    prod_name VARCHAR2(50),
    prod_price NUMBER(6),
    prod_mf_dt DATE,	-- 나중에 그냥 문자형으로
    prod_detail VARCHAR2(4000)
);
	
ALTER TABLE product
ADD CONSTRAINT prod_no_pk  PRIMARY KEY(prod_no);

ALTER TABLE product
MODIFY prod_name         NOT NULL;

ALTER TABLE product
ADD CONSTRAINT prod_price_ck CHECK (prod_price>=0);

위의 데이터 중복 제거

PK 2개로 복합으로 PK KEY 만들 수 있음
한 고객이 여러번 주문 가능( 원에 삼발이)
한 주문에는 한 고객 정보
주문상세에는 null은 있을 수 없다

 

**테이블 : ORDER_INFO (ORDER BY 있어서 테이블 이름 ORDER 불가능)

CREATE TABLE order_info(
    order_no number,
   order_id VARCHAR2(5),
   order_dt DATE DEFAULT SYSDATE
);

ALTER TABLE order_info
ADD CONSTRAINT order_info_no_pk PRIMARY KEY(order_no);

ALTER TABLE order_info
ADD CONSTRAINT order_id_fk FOREGIN KEY(order_id) REFERENCES customer(id);

**테이블 : ORDER_LINE

CREATE TABLE order_line(
    order_line_no number,
    order_prod_no VARCHAR2(5),
    order_quantity number(3)
);

--컬럼 레벨로는 복합키 설정 못함
CREATE TABLE order_line(
	order_line_no number PRIMARY KEY,
	order_prod_no VARCHAR2 PRIMARY KEY(5),
	order_quantity number(3)
); --(X)

ALTER TABLE order_line
ADD CONSTRAINT order_line_fk PRIMARY KEY(order_line_no, order_prod_no); --복합키설정

ALTER TABLE order_line
ADD CONSTRAINT order_prod_no_fk FOREGIN KEY(order_prod_no)
                                REFERENCES product(prod_no);

ALTER TABLE order_line
ADD CONSTRAINT order_line_no_fk FOREGIN KEY(order_line_no)
                                REFERENCES order_info(order_no);

 

INSERT INTO customer(id, pwd, name) VALUES('A', NULL, 'A');

제약조건, null값 불가능

INSERT INTO customer(id, pwd, name) VALUES('B', 'b', 'B'); --OK
				
--SQL 문법이 대소문자 구분못하는거지, 값은 구분함
INSERT INTO customer(id, pwd, name) VALUES('B', 'b11', 'B11'); 

INSERT INTO customer(id, pwd, name) VALUES('A', 'a', 'A'); --OK
INSERT INTO customer(id, pwd, name) VALUES('C', 'c', 'C'); --OK

cannot insert NULL 제약조건 이름이 노출되지 않음 제약조건 이름 굳이 부여할 필요없다, 네모는 제약조건 이름,

INSERT INTO product(prod_no, prod_name, prod_price) VALUES('C0001', '아메리카노', 1000);
INSERT INTO product(prod_no, prod_name, prod_price) VALUES('C0002', '아이스아메리카노', 1000);
INSERT INTO product(prod_no, prod_name, prod_price) VALUES('C0003', '라테', 1500);
INSERT INTO product(prod_no, prod_name, prod_price) VALUES('C0004', '아이스라테', 1500);
INSERT INTO product(prod_no, prod_name, prod_price) VALUES('C0005', '카푸치노', 1500);

INSERT INTO order_info(order_no, order_id) VALUES (1,'A'); --OK
INSERT INTO order_info(order_no, order_id) VALUES (1,'X'); -- HR.ORDER_INFO_NO_PK violated
                                                           --기본 키(PK) 무결성 제약 조건이 위반
INSERT INTO order_info( order_no, order_id) VALUES (2,'X'); -- HR.ORDER_ID_FK violated - parent key n
                                   --외래 키(Foreign Key) 관계를 가진 테이블 간에 무결성 제약 조건이 위반
INSERT INTO order_info(order_no, order_id) VALUES (2,'B'); --OK
INSERT INTO order_info(order_no, order_id) VALUES (3,'A'); --OK
SELECT * FROM order_info;

INSERT INTO order_line(order_line_no, order_prod_no, order_quantity) VALUES (0, 'C0001', 1); 
                       --(HR.ORDER_LINE_NO_FK) violated - parent key not found
INSERT INTO order_line(order_line_no, order_prod_no, order_quantity) VALUES (1, 'X', 1);
                       --(HR.ORDER_PROD_NO_FK) violated - parent key not found
					   
INSERT INTO order_line(order_line_no, order_prod_no, order_quantity) VALUES (1, 'C0001' , 2); --OK
INSERT INTO order_line(order_line_no, order_prod_no, order_quantity) VALUES (1, 'C0001' , 9); 
                       --(HR.ORDER_LINE_PK) violated
INSERT INTO order_line(order_line_no, order_prod_no, order_quantity) VALUES (2, NULL , 9); 
                       --cannot insert NULL into ("HR"."ORDER_LINE"."ORDER_PROD_NO")
INSERT INTO order_line(order_line_no, order_prod_no, order_quantity) VALUES (NULL, 'C0001' , 9); 
                       --cannot insert NULL into ("HR"."ORDER_LINE"."ORDER_LINE_NO")
INSERT INTO order_line(order_line_no, order_prod_no, order_quantity) VALUES ( 2, 'C0001', 4); --OK
INSERT INTO order_line(order_line_no, order_prod_no, order_quantity) VALUES ( 2, 'C0002', 1); --OK
INSERT INTO order_line(order_line_no, order_prod_no, order_quantity) VALUES ( 2, 'C0005', 1); --OK
INSERT INTO order_line(order_line_no, order_prod_no, order_quantity) VALUES ( 3, 'C0002', 1); --OK

--참조안된 상품을 수정한다
UPDATE product SET prod_no='F0001', prod_name='바나나' WHERE prod_no='C0003';  --OK 
SELECT * FROM product;

--참조된 상품을 수정한다
UPDATE product SET prod_no='X'  WHERE prod_no='C0001'; --ERROR
      --FK제약조건위배: integrity constraint (HR.ORDER_PROD_NO_FK) violated - child record found


UPDATE product SET prod_price=prod_price+100 WHERE prod_no='C0001'; --OK

--CHECK제약조건
UPDATE product SET prod_price=-1 WHERE prod_no='F0001';
      --CHECK제약조건위배 : check constraint (HR.PROD_PRICE_CK) violated

--참조안된 상품을 삭제한다
DELETE product WHERE prod_no='C0004';  --OK

--참조된 상품을 삭제한다
DELETE product WHERE prod_no='C0001';
      --FK제약조건위배:  integrity constraint (HR.ORDER_PROD_NO_FK) violated - child record found

 

제약조건정보 담고있는 딕셔너리/객체가 있는데, 딕셔너리용 뷰객체 : USER_CONSTRATINTS

SELECT * FROM user_constraints WHERE table_name = 'CUSTOMER';

결과