Adding Constraints

Add Primary Key Constraint

-- Add Primary Key Constraint

mysql> ALTER TABLE Employee

    -> ADD CONSTRAINT pk_employee PRIMARY KEY (EMPNO);

Query OK, 0 rows affected (1.65 sec)

-- verify primary key constraint

mysql> DESC Employee;

+------------+---------------+------+-----+---------+-------+

| Field      | Type          | Null | Key | Default | Extra |

+------------+---------------+------+-----+---------+-------+

| Emp_No      | int           | NO   | PRI | NULL    |       |

| Emp_Name     | varchar(255)  | YES  |     | NULL    |       |

| Designation       | varchar(255)  | YES  |     | NULL    |       |

| Designation_NO | int           | YES  |     | NULL    |       |

| SAL        | decimal(10,2) | YES  |     | NULL    |       |

| COMMISSION | decimal(10,2) | YES  |     | NULL    |       |

+------------+---------------+------+-----+---------+-------+

6 rows in set (0.00 sec)

mysql> INSERT INTO Employee (Emp_No, Emp_Name, Designation, Designation_No, SAL, COMMISSION)

    -> VALUES (1, 'Ranjan', 'Manager', NULL, 5000.00, 1000.00);

ERROR 1062 (23000): Duplicate entry '1' for key 'Employee.PRIMARY'

Since Emp_No field is the primary key it cannot have duplicate values, hence we see that the insert operation fails when provided with a duplicate value.

-- Add Not Null Constraints

mysql> ALTER TABLE Employee

    -> MODIFY Emp_Name VARCHAR(255) NOT NULL,

    -> MODIFY Designation VARCHAR(255) NOT NULL,

    -> MODIFY SAL DECIMAL(10, 2) NOT NULL;

Query OK, 0 rows affected (1.08 sec)

mysql> INSERT INTO Employee (Emp_No, Emp_Name, JOB, Designation_NO, SAL, COMMISSION)

    -> VALUES (4, 'Ranjan', 'Manager', NULL, 5000.00, 1000.00);

Query OK, 1 row affected (0.16 sec)

mysql> 

mysql> SELECT * FROM Employee;

+-------+---------------+---------+------------+---------+------------+

| Emp_No | Emp_Name         | Designation     |  Designation_No | SAL     | COMMISSION |

+-------+---------------+---------+------------+---------+------------+

|     1 | Kavana Shetty | Manager |       NULL | 5000.00 |    1000.00 |

|     4 | Ranjan        | Manager |       NULL | 5000.00 |    1000.00 |

+-------+---------------+---------+------------+---------+------------+

2 rows in set (0.00 sec)

mysql> INSERT INTO Employee (Emp_Name, Designation, Designation_No, SAL, COMMISSION)

    -> VALUES (NULL, 'Tester', NULL, 3500.00, NULL);

ERROR 1048 (23000): Column 'Emp_Name' cannot be null