RIanGillis RIanGillis - 5 months ago 18
SQL Question

Composite Keys and Referential Integrity in T-SQL

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?

For a shared-email address example:
Three different user tables (UserA, UserB, UserC)
One UserType Table (UserType)
One Email Table (EmailAddress)
One Email-User Relationship Table (EmailRelationship)

The EmailRelationship Table contains three columns, EmailId, UserTypeId and UserId

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

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

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)

Answer

Thanks to @GarethD I created a CHECK constraint that called a scalar-function that would enforce referential integrity:

Using my above example:

alter FUNCTION [dbo].[UserTableConstraint](@Id int, @UserTypeId int)
RETURNS int
AS 
BEGIN
    IF EXISTS (SELECT Id From [dbo].[UserA] WHERE Id = @Id and UserTypeId = @UserTypeId)
    return 1
    ELSE IF EXISTS (SELECT Id From [dbo].[UserB] WHERE Id = @Id and UserTypeId = @UserTypeId)
    return 1
    ELSE IF EXISTS (SELECT Id From [dbo].[UserC] WHERE Id = @Id and UserTypeId = @UserTypeId)
    return 1
    return 0
end;

alter table [dbo].[emailrelationship]
--drop constraint CK_UserType
with CHECK add constraint CK_UserType
CHECK([dbo].[UserTableConstraint](UserId,UserTypeId) = 1)

I am sure there is a not insignificant overhead to a Scalar-function call from within a CONSTRAINT. If the above becomes prohibitive I will report back here, though the tables in question will not have to deal with a large volume of INSERTs.

If there are any other reasons to not do the above, I would like to hear them. Thanks!

Update:

I've tested INSERT and UPDATE with 100k rows (SQL Server 2014, 2.1ghz quadcore w/ 8gb ram):

  • INSERT takes 2 seconds with out the CONSTRAINT
  • and 3 seconds with the CHECK CONSTRAINT

Turning on IO and TIME STATISTICS causes the INSERT tests to run in:

  • 1.7 seconds with out the CONSTRAINT
  • and 10 seconds with the CHECK CONSTRAINT

I left the STATISTICS on for the UPDATE 100k rows test:

  • just over 1sec with out the CONSTRAINT
  • and 1.5sec with the CHECK CONSTRAINT

My referenced tables (UserA, UserB, UserC from my example) only contain around 10k rows each, so anybody else looking to implement the above may want to run some additional testing, especially if your referenced tables contain millions of rows.

Caveat:

The above solution may not be suitable for most uses, as the only time referential integrity is checked is during the CHECK CONSTRAINT upon INSERT. Any other operations or modifications of the data needs to take that into account. For example, using the above, if an Email is deleted any related EmailRelationship entries will be pointing to invalid data.