Oracle PL/SQL/Constraints/Alter Constraints

Adding a check constraint that allows null values

   <source lang="sql">

SQL> SQL> SQL> CREATE TABLE product (

 2       product_name  VARCHAR2(25),
 3       product_price NUMBER(4,2),
 4       purchase_date DATE
 5       );

Table created. SQL> SQL> ALTER TABLE product ADD (

 2       CONSTRAINT reasonable_stock_date CHECK(
 3            TO_CHAR(purchase_date, "YYYY-MM-DD") >= "2001-12-31"
 4            )
 5       );

Table altered. SQL> SQL> INSERT INTO product VALUES ("S", 49, "30-DEC-01"); INSERT INTO product VALUES ("S", 49, "30-DEC-01")

ERROR at line 1: ORA-02290: check constraint (sqle.REASONABLE_STOCK_DATE) violated

SQL> SQL> drop table product; Table dropped. SQL> SQL>

</source>