MySQL enforce uniqueness across two columns?
I want to store three columns in a database, prohibiting duplicate entries:
( 'Name' , 'Location' , 'Data' )
Here's what I'm doing so far, but it only enforces uniqueness on Name.
CREATE TABLE IF NOT EXISTS SomeStuff (
Name VARCHAR(200),
Location VARC开发者_C百科HAR(200),
Data LONGTEXT,
UNIQUE INDEX (Name)
)
What I want to do is enforce unique combinations of Name and Location. i.e. if I have these entries to store, I want that all to be valid:
Toothbrush , Ohio , (DATA)
Bacon , Ohio , (DATA)
Sandwich , Kansas , (DATA)
Sandwich , Ohio , (DATA)
Bacon , Kansas , (DATA)
However, if I try to store this entry, I want that to be invalid:
Bacon , Ohio , (DIFFERENT_DATA) <--Database won't allow this
How do I structure a table definition to do that?
I'm periodically updating the (DATA) column using a line like this:
REPLACE INTO LicenseData (Name, Location, Data)
VALUES ( arg , arg , arg )
This was working before I had to keep track of Location, so right now I cannot store both ( Sandwich , Kansas )
and ( Sandwich , Ohio )
.
I know T-SQL better than MySql, but maybe you can do this?
CREATE TABLE IF NOT EXISTS SomeStuff (
Name VARCHAR(200),
Location VARCHAR(200),
Data LONGTEXT,
UNIQUE INDEX (Name, Location)
)
You should add a unique key for the pair Name, Location
, e.g. see here.
精彩评论