check.txt

orale培训教材包括了所有的sql说明和实例

--CHECK使用单一条件


CREATE TABLE sm_emp_check(
empid  VARCHAR2(3),
name   VARCHAR2(10),
salary NUMBER(8,2)  CHECK (salary<10000 ));--ok


INSERT INTO sm_emp_check VALUES('001', 'JACK', 100);
INSERT INTO sm_emp_check VALUES('002', 'JACK2', 101);

INSERT INTO sm_emp_check VALUES('003', 'JACK3', 10000);
ERROR 位于第 1 行:


CREATE TABLE sm_emp_check(
empid  VARCHAR2(3),
name   VARCHAR2(10),
salary NUMBER(8,2)  CHECK ( 100<salary<10000 ));
--error:100<salary<10000 实际是个复合条件。


DROP TABLE sm_emp_check;
CREATE TABLE sm_emp_check(
empid  VARCHAR2(3),
name   VARCHAR2(10),
salary NUMBER(8,2)  CHECK ( 100<salary  and salary<10000 ));
INSERT INTO sm_emp_check VALUES('001', 'JACK', 100);
INSERT INTO sm_emp_check VALUES('002', 'JACK2', 101);
INSERT INTO sm_emp_check VALUES('003', 'JACK3', 10000);

--NULL = SALAY
INSERT INTO sm_emp_check VALUES('003', 'JACK3', NULL);
DROP TABLE sm_emp_check;
CREATE TABLE sm_emp_check(
empid  VARCHAR2(3),
name   VARCHAR2(10),
salary NUMBER(8,2)  CHECK ( 100<salary  AND salary<10000 AND salary IS NOT NULL));
INSERT INTO sm_emp_check VALUES('003', 'JACK3', NULL);



ALTER TABLE plsql101_purchase ADD ( 
     CONSTRAINT reasonable_date CHECK(
          purchase_date IS NOT NULL
          AND
          TO_CHAR(purchase_date, 'YYYY-MM-DD') >= '2000-06-30'
          )
     )
;

INSERT INTO plsql101_purchase VALUES (
     'Small Widget', 'GA', '28-2月-00', 10);