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.
SQL Constraints are part of a database schema definition.
SQL Constraints are help us to enter limited fixed size data. For example postal code India country size six digit no below or no more then six digit
Constraints can be divided into two types,
The Constraints which can be applied on the table are.
A Not Null constraint ensures that the column in the table cannot have Null values, however it may have duplicate values.
This means if NOT NULL
constraint is applied on a column then you cannot insert a new row in the table without adding a non-NULL
value for that column.
Datatype | Action | Values |
---|---|---|
NUMBER | Error Produce | blank, space, special character, NULL |
NUMBER | Allow Only | O or 1 to 9 |
VARCHAR2 | Error Produce | blank, space, special character, NULL |
VARCHAR2 | blank | NULL, space, special character |
CREATE TABLE table_name( column_name datatype[(size)] NOT NULL, column_name datatype[(size)] NOT NULL, .... );
SQL> CREATE TABLE std_info( no NUMBER(3,0) PRIMARY KEY, name VARCHAR(30) NOT NULL, address VARCHAR(70) ); ----------------------------- Table created. -----------------------------
Now, let's Describe(view) the table std_info
we have just created
SQL> DESCRIBE std_info; Name Null? Type ---------------------------- --------- ---------------------------- NO NOT NULL NUMBER(3) NAME NOT NULL VARCHAR2(30) ADDRESS VARCHAR2(70)
ADD NOT NULL constraint (ALTER TABLE)
ALTER TABLE statement to add NOT NULL constraint in existing table column.
ALTER TABLE table_name MODIFY column_name datatype[(size)] NOT NULL;
SQL> ALTER TABLE std_info MODIFY address VARCHAR2(70) NOT NULL; -------------- Table altered.
Now, let's Describe(view) the table std_info
we have just created
SQL> DESCRIBE std_info; Name Null? Type ---------------------------- --------- ---------------------------- NO NOT NULL NUMBER(3) NAME NOT NULL VARCHAR2(30) ADDRESS NOT NULL VARCHAR2(70)
A PRIMARY KEY is a combination of a NOT NULL
and UNIQUE
. A Primary key constraint is applied for uniquely identifying rows in a table.
It cannot contain Null values
and rest of table data should be unique
.
While creating a table if we do not specify a name to the constraint, sql server automatically assigns a name to the constraint.
A PRIMARY KEY must contain unique
value and it must not contain null
value. Usually Primary Key is used to index the data inside the table.
SQL Primary Key in a table have following three special attributes,
CREATE TABLE table_name( column_name datatype[(size)] [ NULL | NOT NULL ] PRIMARY KEY, column_name datatype[(size)] [ NULL | NOT NULL ] PRIMARY KEY, .... );
SQL> CREATE TABLE std_info( no NUMBER(3,0) PRIMARY KEY, name VARCHAR(30), address VARCHAR(70), contact_no VARCHAR(12) ); --------------------------- Table created.
CREATE TABLE table_name( column_name datatype[(size)] [ NULL | NOT NULL ], column_name datatype[(size)] [ NULL | NOT NULL ], ..., PRIMARY KEY ( column_name, ... ), ... );
SQL> CREATE TABLE std_info( no NUMBER(3,0), name VARCHAR(30), address VARCHAR(70), contact_no VARCHAR(12), PRIMARY KEY(no) ); ------------------------- Table created.
The UNIQUE Constraint prevents having of two identical values in a column records.
A unique constraint is similar to Primary key except that it can have null values unless specified not null.
Ensures that all values in a column are different
Primary Key | Unique Key |
---|---|
Primary Key does not allow NULL(blank) values. | Whereas Unique key allow NULL(blank) values. | PRIMARY KEY = UNIQUE + NOT NULL | UNIQUE KEY = UNIQUE + NULL |
CREATE TABLE table_name( column_name datatype[(size)] [ NULL | NOT NULL ] UNIQUE, column_name datatype[(size)] [ NULL | NOT NULL ] UNIQUE, .... );
SQL> CREATE TABLE std_info( no NUMBER(3,0) PRIMARY KEY, name VARCHAR(30) UNIQUE, address VARCHAR(70), contact_no VARCHAR(12) ); --------------------------- Table created.
CREATE TABLE table_name( column_name datatype[(size)] [ NULL | NOT NULL ], column_name datatype[(size)] [ NULL | NOT NULL ], ..., UNIQUE ( column_name, ... ), ... );
SQL> CREATE TABLE std_info( no NUMBER(3,0) PRIMARY KEY, name VARCHAR(30), address VARCHAR(70), contact_no VARCHAR(12), UNIQUE(name) ); -------------------------------- Table created.
This constraint specifies a default value
for the column when no value is specified by the user.
SQL DEFAULT constraint
specified only at column level.
A column default
is some value that will be inserted in the column by the database engine when an INSERT
statement doesn't explicitly assign a particular value.
CREATE TABLE table_name( column_name datatype[(size)] [ NULL | NOT NULL ] DEFAULT default_value, column_name datatype[(size)] [ NULL | NOT NULL ] DEFAULT default_value, .... );
SQL> CREATE TABLE std_info( no NUMBER(3,0) PRIMARY KEY, name VARCHAR(30) NOT NULL, std VARCHAR(18) DEFAULT 'M.Sc.(CS)', fees_pay NUMBER(5) DEFAULT 2000 ); ------------------------------------- Table created.
A Foreign key
is a field which can uniquely identify each row in a another table. And this constraint is used to specify a field as Foreign key
.
A Foreign Key
Constraint is used to establish a relationship between two tables where one column is a Primary Key
of the table and the other column from other table is referenced to the Primary Key
column. A Foreign Key
column can also have reference to Unique Key
column of another table.
CREATE TABLE table_name( column_name datatype[(size)] [ NULL | NOT NULL ] REFERENCES another_table_name(column_name) [ ON UPDATE | ON DELETE [ NO ACTION | SET NULL | SET DEFAULT | CASCADE ] ], .... );
SQL> CREATE TABLE emp_info( no NUMBER(3,0) PRIMARY KEY, name VARCHAR(30), address VARCHAR(70), contact_no NUMBER(12,0) ); ------------------------ Table created.
SQL> CREATE TABLE emp_salary( no NUMBER(3,0) PRIMARY KEY, users_no NUMBER(3,0) REFERENCES emp_info(no), salary NUMBER(12) ); ---------------------------- Table created
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_code
column must be start with the lower letter 'j'.name
column must be capitalize.city
column only allow 'Delhi','Mumbai','Bangalore','Chennai' as valid legitimate values.scholarship
column between 5000 and 20000.