Re: 2 Unique FKs in single Table
Posted by: Manuel Maurer
Date: August 08, 2016 02:54AM

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
)

Options: ReplyQuote


Subject
Written By
Posted
Re: 2 Unique FKs in single Table
August 08, 2016 02:54AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.