SQL Constraints are rules used to limit the type of data that can go into a table, to maintain the accuracy and integrity of the data inside table.
Constraints provide a standard mechanism to maintain the accuracy and integrity of the data inside a database table.
The CHECK constraint is used to restrict the values that can be placed in a column.
SQL CHECK Constraint must be specified as a logical expression that evaluated specific condition either TRUE or FALSE.
CREATE TABLE table_name( column_name datatype[(size)] [ NULL | NOT NULL ] CHECK (column_name condition), column_name datatype[(size)] [ NULL | NOT NULL ] CHECK (column_name condition), .... );
SQL> CREATE TABLE std_info( no NUMBER(3) PRIMARY KEY, stu_code VARCHAR(10) CHECK (stu_code like 'j%'), name VARCHAR(30) CHECK ( name = upper(name) ), city VARCHAR(30) CHECK (city IN ('DELHI','MUMBAI','BANGALORE','CENNAI')) scholarship NUMBER(5) CHECK (scholarship BETWEEN 5000 AND 20000) ); ------------------------ Table created.
SQL> INSERT INTO std_info VALUES (1,'j001', 'PRAYAG VERMA', 'CHENNAI', 8900); ----------------------- 1 row created.
We have created new
std_info table name with following check constraints:
stu_codecolumn must be start with the lower letter 'j'.
namecolumn must be capitalize.
citycolumn only allow 'Delhi','Mumbai','Bangalore','Chennai' as valid legitimate values.
scholarshipcolumn between 5000 and 20000.