开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