How to prevent adding duplicate values for two columns in SQL?-CodePudding

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?

CodePudding user response：

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?

CodePudding user response：

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.

CodePudding user response：

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.

CodePudding user response：

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)
)