Unique row question

[Laphan]

Hi All

I know you can set a row to be unique, but I want expand on this in that I
want the DB schema to only disallow an entry if the row isn't unique across
3 fields. Is it possible?

My DDL for this table is as follows:

CREATE TABLE `WEBSTRINGS` (
`STRINGID` INT NOT NULL AUTO_INCREMENT,
`TOKENID` INT DEFAULT 0,
`LANGID` VARCHAR(30),
`STRINGTEXT` VARCHAR(255),
`PAGEID` SMALLINT DEFAULT 0,
`GUI` TINYINT UNSIGNED DEFAULT 0,

INDEX `indxLANGID` (`LANGID`),
INDEX `indxGUI` (`GUI`),
INDEX `indxTOKENID` (`TOKENID`),
INDEX `indxSTRINGID` (`STRINGID`),
PRIMARY KEY `PrimaryKey` (`STRINGID`)
# -- can use? UNIQUE `unqSTRINGID` (`LANGID,STRINGTEXT,GUI`)
);

The 'uniquness' I need is that if there is already an entry with the same
LANGID, STRINGTEXT and GUI then don't allow the INSERT entry to go in.

Please note that I already have this kind of checking in my front-end code,
its just for when values are being poked in directly, eg via SQLyog.

Thanks

Laphan

[steve]

"Laphan" wrote:

Hi All

I know you can set a row to be unique, but I want expand on this in
that I
want the DB schema to only disallow an entry if the row isn’t
unique across
3 fields. Is it possible?

My DDL for this table is as follows:

CREATE TABLE `WEBSTRINGS` (
`STRINGID` INT NOT NULL AUTO_INCREMENT,
`TOKENID` INT DEFAULT 0,
`LANGID` VARCHAR(30),
`STRINGTEXT` VARCHAR(255),
`PAGEID` SMALLINT DEFAULT 0,
`GUI` TINYINT UNSIGNED DEFAULT 0,

INDEX `indxLANGID` (`LANGID`),
INDEX `indxGUI` (`GUI`),
INDEX `indxTOKENID` (`TOKENID`),
INDEX `indxSTRINGID` (`STRINGID`),
PRIMARY KEY `PrimaryKey` (`STRINGID`)
# -- can use? UNIQUE `unqSTRINGID` (`LANGID,STRINGTEXT,GUI`)
);

The ’uniquness’ I need is that if there is already an
entry with the same
LANGID, STRINGTEXT and GUI then don’t allow the INSERT entry to
go in.

Please note that I already have this kind of checking in my front-end code,
its just for when values are being poked in directly, eg via SQLyog.
Thanks

Laphan

I think you have answered your own question. Create a unique index on
three fields. See exact syntax here:
http://dev.mysql.com/doc/mysql/en/CREATE_INDEX.html

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/mySQL-Unique...ict133020.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=444520