title: MySQL: Key field value unique to 2 tables
body: <p>I'm looking to have 3 tables in a MySQL database that control, say items for sale in an online shop. The 3 tables are as follows:</p>
  
  <ol>
  <li>Current items</li>
  <li>Discontinued items</li>
  <li>Invoicing</li>
  </ol>
  
  <p>I've split the items into 2 tables to prevent the website having to look up the entire current and discontinued items when listing the sale items on the site. The website only needs to list the current items so I think doing this will speed up performance as the site grows. The query to list stock will be run frequently.</p>
  
  <p>Once items are not longer for sale, they will be 'decanted' into the discontinued table and new items will be added to the current items table.</p>
  
  <p>Each invoice will refer to a unique key field of the items databases (current or discontinued) to identify what items were on that invoice.</p>
  
  <p>In order for this to work, when a new item is created and added to the current items table, the key field will need to be unique to both tables. Otherwise, the invoicing will potentially reference 2 items as it looks through both the current and discontinued tables to work out what items were on each invoice.</p>
  
  <p>Is there a way to assign a unique value to a field that is unique to 2 different tables?</p>
  
  <p>Many thanks in advance.</p>