Composite Keys and Referential Integrity in T-SQL
<p>Is it possible, in T-SQL, to have a relationship table with a composite key composed of 1 column defining Table Type and another column defining the Id of a row from a table referenced in the Table Type column?</p>

<p>For a shared-email address example:<br/>Three different user tables (UserA, UserB, UserC)<br/>One UserType Table (UserType)<br/>One Email Table (EmailAddress)<br/>One Email-User Relationship Table (EmailRelationship)<br/><br/>The EmailRelationship Table contains three columns, EmailId, UserTypeId and UserId</p>

<p>Can I have a relationship from each User table to the EmailRelationship table (or some other way?) to maintain referential integrity? </p>

<p>I've tried making all three columns in the EmailRelationship table into primary keys, I've tried making only UserTypeId and UserId primary.</p>

<pre><code>CREATE TABLE [dbo].[UserType](
[Id] [int] IDENTITY(1,1) NOT NULL ,
[Type] [varchar](50) NOT NULL)
insert into [dbo].[UserType]
([Type])
values
('A'),('B'),('C')

CREATE TABLE [dbo].[UserA](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserTypeId] [int] NOT NULL,
[Name] [varchar](50) NOT NULL)

insert into [dbo].[UserA]
(UserTypeId,Name)
values
(1,'UserA')

CREATE TABLE [dbo].[UserB](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserTypeId] [int] NOT NULL,
[Name] [varchar](50) NOT NULL)
insert into [dbo].[UserB]
(UserTypeId,Name)
values
(2,'UserB')

CREATE TABLE [dbo].[UserC](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserTypeId] [int] NOT NULL,
[Name] [varchar](50) NOT NULL)
insert into [dbo].[UserC]
(UserTypeId,Name)
values
(3,'UserC') 

CREATE TABLE [dbo].[Email](
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmailAddress] [varchar](50) NOT NULL)
insert into [dbo].[email]
(EmailAddress)
values
('SharedEmail@SharedEmail.com')

CREATE TABLE [dbo].[EmailRelationship](
[EmailId] [int] NOT NULL,
[UserTypeId] [int] NOT NULL,
[UserId] [int] NOT NULL)
insert into [dbo].[EmailRelationship]
(EmailId, UserTypeId, UserId)
values
(1,1,1),(1,2,1),(1,3,1)
</code></pre>