SQL dont allow duplicate records in table

Question

How do I make it not add duplicates? I want to make it check by all other columns beside ID.

I want this to be invalid.

ID    col1    col2   col3

1    first   middle  last   


ID    col1     col2   col3

2    first   middle  last  

I want this to be valid.

ID    col1    col2   col3

1    first   middle  last   


ID    col1     col2   col3

2    first    last   middle   

Answer 1

You need to use a composite UNIQUE INDEX on all three columns. See this example table definition:

CREATE TABLE example (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    col1 VARCHAR(32) NOT NULL,
    col2 VARCHAR(32) NOT NULL,
    col3 VARCHAR(32) NOT NULL,
    UNIQUE(col1, col2, col3)
);

-- expected to be valid
INSERT INTO example (ID, col1, col2, col3) VALUES
    (NULL, 'first', 'middle', 'last'),
    (NULL, 'first', 'last', 'middle');

-- expected to be invalid
INSERT INTO example (ID, col1, col2, col3) VALUES
    (NULL, 'first', 'middle', 'last'),
    (NULL, 'first', 'middle', 'last');

DEMO @ SQL Fiddle

Answer 2

1. Adding UNIQUE key over all the other columns is a possible solution.
2. Another solution would be software dependent.
   • You should first lock your table;
   • then check for the existence of that line with a where clause for all the columns;
   • insert the data if the above returns zero lines.
   • Or, if using InnoDB you might take a look at https://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html