Menu
Is there way in MySQL to have two unique keys and connect them somehow?
for example if i have the following table and 'title'and 'store' are a unique keys
id | category | title | price | store
1 | outdoors | fishing rod | 59.99 | wal-mart
2 | auto | Penzoil Oil | 9.99 | target
and i try to insert the following record. This new record would be ignored because the title is "fishing rod" AND the store is 'wal-mart' and there is an existing record with that title and store
| outdoors | fishing rod | 30.99 | wal-mart
but if i attempted to insert the following record it would be accepted because there isn't a record that exists with the title of "fishing rod" and store of "target"
| outdoors | fishing rod | 30.99 | target
is this possible with just MySQL?
277
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.
Creating Composite IndexCREATE TABLE table_name ( c1 data_type PRIMARY KEY, c2 data_type, c3 data_type, c4 data_type, INDEX index_name (c2,c3,c4) ); In the above statement, the composite index consists of three columns c2, c3, and c4.
Key Differences Between Primary key and Unique key: A table can have only one primary key whereas there can be multiple unique key on a table. A Clustered index automatically created when a primary key is defined whereas Unique key generates the non-clustered index.
A composite unique key is a unique key made up of a combination of columns. Oracle creates an index on the columns of a unique key, so a composite unique key can contain a maximum of 16 columns. To define a composite unique key, you must use table_constraint syntax rather than column_constraint syntax.
You can define an index on multiple columns, e.g.:
CREATE UNIQUE INDEX arbitrary_index_name ON table_name (title, store);
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
