Avoid INSERT when the combination of First name and Last already exists

newbie and appreciate your help.

So I have the below employee table with emp_id as primary and auto incremental. I would like to avoid users from inserting new record when the combination of first_name and last_name already exists.

I am connecting the DB to windows from (VB.net), ultimately I would like to display a message saying that the user's first and last name already exists. I could do it progrmaticly by vb code, however i am wondering if I can setup this check at the database level.

CREATE TABLE `employee` (
  `emp_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(20) NOT NULL,
  `middle_name` varchar(30) DEFAULT NULL,
  `last_name` varchar(20) NOT NULL,
  `date_of_birth` varchar(45) NOT NULL,
  `gender` char(6) NOT NULL,
  PRIMARY KEY (`emp_id'),
  UNIQUE KEY `employee_id_UNIQUE` (`emp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

CodePudding user response：

You can make constraints on sets of columns like this:

CREATE TABLE `employee` (
  `emp_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(20) NOT NULL,
  `middle_name` varchar(30) DEFAULT NULL,
  `last_name` varchar(20) NOT NULL,
  `date_of_birth` varchar(45) NOT NULL,
  `gender` char(6) NOT NULL,
  PRIMARY KEY (`emp_id'),
  UNIQUE KEY `employee_name_UNIQUE` (`first_name`, `last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

That means if the combination of first_name and last_name already exists in the database, if you try to insert a new row with the same values, it results in a duplicate key error.

You need to write code in your application to handle that error, and then display an appropriately helpful message to the user.