SQL Constraints With Example

Constraints Definition:

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,

1. Column level constraints : Limits only column data.
2. Table level constraints : Limits whole table data.

The Constraints which can be applied on the table are.

1. NOT NULL
2. PRIMARY KEY
3. UNIQUE
4. DEFAULT
5. FOREIGN KEY
6. CHECK

1. NOT NULL Constraints

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.

SQL NOT NULL Constraint Rules

• A NULL values is different from a blank or zero.
• A NULL value can be inserted into the columns of any Datatype.

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

Syntax:

  							   
CREATE TABLE table_name(
    column_name datatype[(size)] NOT NULL,
    column_name datatype[(size)] NOT NULL,
    ....
);

Example 1:

 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.

Syntax:

  
  ALTER TABLE table_name
  MODIFY column_name datatype[(size)] NOT NULL;

Example 2:

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)

---

2. PRIMARY KEY

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,

• The NOT NULL attribute is automatic active.
• The data across the column must be unique.
• Defines column as a mandatory column.

Example: Defined at Column level

Syntax:

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.

Example: Defined at Table level

Syntax:

CREATE TABLE table_name(
    column_name datatype[(size)] [ NULL | NOT NULL ],
    column_name datatype[(size)] [ NULL | NOT NULL ],
    ...,
    PRIMARY KEY ( column_name, ... ),
    ...
);	

Example:

SQL> CREATE TABLE std_info(
    no NUMBER(3,0),
    name VARCHAR(30),
    address VARCHAR(70),
    contact_no VARCHAR(12),
    PRIMARY KEY(no)
);

-------------------------
Table created.

---

3. UNIQE Constraints

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

Difference Between Primary Key and Unique Key

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

Syntax:

CREATE TABLE table_name(
    column_name datatype[(size)] [ NULL | NOT NULL ] UNIQUE,
    column_name datatype[(size)] [ NULL | NOT NULL ] UNIQUE,
    ....
);

Example: defined at Column level

SQL> CREATE TABLE std_info(
    no NUMBER(3,0) PRIMARY KEY,
    name VARCHAR(30) UNIQUE,
    address VARCHAR(70),
    contact_no VARCHAR(12)
);

---------------------------
Table created.

Example: defined at Table level

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.

---

4. DEFAULT constraints:

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.

Syntax:

CREATE TABLE table_name(
    column_name datatype[(size)] [ NULL | NOT NULL ] DEFAULT default_value,
    column_name datatype[(size)] [ NULL | NOT NULL ] DEFAULT default_value,
    ....
);

Example: defined only at Column level

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.

---

5. FOREIGHN KEY

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.

Syntax:

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 ] 
        ],
    ....
);

Example:

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									

---

6. CHECK Constraints

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.

Syntax:

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),
    ....
);

Example:

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:

• Values inserted into stu_code column must be start with the lower letter 'j'.
• Values inserted into name column must be capitalize.
• Values inserted into city column only allow 'Delhi','Mumbai','Bangalore','Chennai' as valid legitimate values.
• Values inserted into scholarship column between 5000 and 20000.