SQL Unique constraint across multiple tables

by

Add a 4th table specifically for these values you want to be unique then link these keys from this table into the others using a one to many relationship.
For example you will have the unique table with an ID, AppName and ItemName to make up its 3 columns. Then have this table link to the others.

For how to do this here is a good example
Create a one to many relationship using SQL Server

EDIT: This is what I would do but considering your server needs you can change what is needed:

CREATE TABLE AllItems(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [itemType] [int] NOT NULL,
    [AppName] [nvarchar](20) NOT NULL,
    [ItemName] [nvarchar](32) NOT NULL,
    CONSTRAINT [pk_AllItems] PRIMARY KEY CLUSTERED ( [id] ASC )
) ON [PRIMARY]

CREATE TABLE Analog(
    [itemId] [int] NOT NULL,
    [Value] [float] NOT NULL
)

CREATE TABLE Discrete(
    [itemId] [int] NOT NULL,
    [Value] [bit] NOT NULL
)

CREATE TABLE Message(
    [itemId] [bigint] NOT NULL,
    [Value] [nvarchar](256) NOT NULL
)

ALTER TABLE [Analog] WITH CHECK 
    ADD CONSTRAINT [FK_Analog_AllItems] FOREIGN KEY([itemId])
REFERENCES [AllItems] ([id])
GO
ALTER TABLE [Analog] CHECK CONSTRAINT [FK_Analog_AllItems]
GO

ALTER TABLE [Discrete] WITH CHECK 
    ADD CONSTRAINT [FK_Discrete_AllItems] FOREIGN KEY([itemId])
REFERENCES [AllItems] ([id])
GO
ALTER TABLE [Discrete] CHECK CONSTRAINT [FK_Discrete_AllItems]
GO

ALTER TABLE [Message] WITH CHECK 
    ADD CONSTRAINT [FK_Message_AllItems] FOREIGN KEY([itemId])
REFERENCES [AllItems] ([id])
GO
ALTER TABLE [Message] CHECK CONSTRAINT [FK_Message_AllItems]
GO

From what I can tell your syntax is fine, I simply changed it to this way simply because I am more familiar with it but either should work.

More Related Contents:

  1. How can you represent inheritance in a database?
  2. What are the best practices for using a GUID as a primary key, specifically regarding performance? [closed]
  3. Use float or decimal for accounting application dollar amount?
  4. How to create materialized views in SQL Server?
  5. When/Why to use Cascading in SQL Server?
  6. What columns generally make good indexes?
  7. What’s the longest possible worldwide phone number I should consider in SQL varchar(length) for phone
  8. SQL Server: the maximum number of rows in table [closed]
  9. Best way to store time (hh:mm) in a database
  10. Database design: Calculating the Account Balance
  11. How to store directory / hierarchy / tree structure in the database?
  12. Date range overlapping check constraint
  13. “Prevent saving changes that require the table to be re-created” negative effects
  14. Unique identifier (guid) as primary key in database design
  15. How to choose the clustered index in SQL Server?
  16. SQL Server insert if not exists best practice [closed]
  17. Are GUID collisions possible?
  18. best way to convert and validate a date string
  19. Why do table names in SQL Server start with “dbo”?
  20. Add alternating row color to SQL Server Reporting services report
  21. LIKE vs CONTAINS on SQL Server
  22. How do I enable MSDTC on SQL Server?
  23. Is there any difference between DECIMAL and NUMERIC in SQL Server?
  24. TSQL: Create a view that accesses multiple databases
  25. Check if a temporary table exists and delete if it exists before creating a temporary table
  26. What is the sql connection string I need to use to access localhost\SQLEXPRESS with Windows Authentication or SQL Authentication?
  27. migrate data from MS SQL to PostgreSQL?
  28. Conditional JOIN Statement SQL Server
  29. How do you deal with blank spaces in column names in SQL Server?
  30. ODBC Call Failed with stored procedure – Pass through query

Leave a Comment