2 Unique FKs in single Table

[Manuel Maurer]

Hi,

I have an electronic board (pcb) which contains 2 sensors. I am trying to model that into my database:

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`,`wafer_id`),
UNIQUE KEY `id_UNIQUE` (`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
);

Each sensor comes from a wafer and is identified by the triplet wafer/line/column. Now I want to have a table assigning two sensors to the pcb:

CREATE TABLE `pcb` (
`id` bigint(19) unsigned NOT NULL AUTO_INCREMENT,
`barcode` varchar(45) NOT NULL,
`s1_id` bigint(19) unsigned NOT NULL,
`s2_id` bigint(19) unsigned NOT NULL,
PRIMARY KEY (`id`,`s1_id`,`s2_id`),
UNIQUE KEY `id_UNIQUE` (`id`),
UNIQUE KEY `barcode_UNIQUE` (`barcode`),
UNIQUE KEY `fk_pcb_sensor1_idx` (`s1_id`),
UNIQUE KEY `fk_pcb_sensor2_idx` (`s2_id`),
CONSTRAINT `fk_pcb_sensor1` FOREIGN KEY (`s1_id`) REFERENCES `sensor` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_pcb_sensor2` FOREIGN KEY (`s2_id`) REFERENCES `sensor` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

Now that's not even close to what I want ;-)

In principal I have only two requirements:
1) Every sensor can only be assigned to one pcb (can, not must!)
2) Every pcb contains two different sensors (and I need to know which sensor is on which position, S1 or S2)

Requirement 1 would be solvable by a foreign key in the sensor table, although I don't like unique columns that contain NULL.

For Requirement 2 I have no Idea at all how to solve this.

Any help is very appreciated!