Menu
I have run into a situation where I want to ensure that a compound element of a table is unique. For example:
Table (
id char(36) primary key,
fieldA varChar(12) not null,
fieldB varChar(36) not null
)
I don't want fieldA and fieldB to be a compound primary key, since they change frequently, and 'id' is used as a reference throughout the system.
fieldA and fieldB are not unique in and of themselves, but their combinations need to be unique. So for example, {{1, Matt, Jones}, {2, David, Jones}, {3, Matt, Smith}} would be valid data, but {{1, Matt, Jones}, {2, Matt, Jones}} would not be.
356
A PRIMARY KEY constraint automatically has a UNIQUE constraint. However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
The syntax for creating a unique constraint using an ALTER TABLE statement in MySQL is: ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n); table_name.
Hence, if you want to have a more than one column or a set of columns with unique values, you cannot use the primary key constraint. Luckily, MySQL provides another kind of index called UNIQUE index that allows you to enforce the uniqueness of values in one or more columns.
We can define multiple Unique keys on a table where one or more columns combine to make a Unique key. According to ANSI, we can use multiple NULL values but in the SQL server, we can add only one NULL value.
You can add an UNIQUE key constraint on the two fields:
ALTER TABLE `table_name` ADD UNIQUE (
`fieldA`,
`fieldB`
);
Add a UNIQUE key to your table definition:
Table (
id char(36) primary key,
fieldA varChar(12) not null,
fieldB varChar(36) not null,
UNIQUE fieldA_fieldB (fieldA, fieldB)
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
