SQL : Constraints

I am going to explain about following constraints.

a.   not null

b.   default

c.   unique

d.   primary key

e.   foreign key

f.    check

not null constraint

If you want any field to not take null values, then make the field as not null at the time of table creation.

CREATE TABLE employee1(

  id int not null,

  name varchar(20) not null,

  city varchar(20)

);

Above statement creates an employee1 table. It is expecting id, name values to be not null,city can be null.

INSERT INTO employee1 VALUES(1, "Hari", null);

Above statement works fine, since city can be null;

INSERT INTO employee1 VALUES(1, null, null);

Above statement throws an error, since name can’t be null.

mysql> CREATE TABLE employee1(
    ->   id int not null,
    ->   name varchar(20) not null,
    ->   city varchar(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO employee1 VALUES(1, "Hari", null);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO employee1 VALUES(1, null, null);
ERROR 1048 (23000): Column 'name' cannot be null
mysql>
mysql> SELECT * FROM  employee1;
+----+------+------+
| id | name | city |
+----+------+------+
|  1 | Hari | NULL |
+----+------+------+
1 row in set (0.00 sec)

default constraint

By specifying default constraint, you can specify default value for a field. If you don’t provide any value for the field, then it takes default value.

mysql> CREATE TABLE employee2(
    ->   id int not null,
    ->   name varchar(20) not null,
    ->   city varchar(20) default "Bangalore"
    -> ); 
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO employee2 (id, name) VALUES (1, "Hari Krishna");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM employee2;
+----+--------------+-----------+
| id | name         | city      |
+----+--------------+-----------+
|  1 | Hari Krishna | Bangalore |
+----+--------------+-----------+
1 row in set (0.00 sec

unique constraint

Unique constraint makes sure that the field has unique value across the table.

CREATE TABLE employee3(

  id int not null,

  name varchar(20) not null,

  mailId varchar(50) unique

);

mysql> CREATE TABLE employee3(
    ->   id int not null,
    ->   name varchar(20) not null,
    ->   mailId varchar(50) unique
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO employee3 VALUES (1,"hari krishna", "abc@abc.com");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee3 VALUES (2, "Kiran", "abc@abc.com");
ERROR 1062 (23000): Duplicate entry 'abc@abc.com' for key 'mailId'

Observe above snippet, when I tried to insert same mailed in secod insert statement, it throws error ‘Duplicate entry 'abc@abc.com' for key 'mailId'’.

Remember one thing, unique key constraint field can take null values.

mysql> INSERT INTO employee3 VALUES (2, "Kiran", null);
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT INTO employee3 VALUES (2, "Kiran", null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from employee3;
+----+--------------+-------------+
| id | name         | mailId      |
+----+--------------+-------------+
|  1 | hari krishna | abc@abc.com |
|  2 | Kiran        | NULL        |
|  2 | Kiran        | NULL        |
|  2 | Kiran        | NULL        |
+----+--------------+-------------+
4 rows in set (0.00 sec)

Primary key constraint
Primary key constraint  = (unique + not null)

It is a combination of not null and unique. It makes sure that the field has some value (not null) and unique.

CREATE TABLE employee4(

  id int,

  name varchar(20) not null,

  mailId varchar(50) unique,

  PRIMARY KEY (id)

);

mysql> CREATE TABLE employee4(
    ->   id int,
    ->   name varchar(20) not null,
    ->   mailId varchar(50) unique,
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.01 sec)
 
mysql>
mysql>
mysql> describe employee4;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(20) | NO   |     | NULL    |       |
| mailId | varchar(50) | YES  | UNI | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

 

 

 

Previous                                                 Next                                                 Home