InnoDB one (or none) to one relationship problem.

Question

Hey,

I have two tables:

CREATE TABLE `Car` (
  `car_id` int(11) NOT NULL AUTO_INCREMENT,
  `car_name` varchar(25),
  PRIMARY KEY(`car_id`)
)
ENGINE=INNODB;

CREATE TABLE `Tire` (
  `tire_id` int(11) NOT NULL AUTO_INCREMENT,
  `tire_size` int(11),
  `car_id_FK` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY(`tire_id`),
  CONSTRAINT `Ref_Car_Has_tire` FOREIGN KEY (`car_id_FK`)
    REFERENCES `Car`(`car_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE=INNODB;

My problem is that a tire does not have to belong to a car, there could be extra tires simply sitting around. Attempting to create a tire without a proper car_id of course throws an error.

Now I could simply remove the reference but I'm sure there's a proper way of handling this situation.

Answer 1

I'm not sure right now if MySQL allows this, but I think the following could solve your problem:

`car_id_FK` int(11) DEFAULT NULL,