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