Suppose I have a table:
CREATE TABLE `ml_vendor_refs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ml_id` int(11) NOT NULL,
`ven_id` int(11) NOT NULL,
`designator` int(4) NOT NULL,
`telco` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
As you can see, "id" is primary and AI. However, I want to be able to add several rows of 'designator' that my not duplicate based on the parent of 'ml_id' and 'telco'. For example:
"id" "ml_id" "ven_id" "designator" "telco"
"1" "5144" "3" "1" "0"
"2" "5144" "7" "2" "0"
"3" "5144" "44" "3" "0"
"4" "5144" "49" "4" "0"
for every instance of "ml_id" and t开发者_运维技巧he boolean "telco", I want to be able to have unique and ONLY unique "designators". Make sense?
Thanks
You can have a UNIQUE
index than spans multiple columns. The result would be a restriction such that the same value can appear in one column multiple times but the same combination of values cannot appear in the specified columns.
CREATE TABLE `ml_vendor_refs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ml_id` int(11) NOT NULL,
`ven_id` int(11) NOT NULL,
`designator` int(4) NOT NULL,
`telco` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `designators` (`designator`,`ml_id`,`telco`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
UNIQUE( ml_id, telco, designator )
You can have a unique pairing defined if you add a unique index on more than one column. For instance, you could add an index on ml_id, designator
that forces the pairings to be unique.
As far as I know, NULL
values are not subject to unique constraints.
精彩评论