Oracle PL/SQL/Constraints/Constraint Check
Содержание
- 1 A check constraint can also be created or added as a table constraint.
- 2 add constraint for a column: check (gender in ("MALE", "FEMALE"))
- 3 Add constraint for date type column
- 4 Add constraint to ensure that value from one column is bigger than that from another
- 5 Add two conditions for one column
- 6 alter table dept add emp_count number, constraint must_be_between_3_8, check(emp_count between 3 and 8 OR emp_count = 0), deferrable initially deferred
- 7 Check date type column for nullable and value range
- 8 Check nullable and value range for one single column data
- 9 Constraint check with in function
- 10 Constraint check with "upper" function
- 11 Constriant: check value between 0 and 100
- 12 Could only contain three values
- 13 Follow the contraint
- 14 Must be upper case
- 15 Setting CHECK constraint for number type field
- 16 Setting the Check constraint for int value field during declaration
- 17 State a check constraint that check relation between two columns
- 18 Syntax for adding check constraint to a column
- 19 Use decode() function in check constraints
- 20 Violate a check constraint
- 21 Violate constraints
A check constraint can also be created or added as a table constraint.
SQL>
SQL> CREATE TABLE myTable
2 (policy_id NUMBER
3 ,holder_name VARCHAR2(40)
4 ,gender VARCHAR2(1) constraint chk_gender CHECK (gender in ("M","F")) --inline syntax
5 ,marital_status VARCHAR2(1)
6 ,date_of_birth DATE
7
8 ,constraint chk_marital CHECK (marital_status in ("S" ,"M" ,"D" ,"W"))
9 );
Table created.
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
add constraint for a column: check (gender in ("MALE", "FEMALE"))
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
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>
SQL>
SQL> alter table Employee
2 add ( gender varchar(10));
Table altered.
SQL>
SQL> select * from Employee;
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION GENDER
---- ---------- ---------- --------- --------- ---------- ---------- --------------- ----------
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>
SQL> alter table Employee
2 add constraint ck_gender
3 check (gender in ("MALE", "FEMALE"));
Table altered.
SQL>
SQL>
SQL>
SQL>
SQL> insert into Employee (ID, gender) values ("09", "UNKNOWN");
insert into Employee (ID, gender) values ("09", "UNKNOWN")
*
ERROR at line 1:
ORA-02290: check constraint (SYS.CK_GENDER) violated
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
Add constraint for date type column
SQL>
SQL> CREATE TABLE product (
2 product_name VARCHAR2(25),
3 product_price NUMBER(4,2),
4 last_stock_date date
5 );
Table created.
SQL>
SQL> ALTER TABLE product ADD (
2 CONSTRAINT reasonable_date CHECK(
3 TO_CHAR(last_stock_date, "YYYY-MM-DD") >= "2001-12-31"
4 )
5 );
Table altered.
SQL>
SQL>
SQL> drop table product;
Table dropped.
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.
Add two conditions for one column
SQL>
SQL> CREATE TABLE product (
2 product_name VARCHAR2(25),
3 product_price NUMBER(4,2),
4 quantity_on_hand number
5 );
Table created.
SQL>
SQL>
SQL> ALTER TABLE product ADD (
2 CONSTRAINT positive_quantity CHECK(
3 quantity_on_hand IS NOT NULL
4 AND
5 quantity_on_hand >=0
6 )
7 );
Table altered.
SQL>
SQL> drop table product;
Table dropped.
SQL>
alter table dept add emp_count number, constraint must_be_between_3_8, check(emp_count between 3 and 8 OR emp_count = 0), deferrable initially deferred
SQL>
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 );
SQL>
SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
SQL>
SQL> CREATE TABLE DEPT(
2 DEPTNO NUMBER(2),
3 DNAME VARCHAR2(14),
4 LOC VARCHAR2(13)
5 );
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
SQL>
SQL>
SQL> alter table dept
2 add emp_count number
3 constraint must_be_between_3_8
4 check(emp_count between 3 and 8 OR emp_count = 0)
5 deferrable initially deferred;
SQL>
SQL> update dept set emp_count = (select count(*) from emp where emp.deptno = dept.deptno )
2 /
SQL>
SQL> alter table dept
2 modify emp_count NOT NULL;
SQL>
SQL> create trigger emp_dept_cnt_trigger
2 after insert or update or delete on emp
3 for each row
4 begin
5 if ( updating and :old.deptno = :new.deptno )
6 then
7 return;
8 end if;
9 if ( inserting or updating )
10 then
11 update dept set emp_count = emp_count+1
12 where deptno = :new.deptno;
13 end if;
14 if ( updating or deleting )
15 then
16 update dept set emp_count = emp_count-1
17 where deptno = :old.deptno;
18 end if;
19 end;
20 /
SQL>
SQL> drop table emp;
SQL> drop table dept;
SQL>
Check date type column for nullable and value range
SQL>
SQL> CREATE TABLE purchase (
2 product_name VARCHAR2(25),
3 product_price NUMBER(4,2),
4 purchase_date DATE
5 );
Table created.
SQL>
SQL>
SQL>
SQL> ALTER TABLE purchase ADD (
2 CONSTRAINT reasonable_date CHECK(
3 purchase_date IS NOT NULL
4 AND
5 TO_CHAR(purchase_date, "YYYY-MM-DD") >= "2000-06-30"
6 )
7 );
Table altered.
SQL>
SQL> INSERT INTO purchase VALUES ("S", 1, "28-FEB-00");
INSERT INTO purchase VALUES ("S", 1, "28-FEB-00")
*
ERROR at line 1:
ORA-02290: check constraint (sqle.REASONABLE_DATE) violated
SQL>
SQL>
SQL> drop table purchase;
Table dropped.
SQL>
Check nullable and value range for one single column data
SQL>
SQL> CREATE TABLE product (
2 product_name VARCHAR2(25),
3 product_price NUMBER(4,2)
4 );
Table created.
SQL>
SQL> ALTER TABLE product ADD (
2 CONSTRAINT valid_price CHECK(
3 product_price IS NOT NULL
4 AND
5 product_price BETWEEN 0 AND 10000
6 )
7 );
Table altered.
SQL>
SQL> drop table product;
Table dropped.
Constraint check with in function
SQL>
SQL>
SQL> CREATE TABLE suppliers
2 ( supplier_id numeric(4),
3 supplier_name varchar2(50)
4 );
Table created.
SQL>
SQL> ALTER TABLE suppliers
2 add CONSTRAINT check_supplier_name
3 CHECK (supplier_name IN ("A", "B", "C"));
Table altered.
SQL>
SQL> ALTER TABLE suppliers
2 enable CONSTRAINT check_supplier_name;
Table altered.
SQL>
SQL>
SQL>
SQL> ALTER TABLE suppliers
2 disable CONSTRAINT check_supplier_name;
Table altered.
SQL>
SQL>
SQL> ALTER TABLE suppliers
2 drop CONSTRAINT check_supplier_name;
Table altered.
SQL>
SQL> drop table suppliers;
Table dropped.
SQL>
Constraint check with "upper" function
SQL>
SQL>
SQL> CREATE TABLE suppliers
2 ( supplier_id numeric(4),
3 supplier_name varchar2(50),
4 CONSTRAINT check_supplier_name
5 CHECK (supplier_name = upper(supplier_name))
6 );
Table created.
SQL>
SQL>
SQL>
SQL>
SQL> ALTER TABLE suppliers
2 enable CONSTRAINT check_supplier_name;
Table altered.
SQL>
SQL>
SQL>
SQL> ALTER TABLE suppliers
2 disable CONSTRAINT check_supplier_name;
Table altered.
SQL>
SQL>
SQL> ALTER TABLE suppliers
2 drop CONSTRAINT check_supplier_name;
Table altered.
SQL>
SQL> drop table suppliers;
Table dropped.
SQL>
Constriant: check value between 0 and 100
SQL> -- Check
SQL>
SQL> create table myTable (
2 a number check (a between 0 and 100),
3 b number
4 );
Table created.
SQL>
SQL> insert into myTable values(1, 3);
1 row created.
SQL> insert into myTable values(2000, 3);
insert into myTable values(2000, 3)
*
ERROR at line 1:
ORA-02290: check constraint (SYS.SYS_C004423) violated
SQL> insert into myTable values(2, 3);
1 row created.
SQL>
SQL> select * from myTable;
A B
---------- ----------
1 3
2 3
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>
Could only contain three values
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.
Follow the contraint
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
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>
SQL> alter table Employee
2 add ( gender varchar(10));
Table altered.
SQL>
SQL> select * from Employee;
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION GENDER
---- ---------- ---------- --------- --------- ---------- ---------- --------------- ----------
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>
SQL> alter table Employee
2 add constraint ck_gender
3 check (gender in ("MALE", "FEMALE"));
Table altered.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> update Employee set gender = "MALE" where mod(id,2) = 0;
4 rows updated.
SQL> update Employee set gender = "FEMALE" where mod(id,2) = 1;
4 rows updated.
SQL>
SQL> select ID, gender from Employee order by gender;
ID GENDER
---- ----------
07 FEMALE
03 FEMALE
05 FEMALE
01 FEMALE
06 MALE
08 MALE
02 MALE
04 MALE
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
SQL>
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.
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 Check constraint for int value field during declaration
SQL>
SQL> CREATE TABLE myTable (
2 Name VARCHAR(50) PRIMARY KEY NOT NULL,
3 PhoneNo VARCHAR(15) DEFAULT "Unknown Phone",
4 Age INT CHECK (Age BETWEEN 10 and 100));
Table created.
SQL>
SQL> drop table myTable;
Table dropped.
State a check constraint that check relation between two columns
SQL> -- State a check constraint that check the value of more than one column.
SQL> -- The following example makes sure that the value of begin_ is smaller than the value of end_.
SQL>
SQL> create table myTable(
2 begin_ number,
3 end_ number,
4 value_ number,
5 check (begin_ < end_)
6 );
Table created.
SQL>
SQL> insert into myTable values(1, 3, 4);
1 row created.
SQL> insert into myTable values(2000, 3, 5);
insert into myTable values(2000, 3, 5)
*
ERROR at line 1:
ORA-02290: check constraint (SYS.SYS_C004432) violated
SQL> insert into myTable values(2, 3 ,6);
1 row created.
SQL>
SQL> select * from myTable;
BEGIN_ END_ VALUE_
---------- ---------- ----------
1 3 4
2 3 6
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>
Syntax for adding check constraint to a column
SQL> --CREATE TABLE <table schema>
SQL> -- (key attribute NOT NULL,
SQL> -- attribute attribute type,
SQL> -- attribute attribute type
SQL> -- [CHECK (<attribute value> IN (set of values))]
SQL> -- PRIMARY KEY (key attribute));
SQL>
SQL> --Example:
SQL>
SQL> CREATE TABLE emp
2 (id VARCHAR2(10) NOT NULL,
3 name VARCHAR2(20),
4 address VARCHAR2(35),
5 emp_type VARCHAR2(8)
6 CHECK(emp_type IN ("Designer", "Worker")),
7 PRIMARY KEY (id));
Table created.
SQL>
SQL> drop table emp;
Table dropped.
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 check 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.
Violate constraints
SQL>
SQL> create table t
2 ( x int,
3 constraint x_greater_than_zero check ( x > 0 )
4 deferrable initially immediate
5 )
6 /
Table created.
SQL> insert into t values ( -1 );
insert into t values ( -1 )
*
ERROR at line 1:
ORA-02290: check constraint (SYS.X_GREATER_THAN_ZERO) violated
SQL>
SQL> set constraint x_greater_than_zero deferred;
Constraint set.
SQL> insert into t values ( -1 );
1 row created.
SQL>
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (SYS.X_GREATER_THAN_ZERO) violated
SQL> set constraint x_greater_than_zero deferred;
Constraint set.
SQL> insert into t values ( -1 );
1 row created.
SQL> set constraint x_greater_than_zero immediate;
set constraint x_greater_than_zero immediate
*
ERROR at line 1:
ORA-02290: check constraint (SYS.X_GREATER_THAN_ZERO) violated
SQL>
SQL> drop table t;
drop table t
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (SYS.X_GREATER_THAN_ZERO) violated