Re: 2 Unique FKs in single Table
Oh man, why didn't I see that. That is actually the easiest solution, thanks very much for the hint.
Not sure if I keep the enum or put position in a separate table, for the moment this is my solution:
CREATE TABLE `wafer` (
`id` BIGINT(19) UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
)
CREATE TABLE `pcb` (
`barcode` varchar(45) NOT NULL,
PRIMARY KEY (`barcode`)
)
CREATE TABLE `sensor` (
`id` bigint(19) unsigned NOT NULL AUTO_INCREMENT,
`col` smallint(6) NOT NULL,
`line` smallint(6) NOT NULL,
`wafer_id` bigint(19) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `sensor_UNIQUE` (`wafer_id`,`line`,`col`),
KEY `fk_sensor_wafer1_idx` (`wafer_id`),
CONSTRAINT `fk_sensor_wafer1` FOREIGN KEY (`wafer_id`) REFERENCES `wafer` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)
CREATE TABLE `sensor_pcb` (
`id` bigint(20) NOT NULL,
`pcb_barcode` varchar(45) NOT NULL,
`sensor_id` bigint(19) unsigned NOT NULL,
`position` enum('S1','S2') NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `sensor_id_UNIQUE` (`sensor_id`),
UNIQUE KEY `unique_sensor_pcb` (`pcb_barcode`,`position`),
KEY `fk_sensor_pcb_pcb1_idx` (`pcb_barcode`),
KEY `fk_sensor_pcb_sensor1_idx` (`sensor_id`),
CONSTRAINT `fk_sensor_pcb_pcb1` FOREIGN KEY (`pcb_barcode`) REFERENCES `pcb` (`barcode`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_sensor_pcb_sensor1` FOREIGN KEY (`sensor_id`) REFERENCES `sensor` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)