Oracle PL/SQL Tutorial/Table/Check

Материал из SQL эксперт
Перейти к: навигация, поиск

A column with upper case constraint

   <source lang="sql">

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></source>


Add condition constraint with "and" operator

   <source lang="sql">

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></source>


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

   <source lang="sql">

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.</source>


Adding a CHECK Constraint

   <source lang="sql">

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.</source>


check constraint: must be positive value

   <source lang="sql">

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.</source>


Check constraint: one column must less than another column

   <source lang="sql">

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></source>


Check constraint with decode and nvl2

   <source lang="sql">

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.</source>


Check constraint with MOD function

   <source lang="sql">

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></source>


Constraint with Two conditions

   <source lang="sql">

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.</source>


Create a table with check constraint

   <source lang="sql">

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.</source>


Must be upper case

   <source lang="sql">

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.</source>


Salary column value cannot be greater than 1000

   <source lang="sql">

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></source>


Setting CHECK constraint for number type field

   <source lang="sql">

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></source>


Setting the Regular expression check for varchar field

   <source lang="sql">

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></source>


Use char function to build default column value

   <source lang="sql">

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></source>


Use comparison operators with a CHECK constraint

   <source lang="sql">

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></source>


Use decode() function in check constraints

   <source lang="sql">

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.</source>


Violate a ckeck constraint

   <source lang="sql">

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.</source>