Is there a way to make an entire MySQL row unique

Question

I have a table in MySQL with 30 columns and thousands of entries.
Is there a way I could make every row unique, that means if a row already exists, I should not be able to enter that row again.

I can't use primary or unique keys here, because individually every column might be repeated.
I want the row to be unique. for example:-There are is a table with

columns->name,age,height,weight.

In this column I can't make any one or two columns unique but I should not have two entries with all the same data.

Keep in mind [ERROR 1071](http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes/16820166#16820166) where combining so many columns can create issues. — roundar, Aug 13 '15 at 15:48

score 23 · Answer 1 · answered Jul 20 '11 at 12:15

23

You can make a unique index that includes all of the columns in your table

ALTER TABLE buyers ADD UNIQUE idx_row_unique(first_name,last_name,...);

This way you can keep a unique AUTO INCREMENT primary key for join purposes, while still ensuring that all of the data in your table is unique.

answered Jul 20 '11 at 12:15

Ryan Gross

6,423
2
32
44

1

The problem with this is that indexes have max lengths and very long indexes are inefficient. – John Foley Mar 28 '17 at 18:57

score 11 · Accepted Answer · edited May 23 '17 at 12:13

11

You need a composite primary key.

A composite primary key tells MySQL that you want your primary key to be a combination of fields.

More info here: Why use multiple columns as primary keys (composite primary key)

edited May 23 '17 at 12:13

Community

1
1

answered Jul 20 '11 at 12:15

Edgar Velasquez Lim

2,426
18
15

The OP says she cannot use Primary keys for this purpose – Raj More Jul 20 '11 at 12:18
1

@Raj More: From the question it looks like OP thinks that PK or UK only can apply to a single column at a time, so i think that this is a valid answer. – Anders Abel Jul 20 '11 at 12:22

score 5 · Answer 3 · answered Jul 20 '11 at 12:15

You may create UNIQUE key on all the columns, not individual unique keys on each column. This means that the combination of the values will be unique - exactly what you need. But please note, that if any column allows null value, if the column contains null value, it will be counted as unique, even if another row contains the same values, with null for the same value.

score 2 · Answer 4 · answered Apr 26 '19 at 15:48

2

You can hash the data and set the hash value as your PK, this will ensure that the rows are unique.

answered Apr 26 '19 at 15:48

jacky luster

53
7

score 2 · Answer 5 · answered Jul 20 '11 at 12:15

2

You can make a unique index on more than one column. Just put all the columns in the index. If the columns are large you may run into issues with the maximum length of an index, but try it first and see.

answered Jul 20 '11 at 12:15

Ariel

25,995
5
59
69

Is there a way to make an entire MySQL row unique

5 Answers5

Linked