Oracle PL/SQL Tutorial/Table/Check

Материал из SQL эксперт

Перейти к: навигация, поиск

Содержание

A column with upper case constraint

SQL>
SQL> CREATE TABLE departments
  2  ( location VARCHAR2(8) constraint D_LOC_NN
  3                         NOT NULL
  4                         constraint D_LOC_CHK
  5                         CHECK (location = upper(location))
  6  ) ;
TABLE created.
SQL>
SQL> DROP TABLE departments;
TABLE dropped.
SQL>
SQL>


Add condition constraint with "and" operator

SQL>
SQL>
SQL> CREATE TABLE product (
  2       product_name     VARCHAR2(25) PRIMARY KEY,
  3       product_price    NUMBER(4,2),
  4       quantity_on_hand NUMBER(5,0),
  5       last_stock_date  DATE
  6       );
TABLE created.
SQL>
SQL> INSERT INTO product VALUES ("Product 1", 99,  1,    "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Product 2", 75,  1000, "15-JAN-02");
1 row created.
SQL> INSERT INTO product VALUES ("Product 3", 50,  100,  "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Product 4", 25,  10000, NULL);
1 row created.
SQL> INSERT INTO product VALUES ("Product 5", 9.95,1234, "15-JAN-04");
1 row created.
SQL> INSERT INTO product VALUES ("Product 6", 45,  1,    TO_DATE("December 31, 2008, 11:30 P.M.","Month dd, YYYY, HH:MI P.M."));
1 row created.
SQL>
SQL>
SQL>
SQL> ALTER TABLE product ADD CONSTRAINT positive_quantity CHECK(
  2       quantity_on_hand IS NOT NULL
  3       AND
  4       quantity_on_hand >=0
  5       );
TABLE altered.
SQL>
SQL> DROP TABLE product;
TABLE dropped.
SQL>
SQL>


Add constraint to ensure that value from one column is bigger than that from another

SQL>
SQL>
SQL> CREATE TABLE salary
  2  ( grade      NUMBER(2)   constraint S_PK
  3                           PRIMARY KEY
  4  , lowerlimit NUMBER(6,2)
  5  , upperlimit NUMBER(6,2)
  6  , bonus      NUMBER(6,2)
  7  , constraint S_LO_UP_CHK CHECK (lowerlimit <= upperlimit)
  8  ) ;
TABLE created.
SQL>
SQL> INSERT INTO salary VALUES (1,  700,1200,   0);
1 row created.
SQL> INSERT INTO salary VALUES (2, 1201,1400,  50);
1 row created.
SQL> INSERT INTO salary VALUES (3, 1401,2000, 100);
1 row created.
SQL> INSERT INTO salary VALUES (4, 2001,3000, 200);
1 row created.
SQL> INSERT INTO salary VALUES (5, 3001,9999, 500);
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT * FROM salary
  2  WHERE  grade > 3;
     GRADE LOWERLIMIT UPPERLIMIT    BONUS
---------- ---------- ---------- --------
         4       2001       3000   200.00
         5       3001       9999   500.00
SQL> COL bonus FOR 9999.99
SQL> /
     GRADE LOWERLIMIT UPPERLIMIT    BONUS
---------- ---------- ---------- --------
         4       2001       3000   200.00
         5       3001       9999   500.00
SQL>
SQL>
SQL> DROP TABLE salary;
TABLE dropped.


Adding a CHECK Constraint

