TOPICS (Click to Navigate)
Thursday, 14 June 2018
CREATE TABLE statement in Oracle SQL with CHECK constraints
CREATE TABLE < tablename> (
CHECK ( <column_name> in ( val1, val2, val3, …, valn) )
CHECK ( <column_name> between <val1> and <val2> )
CHECK (<column_name> comparison_operator <value>))
There may be many constraints that are to be considered in modeling a real world entity. Some of them are just the type and size. Others may include various conditions for an attribute like set of permitted values, set of permitted range of values, values that are less than or greater than. The beauty is we can model all of the above said constraints as part of table creation so that the DBMS itself can take care if anything violated. We are using CHECK keyword to model these constraints.
Let us discuss this with an example below;
CREATE TABLE Furnitures (
Furniture_Name VARCHAR(25) NOT NULL,
Furniture_Type VARCHAR(20) NOT NULL,
Manufacturer VARCHAR(25) NOT NULL,
CONSTRAINT pk PRIMARY KEY (Furniture_ID),
CONSTRAINT ch1 CHECK (Quantity > 10),
CONSTRAINT ch2 CHECK (Furniture_Type in (‘Chair’, ‘Table’, ‘Cot’)
In the example given above, the CHECK constraint ch1 make sure that the value inserted for quantity attribute must be greater than 10. While you insert a record, like other basic constraints (type and size) the CHECK constraint also validated. Hence, no one can insert values that are not permitted for Quantity attribute.
Also, the CHECK constraint ch2 make sure that the permitted values for Furniture_Type are either ‘Chair’, or ‘Table’, or ‘Cot’. Other than these three values, no other values are permitted for Furniture_Type column.
Go to CREATE TABLE page
Go to SQL home page
Links to Natural Language Processing (NLP) and Data Science online lectures An introduction to Bayes theorem and Hidden Markov model ...
Advanced concepts in DBMS Advanced Database Topics (Click on the links to navigate) Advanced Concepts in D...
Set of solved exercises in Normalization / Normalization Solved Examples / How to find candidate keys, and primary keys in database? /...
Query Processing in DBMS / Steps involved in Query Processing in DBMS / How is a query gets processed in a Database Management System? / Q...