Home > DeveloperSection > Blogs > SQL Constraints

SQL Constraints


MYSQL Database  SQL 
Ratings:
0 Comment(s)
 148  View(s)
Rate this:

SQL Constraints


Constraints are used to apply some rules for the record in a table. If any rule is violated between constraint and data, then the action is aborted by the constraint. These constraints are specified when the table is created or after the table is created. Following is the syntax of constraint:

CREATE TABLE name_of_table

(

column_name1 data_type(size) name_of_table,

column_name2 data_type(size) name_of_table,

column_name3 data_type(size) name_of_table,

);

 

Following are the types of constraints:

·      PRIMARY KEY: This key is the combination of UNIQUE KEY and NULL KEY. This key ensures that a column must have a unique identity and it cannot be left blank.

·      UNIQUE: This key ensures each row must have unique value.

·      NOT NULL: This key indicates that a column cannot be left blank.

·     CHECK:  This key checks the column meets some specific condition.

·      FOREIGN KEY: It ensures referential integrity of the record in one table to match values in another table.

·      DEFAULT: This key specify a default value for a column.

NOT NULL

The NOT NULL constraint does not allow a column to accept NULL value. In other words, NOT NULL constraint enforces a column to always contain a value.  Following is the example of NOT NULL:

Example:

CREATE TABLE student_info

(

S_Id int NOT NULL,

Name varchar(255) NOT NULL,

Address varchar(255),

City varchar(255)

)

 

In the above example, student id and Name cannot be left blank where as address and city can be left blank.

 UNIQUE CONSTRAINTS

The UNIQUE constraint ensures that each record in a database table must be unique. The UNIQUE and PRIMARY KEY both ensures uniqueness for a column or a set of column. A PRIMARY KEY automatically has a UNIQUE constraint define on it.

The difference between PRIMARY and UNIQUE constraint is that a table can have many UNIQUE constraint, but only one PRIMARY KEY. Following is the example of UNIQUE constraint:

CREATE TABLE student_info

(

S_Id int NOT NULL,

Name varchar(255),

Address varchar(255),

City varchar(255),

UNIQUE (S_Id)

)

PRIMARY KEY CONSTRAINTS

The PRIMARY KEY uniquely identifies record in a table. PRIMARY KEY ensures that these records must be unique in a database table. Each table can only have ONE PRIMARY KEY and that column cannot be left blank or cannot contain NULL values. Following is the example of PRIMARY KEY constraint:

CREATE TABLE student_info

(

S_Id int PRIMARY K EY,

Name varchar(255),

Address varchar(255),

City varchar(255),

)

 

FOREIGN KEY CONSTRAINTS

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Let’s illustrate with the help of an example:

Student_info table:

studentId

Name

Address

12ST09

John

USA

12ST10

Royce

Argentina

12ST11

Mickel

London

12ST12

Tom

New york

12ST13

Hugh

Luanda

12ST14

Nolan

Paris

12ST15

Jennifer

USA

 

Marks table:

studentId

Contact   Marks

 12ST09

456788        54

 12ST10

457912        34

 12ST11

56276          45

 12ST12

2898938      43

 12ST13

2974789      37

 12ST14

232688        56

 12ST15

43762762    67

 

In the above example, two tables are given: student_info and marks

Studentid is PRIMARY KEY for student_info table and FOREIGN KEY for marks table.

The FOREIGN KEY prevents action that would destroy links between tables and also prevents invalid data from being inserted into the foreign key column.

CHECK CONSTRAINTS

The CHECK constraint ensures the limit on value range that can be placed in a column. When CHECK constraint is applied on single column it allows only certain values for this column. Following is the example of CHECK constraint:

CREATE TABLE student
(
S_Id int CHECK (S_Id>0),
name varchar(255) NOT NULL,
Address varchar(255),
City varchar(255),

)

 

DEFAULT CONSTRAINTS

DEFAULT constraints insert the default value into a column. The default value is added to all records, if no other value is specified.

CREATE TABLE student
(
S_Id int CHECK (S_Id>0),
name varchar(255) NOT NULL,
Address varchar(255),
City varchar(255) DEFAULT ‘Denmark’,

)

 

CREATE INDEX

CREATE INDEX is used to create an index.  An index is used to find the data quickly and efficiently.

Note: When updating a table with index, it takes more time then without index. So, only index need to be created on fields. Following is the example of CREATE INDEX:

CREATE INDEX SIndex

ON student (LastName)

 

DROP INDEX

Index, table and database can be removed using DROP index. Following is the example of DROP index:

DROP INDEX name_of_index  ON  name_of_table

 

ALTER TABLE

The ALTER statement is used to delete, add, update table. Following is an example of ALTER TABLE:

ALTER TABLE name_of_table

DROP COLUMN  name_of_column

 

AUTO INCREMENT

AUTO_INCREMENT generates unique value when a new record is inserted. Following is the example of AUTO INCREMENT:

CREATE TABLE Persons

(

ID int NOT NULL AUTO_INCREMENT,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

)


Don't want to miss updates? Please click the below button!

Follow MindStick