constraint_all.txt

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

/* 
-- Following does not check for nulls in LAST_STOCK_DATE, because it's possible 
-- a product could be added to the list before it is received.
ALTER TABLE plsql101_product ADD ( 
     CONSTRAINT valid_price CHECK(
          product_price IS NOT NULL 
          AND
          product_price BETWEEN 0 AND 10000
          )
     )
;

ALTER TABLE plsql101_product ADD ( 
     CONSTRAINT positive_quantity CHECK(
          quantity_on_hand IS NOT NULL
          AND
          quantity_on_hand >=0
          )
     )
;
*/

/* ALTER TABLE plsql101_product ADD ( 
     CONSTRAINT reasonable_date CHECK(
          TO_CHAR(last_stock_date, 'YYYY-MM-DD') >= '2001-12-31'
          )
     )
;



ALTER TABLE plsql101_product ADD ( 
     CONSTRAINT reasonable_date CHECK(
          TO_CHAR(last_stock_date, 'YYYY-MM-DD') >= '2001-12-31'
          )
     )
;


INSERT INTO plsql101_product VALUES (
     'Small Widget', NULL, 1, '28-2月-02');

INSERT INTO plsql101_product VALUES (
     'Small Widget', 10001, 1, '28-2月-02');

INSERT INTO plsql101_product VALUES (
     'Small Widget', 1.95, -5, '28-2月-02');

INSERT INTO plsql101_product VALUES (
     'Small Widget', 1.95, 1, '30-10月-2001');


*/

-- Exercise: Adding constraint that checks values and prohobits nulls
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', 10, '28-2月-00', 'GA');




-- Exercise: Adding a check constraint that allows null values
ALTER TABLE plsql101_product ADD ( 
     CONSTRAINT reasonable_stock_date CHECK(
          TO_CHAR(last_stock_date, 'YYYY-MM-DD') >= '2001-12-31'
          )
     )
;

INSERT INTO plsql101_product VALUES (
     'Anodized Framifier', 49, 5, NULL)
;

INSERT INTO plsql101_product VALUES (
     'Spring-Loaded Pit Puller', 49, 5, '30-10月-01')
;