title: SQL Unique constraint across multiple tables
body: <p>I am trying to create a unique constraint across multiple tables.  I have found similar questions answered here but they don't quite capture the spirit of what I am trying to do.</p>
  
  <p>As an example, I have three tables, t_Analog, t_Discrete, t_Message</p>
  
  <pre><code>CREATE TABLE t_Analog(
      [AppName] [nvarchar](20) NOT NULL,
      [ItemName] [nvarchar](32) NOT NULL,
      [Value] [float] NOT NULL,
      CONSTRAINT [uc_t_Analog] UNIQUE(AppName, ItemName)
  )
  
  CREATE TABLE t_Discrete(
      [AppName] [nvarchar](20) NOT NULL,
      [ItemName] [nvarchar](32) NOT NULL,
      [Value] [bit] NOT NULL,
      CONSTRAINT [uc_t_Discrete] UNIQUE(AppName, ItemName)
  )
  
  CREATE TABLE t_Message(
      [AppName] [nvarchar](20) NOT NULL,
      [ItemName] [nvarchar](32) NOT NULL,
      [Value] [nvarchar](256) NOT NULL,
      CONSTRAINT [uc_t_Message] UNIQUE(AppName, ItemName)
  )
  </code></pre>
  
  <p>My goal is to make AppName and ItemName unique across all 3 tables.  For instance, an item name of Y in application X cannot exist in both analog and discrete tables.</p>
  
  <p>Please note that this example is contrived, the actual data for each Type is different and large enough to make combining tables and adding a Type column pretty ugly.</p>
  
  <p>If you have any suggestions on approaches to this, I would love to hear them!</p>
  
  <p><strong>---- BEGIN EDIT 2012-04-26 13:28 CST ----</strong></p>
  
  <p>Thank you all for your answers!</p>
  
  <p>It seems there may be cause to modify the schema of this database, and that is fine.</p>
  
  <p>Combining the tables into a single table is not really a viable option as there are on the order of 30 columns for each type that do not match (modifying these columns is, unfortunately, not an option). This could lead to large sections of columns not being used in each row, which seems like a bad idea.</p>
  
  <p>Adding a 4th table, like John Sikora and others mention, may be an option but I would like to verify this first.</p>
  
  <p>Modifying Schema to be:</p>
  
  <pre><code>CREATE TABLE t_AllItems(
      [id] [bigint] IDENTITY(1,1) NOT NULL,
      [itemType] [int] NOT NULL,
      [AppName] [nvarchar](20) NOT NULL,
      [ItemName] [nvarchar](32) NOT NULL,
      CONSTRAINT [pk_t_AllItems] PRIMARY KEY CLUSTERED ( [id] )
      CONSTRAINT [uc_t_AllItems] UNIQUE([id], [AppName], [ItemName])
  ) ON [PRIMARY]
  
  CREATE TABLE t_Analog(
      [itemId] [bigint] NOT NULL,
      [Value] [float] NOT NULL,
      FOREIGN KEY (itemId) REFERENCES t_AllItems(id)
  )
  
  CREATE TABLE t_Discrete(
      [itemId] [bigint] NOT NULL,
      [Value] [bit] NOT NULL,
      FOREIGN KEY (itemId) REFERENCES t_AllItems(id)
  )
  
  CREATE TABLE t_Message(
      [itemId] [bigint] NOT NULL,
      [Value] [nvarchar](256) NOT NULL,
      FOREIGN KEY (itemId) REFERENCES t_AllItems(id)
  )
  </code></pre>
  
  <p>I only have one question regarding this approach.  Does this enforce uniqueness across the sub tables?</p>
  
  <p>For instance, could there not exist an 'Item' that has 'id' 9 with tables t_Analog having 'itemId' of 9 with 'value' of 9.3 and, at the same time, t_Message have 'itemId' 9 with 'Value' of "foo"?</p>
  
  <p>I may not fully understand this extra table approach but I am not against it.</p>
  
  <p>Please correct me if I am wrong on this.</p>