SQL>
SQL>
SQL>
SQL> -- create demo table
SQL> CREATE TABLE Employee(
  2    ID                 VARCHAR2(4 BYTE) ,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /
TABLE created.
SQL>
SQL> -- prepare data
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               VALUES ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
  3  /
1 row created.
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                VALUES("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                VALUES("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                VALUES("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
  3  /
1 row created.
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                VALUES("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                VALUES("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
  3  /
1 row created.
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                VALUES("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
  3  /
1 row created.
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                VALUES("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
  3  /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> SELECT * FROM Employee
  2  /
ID   FIRST_NAME           LAST_NAME            START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01   Jason                Martin               25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer
02   Alison               Mathews              21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester
03   James                Smith                12-DEC-78 15-MAR-90    6544.78 Vancouver  Tester
04   Celia                Rice                 24-OCT-82 21-APR-99    2344.78 Vancouver  Manager
05   Robert               Black                15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester
06   Linda                Green                30-JUL-87 04-JAN-96    4322.78 New York   Tester
07   David                Larry                31-DEC-90 12-FEB-98    7897.78 New York   Manager
08   James                Cat                  17-SEP-96 15-APR-02    1232.78 Vancouver  Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> ALTER TABLE employee
  2  ADD CONSTRAINT status_ck
  3  CHECK (city IN ("Vancouver", "Toronto", "New York"));
TABLE altered.
SQL>
SQL>
SQL> INSERT INTO Employee(City)
  2                VALUES("AAA")
  3  /
INSERT INTO Employee(City)
*
ERROR at line 1:
ORA-02290: CHECK constraint (sqle.STATUS_CK) violated
 
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> DROP TABLE Employee
  2  /
TABLE dropped.


check constraint: must be positive value

SQL>
SQL> CREATE TABLE salgrades
  2  ( lowerlimit NUMBER(6,2) constraint S_LOWER_NN
  3                           NOT NULL
  4                           constraint S_LOWER_CHK
  5                           CHECK (lowerlimit >= 0)
  6  , constraint S_LO_UP_CHK CHECK
  7                           (lowerlimit <= 10)
  8  ) ;
TABLE created.
SQL>
SQL>
SQL> DROP TABLE salgrades;
TABLE dropped.


Check constraint: one column must less than another column

SQL>
SQL> CREATE TABLE salgrades
  2  ( grade      NUMBER(2)   constraint S_PK
  3                           PRIMARY KEY
  4  , lowerlimit NUMBER(6,2) constraint S_LOWER_NN
  5                           NOT NULL
  6                           constraint S_LOWER_CHK
  7                           CHECK (lowerlimit >= 0)
  8  , upperlimit NUMBER(6,2) constraint S_UPPER_NN
  9                           NOT NULL
 10  , bonus      NUMBER(6,2) constraint S_BONUS_NN
 11                           NOT NULL
 12  , constraint S_LO_UP_CHK CHECK
 13                           (lowerlimit <= upperlimit)
 14  );
TABLE created.
SQL>
SQL>
SQL> DROP TABLE salgrades;
TABLE dropped.
SQL>
SQL>


Check constraint with decode and nvl2

SQL>
SQL> CREATE TABLE employees
  2  ( empno      NUMBER(4)    constraint E_PK
  3                            PRIMARY KEY
  4                            constraint E_EMPNO_CHK
  5                            CHECK (empno > 7000)
  6  , job        VARCHAR2(8)
  7  , comm       NUMBER(6,2)
  8  , constraint E_SALES_CHK  CHECK
  9                            (decode(job,"SALESREP",0,1)
 10                             + nvl2(comm,          1,0) = 1)
 11  ) ;
TABLE created.
SQL>
SQL> DROP TABLE employees;
TABLE dropped.


Check constraint with MOD function

SQL>
SQL> CREATE TABLE departments
  2  ( deptno NUMBER(2)     constraint D_PK
  3                         PRIMARY KEY
  4                         constraint D_DEPTNO_CHK
  5                         CHECK (mod(deptno,10) = 0)
  6  ) ;
TABLE created.
SQL>
SQL> DROP TABLE departments;
TABLE dropped.
SQL>


Constraint with Two conditions

SQL>
SQL>
SQL>
SQL>
SQL> -- create demo table
SQL> CREATE TABLE Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL PRIMARY KEY,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /
TABLE created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> SELECT * FROM Employee
  2  /
no rows selected
SQL>
SQL>
SQL> ALTER TABLE employee ADD (
  2       CONSTRAINT date_constraint CHECK(
  3            start_date IS NOT NULL
  4            AND
  5            start_date >= to_date("19870730","YYYYMMDD")
  6            )
  7       );
TABLE altered.
SQL>
SQL>
SQL>
SQL> -- prepare data
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               VALUES ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
  3  /
1 row created.
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                VALUES("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
  3  /
INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,               Salary,  City,       Description)
*
ERROR at line 1:
ORA-02290: CHECK constraint (sqle.DATE_CONSTRAINT) violated
 
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                VALUES("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
  3  /
INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,               Salary,  City,       Description)
*
ERROR at line 1:
ORA-02290: CHECK constraint (sqle.DATE_CONSTRAINT) violated
 
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                VALUES("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
  3  /
INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,               Salary,  City,       Description)
*
ERROR at line 1:
ORA-02290: CHECK constraint (sqle.DATE_CONSTRAINT) violated
 
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                VALUES("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
  3  /
INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,               Salary,  City,       Description)
*
ERROR at line 1:
ORA-02290: CHECK constraint (sqle.DATE_CONSTRAINT) violated
 
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                VALUES("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
  3  /
1 row created.
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                VALUES("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
  3  /
1 row created.
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                VALUES("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
  3  /
1 row created.
SQL>
SQL>
SQL> ALTER TABLE employee
  2  DROP CONSTRAINT date_constraint;
TABLE altered.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> DROP TABLE Employee
  2  /
TABLE dropped.


Create a table with check constraint

SQL>
SQL> CREATE TABLE employees
  2  ( empno      NUMBER(4)    constraint E_PK
  3                            PRIMARY KEY
  4                            constraint E_EMPNO_CHK
  5                            CHECK (empno > 7000)) ;
TABLE created.
SQL>
SQL> DROP TABLE employees;
TABLE dropped.


Must be upper case

SQL>
SQL>
SQL> CREATE TABLE courses
  2  ( code        VARCHAR2(6)  constraint C_PK           PRIMARY KEY
  3  , description VARCHAR2(30) constraint C_DESC_NN      NOT NULL
  4  , category    CHAR(3)      constraint C_TYPE_NN      NOT NULL
  5  , duration    NUMBER(2)    constraint C_DUR_NN       NOT NULL
  6  , constraint  C_CODE_CHK   CHECK                     (code = upper(code))
  7  , constraint  C_TYPE_CHK   CHECK                     (category IN ("GEN","BLD","DSG"))
  8  ) ;
TABLE created.
SQL>
SQL>
SQL> DROP TABLE courses;
TABLE dropped.


Salary column value cannot be greater than 1000

SQL>
SQL>
SQL> CREATE TABLE emp(
  2    EmpNo            NUMBER(10)    PRIMARY KEY,
  3    Name             VARCHAR2(40)  NOT NULL,
  4    DeptNo           NUMBER(2)     DEFAULT NULL,
  5    Salary           NUMBER(7,2)   CHECK (salary < 1000),
  6    Birth_Date       DATE,
  7    Soc_Sec_Num      CHAR(9)       UNIQUE);
TABLE created.
SQL>
SQL>
SQL>
SQL>
SQL> DROP TABLE emp;
TABLE dropped.
SQL>
SQL>


Setting CHECK constraint for number type field

SQL>
SQL>
SQL> CREATE TABLE myTable (
  2     Name    VARCHAR(50) PRIMARY KEY NOT NULL,
  3     PhoneNo VARCHAR(15) DEFAULT "Unknown Phone",
  4     Age     INT,
  5     CONSTRAINT CheckAge CHECK (Age BETWEEN 10 AND 100)
  6  );
TABLE created.
SQL>
SQL> DROP TABLE myTable;
TABLE dropped.
SQL>
SQL>


Setting the Regular expression check for varchar field

SQL>
SQL> CREATE TABLE myTable (
  2     Name    VARCHAR(50) PRIMARY KEY NOT NULL,
  3     PhoneNo VARCHAR(15) DEFAULT "Unknown Phone",
  4     SSN     VARCHAR(15) NOT NULL,
  5     CHECK (SSN LIKE "[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]"));
TABLE created.
SQL>
SQL>
SQL> DROP TABLE myTable;
TABLE dropped.
SQL>


Use char function to build default column value

SQL>
SQL> CREATE TABLE t
  2  ( a int,
  3    b varchar2(4000) DEFAULT rpad("*",4000,"*"),
  4    c varchar2(3000) DEFAULT rpad("*",3000,"*")
  5  )
  6  /
TABLE created.
SQL> INSERT INTO t (a) VALUES ( 1);
1 row created.
SQL> INSERT INTO t (a) VALUES ( 2);
1 row created.
SQL> INSERT INTO t (a) VALUES ( 3);
1 row created.
SQL>
SQL> DELETE FROM t WHERE a = 2 ;
1 row deleted.
SQL>
SQL> INSERT INTO t (a) VALUES ( 4);
1 row created.
 
SQL> DROP TABLE t;
TABLE dropped.
SQL>


Use comparison operators with a CHECK constraint

SQL>
SQL>
SQL>
SQL> -- create demo table
SQL> CREATE TABLE Employee(
  2    ID                 VARCHAR2(4 BYTE),
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /
TABLE created.
SQL>
SQL> -- prepare data
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               VALUES ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
  3  /
1 row created.
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                VALUES("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                VALUES("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                VALUES("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
  3  /
1 row created.
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                VALUES("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                VALUES("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
  3  /
1 row created.
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                VALUES("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
  3  /
1 row created.
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                VALUES("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
  3  /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> SELECT * FROM Employee
  2  /
ID   FIRST_NAME           LAST_NAME            START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01   Jason                Martin               25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer
02   Alison               Mathews              21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester
03   James                Smith                12-DEC-78 15-MAR-90    6544.78 Vancouver  Tester
04   Celia                Rice                 24-OCT-82 21-APR-99    2344.78 Vancouver  Manager
05   Robert               Black                15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester
06   Linda                Green                30-JUL-87 04-JAN-96    4322.78 New York   Tester
07   David                Larry                31-DEC-90 12-FEB-98    7897.78 New York   Manager
08   James                Cat                  17-SEP-96 15-APR-02    1232.78 Vancouver  Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> ALTER TABLE employee
  2  ADD CONSTRAINT salary_ck CHECK (salary > 0);
TABLE altered.
SQL>
SQL>
SQL> INSERT INTO Employee(Salary)
  2                VALUES(-1)
  3  /
INSERT INTO Employee(Salary)
*
ERROR at line 1:
ORA-02290: CHECK constraint (sqle.SALARY_CK) violated
 
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> DROP TABLE Employee
  2  /
TABLE dropped.
SQL>
SQL>
SQL>    SQL>


Use decode() function in check constraints

SQL>
SQL>
SQL> CREATE TABLE emp
  2  ( empno      NUMBER(4)    constraint E_PK
  3                            PRIMARY KEY
  4  , ename      VARCHAR2(8)
  5  , init       VARCHAR2(5)
  6  , job        VARCHAR2(8)
  7  , mgr        NUMBER(4)
  8  , bdate      DATE
  9  , sal        NUMBER(6,2)   constraint E_sal_NN NOT NULL
 10  , comm       NUMBER(6,2)
 11  , deptno     NUMBER(2)    DEFAULT 10
 12  , constraint E_SALES_CHK  CHECK(decode(job,"Tester",0,1)+ nvl2(comm,1,0) = 1)
 13  ) ;
TABLE created.
SQL>
SQL> CREATE INDEX year_sal_idx
  2  ON emp (12*sal + coalesce(comm,0));
INDEX created.
SQL>
SQL>
SQL> DROP INDEX year_sal_idx;
INDEX dropped.
SQL> DROP TABLE emp;
TABLE dropped.


Violate a ckeck constraint

SQL> CREATE TABLE myTable(
  2     col1 char(1)constraint check_col1 CHECK (col1 IN ("B","G","N"))
  3  );
TABLE created.
SQL>
SQL> INSERT INTO myTable VALUES ("B");
1 row created.
SQL> INSERT INTO myTable VALUES ("C");
INSERT INTO myTable VALUES ("C")
*
ERROR at line 1:
ORA-02290: CHECK constraint (sqle.CHECK_COL1)
violated
 
SQL> INSERT INTO myTable VALUES ("C")
  2
SQL>
SQL> DROP TABLE myTable;
TABLE dropped.