Oracle PL/SQL Tutorial/Table/Check
Содержание
- 1 A column with upper case constraint
- 2 Add condition constraint with "and" operator
- 3 Add constraint to ensure that value from one column is bigger than that from another
- 4 Adding a CHECK Constraint
- 5 check constraint: must be positive value
- 6 Check constraint: one column must less than another column
- 7 Check constraint with decode and nvl2
- 8 Check constraint with MOD function
- 9 Constraint with Two conditions
- 10 Create a table with check constraint
- 11 Must be upper case
- 12 Salary column value cannot be greater than 1000
- 13 Setting CHECK constraint for number type field
- 14 Setting the Regular expression check for varchar field
- 15 Use char function to build default column value
- 16 Use comparison operators with a CHECK constraint
- 17 Use decode() function in check constraints
- 18 Violate a ckeck constraint
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>