SQL Server - Check Constraints and UDF's
Just came across a post in a SQL Forum I am a member of ... and found an interesting problem.
Client
----------------------
PK ClientId
PK Version
Product
----------------------
PK ProductId
FK ClientId
Is this possible?! I can´t make it work, becaust it says
that ClientId in table Client must be uniqe. But I just want to know that
the ClientId entered in Product is valid and exists in Client.
Another member of the forum actually suggested changing the table schema to solve the problem. I usually keep myself from giving such advice if the problem can be solved easily. (Also coz I do not know anything about the SQL Server Environment of the person asking the question.)
This left me wondering ... how many people do actually use check constraints to ensure referential integrity. The only way they seem to think is through foreign keys.
The second thought was regarding is the use of User-defined Functions. SQL Server 2000 introduced UDF's but I havent seen many developers making full use of the facility.
Solving the problem is actually quite easy using a combination of check constraints and user defined functions...
Use Pubs
go
CREATE TABLE [Client] (
[ClientId] [int] NOT NULL ,
[Version] [int] NOT NULL ,
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[ClientId],
[Version]
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER FUNCTION [udf_ClientIdCheck] (@ClientId int)
RETURNS INT
AS
BEGIN
DECLARE @return_value bit
if exists (select top 1 Clientid from Client where ClientId = @ClientId)
set @return_value = 1
else
set @return_value = 0
return @return_value
END
go
CREATE TABLE [Product] (
[Productid] [int] NULL ,
[Clientid] [int] check (dbo.udf_ClientIdCheck(ClientID) =1)
) ON [PRIMARY]
GO
insert into client values(1,1)
insert into product values (1,1)
insert into product values (1,2)
go
select * from product
go
drop table [Client]
drop table [Product]
go
If you analyze this code you will find that it achieves the validation check with no changes in the schema ... and leaves both the developer and the DBA free from headaches :) .