CODEWITHSUNDEEP
mysqlsql
Oyeniyi Abiola Peace
Oyeniyi Abiola Peace

Reputation: 421

Is there a way to use 2 columns to uniquely identify a row in MYSQL?

Consider the code below:

CREATE TABLE `user` (
      `firstname` VARCHAR(10) NOT NULL,
      `lastname` VARCHAR(10) NOT NULL

)

How do i make sure users can have the same firstname or lastname but not firstname and lastname together. I have done this kind of thing before but I lost the code and I think I need a composite key?

Upvotes: 0

Views: 79

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521639

You can try adding a unique constraint on the combination of first and last names:

ALTER TABLE user
ADD CONSTRAINT name_unique UNIQUE KEY (firstname, lastname);

For a possible alternative to this, assuming that your table does not have its own dedicated primary key column, you could try making the primary key the combination of first and last names:

CREATE TABLE `user` (
    firstname` VARCHAR(10) NOT NULL,
    lastname` VARCHAR(10) NOT NULL,
    ...
    PRIMARY KEY (firstname, lastname)
)

A primary key value can by definition only appear once in the table, so defining the primary key this way also would ensure uniqueness.

Upvotes: 4

Related Questions