Add condition constraint to date type column : Date « SQL Data Types « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. SQL Data Types
3. Date

SQL>
SQL>
SQL> CREATE TABLE product_order (
  2       product_name  VARCHAR2(25),
  3       salesperson   VARCHAR2(3),
  4       order_date DATE,
  5       quantity      NUMBER(4,2)
  6       );

Table created.

SQL>
SQL>
SQL> INSERT INTO product_order VALUES ('Product 1', 'CA', '14-JUL-03', 1);

1 row created.

SQL> INSERT INTO product_order VALUES ('Product 2', 'BB', '14-JUL-03', 75);

1 row created.

SQL> INSERT INTO product_order VALUES ('Product 3', 'GA', '14-JUL-03', 2);

1 row created.

SQL> INSERT INTO product_order VALUES ('Product 4', 'GA', '15-JUL-03', 8);

1 row created.

SQL> INSERT INTO product_order VALUES ('Product 5', 'LB', '15-JUL-03', 20);

1 row created.

SQL> INSERT INTO product_order VALUES ('Product 6', 'CA', '16-JUL-03', 5);

1 row created.

SQL> INSERT INTO product_order VALUES ('Product 7', 'CA', '17-JUL-03', 1);

1 row created.

SQL>
SQL>
SQL>
SQL> ALTER TABLE product_order ADD CONSTRAINT reasonable_date CHECK(
  2       order_date IS NOT NULL
  3       AND
  4       TO_CHAR(order_date, 'YYYY-MM-DD') >= '2000-06-30'
  5       );

Table altered.

SQL>
SQL>
SQL> drop table product_order;

Table dropped.