How do I specify unique constraint for multiple columns in MySQL

Question

I have a table   table votes       id      user      email      address      primary key id        Now I want to make the columns user  email  address unique  together    How do I do this in MySql   Of course the example is just    an example  So please don t worry about the semantics

User · Answer

If You are creating table in mysql then use following    create table package template mapping   mapping id  int 10  not null auto increment    template id int 10  NOT NULL   package id  int 10  NOT NULL   remark      varchar 100   primary key  mapping id    UNIQUE KEY template fun id  template id   package id

User · Answer

First get rid of existing duplicates  delete a from votes as a  votes as b where a id  lt  b id  and a user  lt   gt  b user and a email  lt   gt  b email  and a address  lt   gt  b address    Then add the unique constraint  ALTER TABLE votes ADD UNIQUE unique index user  email  address     Verify the constraint with  SHOW CREATE TABLE votes    Note that user  email  address will be considered unique if any of them has null value in it

User · Answer

MySql 5 or higher behaves like this  I ve just tested     you can define unique constraints involving nullable columns  Say you define a constraint unique  A  B  where A is not nullable but B is when evaluating such a constraint you can have  A  null  as many times you want  same A value   you can only have one  A  not null B  pair   Example  PRODUCT NAME  PRODUCT VERSION  glass   null  glass   null  wine   1  Now if you try to insert   wine  1  again it will report a constraint violation Hope this helps

User · Answer

ALTER TABLE  votes  ADD UNIQUE  unique index   user    email    address

User · Answer

For adding unique index following are required   1  table name 2  index name 3  columns on which you want to add index    ALTER TABLE   tablename   ADD UNIQUE index-name   column1    column2   column3       columnN      In your case we can create unique index as follows   ALTER TABLE  votes ADD  UNIQUE  lt votesuniqueindex gt    user    email   address

User · Answer

this tutorial works for me ALTER TABLE table name ADD CONSTRAINT constraint name UNIQUE  column1  column2      column n    https   www mysqltutorial org mysql-unique-constraint

User · Answer

I have a MySQL table   CREATE TABLE  content html       id  int 11  NOT NULL AUTO INCREMENT     id box elements  int 11  DEFAULT NULL     id router  int 11  DEFAULT NULL     content  mediumtext COLLATE utf8 czech ci NOT NULL    PRIMARY KEY   id      UNIQUE KEY  id box elements    id box elements   id router        and the UNIQUE KEY works just as expected  it allows multiple NULL rows of id box elements and id router    I am running MySQL 5 1 42  so probably there was some update on the issue discussed above  Fortunately it works and hopefully it will stay that way

User · Answer

Multi column unique indexes do not work in MySQL if you have a NULL value in row as MySQL treats NULL as a unique value and at least currently has no logic to work around it in multi-column indexes  Yes the behavior is insane  because it limits a lot of legitimate applications of multi-column indexes  but it is what it is    As of yet  it is a bug that has been stamped with  will not fix  on the MySQL bug-track

User · Answer

I do it like this   CREATE UNIQUE INDEX index name ON TableName  Column1  Column2  Column3     My convention for a unique index name is TableName Column1 Column2 Column3 uindex

User · Answer

Have you tried this     UNIQUE KEY  thekey    user   email   address

User · Answer

You can add multiple-column unique indexes via phpMyAdmin   I tested in version 4 0 4   Navigate to the structure page for your target table  Add a unique index to one of the columns  Expand the Indexes list on the bottom of the structure page to see the unique index you just added  Click the edit icon  and in the following dialog you can add additional columns to that unique index

User · Answer

If you want to avoid duplicates in future  Create another column say id2    UPDATE tablename SET id2   id    Now add the unique on two columns   alter table tablename add unique index columnname  id2

User · Answer

This works for mysql version 5 5 32  ALTER TABLE   tablename  ADD UNIQUE   column1    column2

User · Answer

For PostgreSQL    It didn t work for me with index  it gave me an error  so I did this   alter table table name add unique column name 1 column name 2     PostgreSQL gave unique index its own name  I guess you can change the name of index in the options for the table  if it is needed to be changed

[mysql] How do I specify unique constraint for multiple columns in MySQL?

Examples related to mysql

Examples related to unique-constraint

Examples related to composite-key

Examples related to database-table