Description
By the SQL CHECK Constraint own conditions can be applied on a column. And if needed to define a column with check constraint then that check constraint column cannot accept the values that are not satisfying the condition.
Syntax
The syntax for SQL Check Constraint is as follows
Create table <table_name>(<column_name1>datatype (size),<column_name2>datatype(size),<column_name3>datatype(size) constraint constraint name constraint type);
Table name => The accurate table in the database.
Column name => The operation that can be performed on a column.
Examples
The below example shows the execution of SQL CHECK Constraint.
Method-1: Applying check constraint on salary column.
[c]sql> CREATE TABLE Employee(empno number(4),ename varchar2(20),salary number(7,2) constraint employee_sal_ck check(salinsert into employee values (1001,'Jack',8000);
Query OK, 1 row affected (0.11 sec)
sql> insert into employee values (1002,'Mack',null);
Query OK, 1 row affected (0.05 sec)
sql> insert into employee values (1003,'James',12000);
Error(5200):Salary values for empno(1003) is greater then the assigned value
sql> select * from employee;
+---------+---------+--------+
| empno | ename | salary |
+---------+------+-----------+
| 1 | Jack | 8000 |
| 2 | mack | null |
+---------+------+-----------+
2 rows in set (0.00 sec)[/c]
Method-2: Applying check constraint on empno column.
[c]sql> CREATE TABLE Employees(empno number(4)constraint employees_empno_ck check(empnoinsert into employees values (1,'Jack',8000);
Query OK, 1 row affected (0.11 sec)
sql> insert into employees values (2,'Mack',9000);
Query OK, 1 row affected (0.05 sec)
sql> insert into employees values (3,'Maddi',10000);
Query OK, 1 row affected (0.05 sec)
sql> insert into employees values (12,'James',12000);
Error(5200):empno values for empno(12) is greater then the assigned value
sql> select * from employees;
+---------+---------+--------+
| empno | ename | salary |
+---------+------+-----------+
| 1 | Jack | 8000 |
| 2 | Mack | 9000 |
| 3 | Maddi | 10000 |
+---------+------+-----------+
3 rows in set (0.00 sec)
[/c]
Here in the above example, in method:1 and method:2 the column name salary and empno is assigned a condition i.e, salary<10000 and empno<12. Then while assigning the value check condition will check the condition and if the salary is null then the check constraint will not check the condition, and if the salary greater then the assigned condition the oracle server execute the logic and null value will be placed in the table.
Key Points
- SQL CHECK Constraint - In SQL CHECK Constraint own conditions can be applied on a column.