Question 113402 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to prevent adding duplicate values for two columns in SQL?

Essentially, I am wondering if there is a way to prevent adding duplicates for two columns. Consider a table with three columns, X, Y, and Z. I would like to be able to add entries with the same X value into the table, and I would like to be able to add entries with the same Y value into the table. However, I want to prevent adding a duplicate item if it has the same X value and the same Y value as something in the table.

Say the table looks like this:

X Y Z
100 10 "test"
200 20 "test1"

I would like to be able to add (100, 20, "Yes") to the table. I would also like to be able to add (200, 10, "Yes") to the table. I would not like to be able to add either (100, 10 "no") or (200,20,"no") to the table.

Is there a way to achieve this upon creation of a MySQL table?

Tags:

4

Answers


  1. 0

    I did not understand your problem very well, but knowing that for the selection of data you can use the keyword DISTINCT in the query, then you can insert the data discarding the duplicates (i.e. those listed by the dbms at the start of the selection query without the duplicates).

    Does it works?

    Login or Signup to reply.
  2. 0

    You should make column x and y combined the primary key, this will prevent anyone from adding duplicate x and y combinations to your table.

    Primary Keys must be unique.

    Login or Signup to reply.
  3. 0

    You can create a multicolumn unique constraint:

    create table mysterytablename (
    x int,
    y int,
    z text,
    unique xy_constraint (x,y)
)

    Or as others suggest, make it a primary key:

    create table mysterytablename (
    x int,
    y int,
    z text,
    primary key (x,y)
)

    Both have potential drawbacks; a primary key will not allow any of its columns to be null, while a unique key will allow duplicates if any of the columns are null. You can prevent that using generated columns that will be always not null and distinguish between null and any other possible value:

    create table mysterytablename (
    x int,
    y int,
    z text,
    x_unique varchar(12) as (if(x is null,'',concat('*',x))),
    y_unique varchar(12) as (if(y is null,'',concat('*',y))),
    unique xy_constraint (x_unique,y_unique)
)
    Login or Signup to reply.
  4. 0

    You can ALTER table and use composite unique index:

    ALTER TABLE tablename ADD UNIQUE KEY `uidx` (`X`, `Y`);

    Keep in mind that this will allow rows with NULL values, so

    INSERT INTO tablename(`X`, `Y`, `Z`) VALUES(100, NULL, "test")

    will create new record.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search