title: How to create one-to-zero-or-one relationship programmatically - MySQL?
body: <p>I'm busy with a project and one of the stumbling blocks I've come across has been the following: </p>
  
  <p>I have a bookings table, which may or may not result in an invoice being issued (because of some irrelevant stuff such as cancellations). How would I enforce a one (on the bookings side) to zero-or-one (on the invoice side) relationship? Here's what I have thus far:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `booking` (    
     `booking_id` int(11) NOT NULL AUTO_INCREMENT,   
     `voucher_id` int(11) NOT NULL,  
     `pickup_date_time` datetime NOT NULL, ...  
     PRIMARY KEY (`booking_id`,`voucher_id`)  
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; 
  </code></pre>
  
  <p>And then, later on:</p>
  
  <pre><code>  CREATE TABLE IF NOT EXISTS `invoice` (  
    `booking_id` int(11) NOT NULL,  
    `voucher_id` int(11) NOT NULL,  
    `invoice_number` int(11) NOT NULL,  
    `paid` tinyint(1) NOT NULL,  
    PRIMARY KEY (`booking_id`,`voucher_id`),  
    UNIQUE KEY `invoice_number` (`invoice_number`),  
    KEY `voucher_id` (`voucher_id`)  
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; 
  </code></pre>
  
  <p><code>voucher_id</code> is just something else I use in the system. The <code>invoice_number</code> is also generated in the PHP, so this is irrelevant.</p>
  
  <p>Any help would be greatly appreciated!</p>