INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Check Constraint to check view (a no-no) or multiple tables (subquery or UDF)

Check Constraint to check view (a no-no) or multiple tables (subquery or UDF)

Check Constraint to check view (a no-no) or multiple tables (subquery or UDF)

(OP)
I have a relationship table which I only want to allow certain entities to enter a record. And those entities could exist in one several places/tables. I have a view that aggregates all of them, but you cannot reference a view in a constraint. So, I was thinking UDF, but now I see that is often problematic. I saw mention of using an instead of trigger, but not sure how that would be accomplished. I have a simple table

CODE

CREATE TABLE [dbo].[Entity_Tag](
	[TagId] [int] NOT NULL,
	[TaggedEntityId] [int] NOT NULL,
	[TaggedBy] [int] NOT NULL,
	[TaggedByDate] [datetime2](7) NOT NULL
) 

where TaggedEntityId has to exist in either Table1, Table2 or Table3. As mentioned earlier, I have a view to look that up, but cannot use a view in a check constraint. I was going to create a UDF

CODE

CREATE FUNCTION dbo.fn_check_entity
  (@key int)
RETURNS bit
AS
BEGIN
DECLARE @Ret INT
IF EXISTS (SELECT * FROM dbo.Vw_AllUserEmails_Advanced WHERE EntityISN = @key)
BEGIN
	SET @Ret = 1
END
ELSE
BEGIN
	SET @Ret = 0
END
RETURN @Ret
END 

then add the constraint

CODE

ALTER TABLE [dbo].[Entity_Tag] ADD CONSTRAINT [FK_Entity_Tag_EntityISN] CHECK (dbo.fn_check_entity(TaggedEntityId)=1) 

But it seems this UDF method may not be reliable as I have seen reports that it is actually not checked until AFTER the insert?

Any insight you can share would be greatly appreciated. I am sure I am not the first person t run into this, I have just not found a good solution.

Thanks,
wb

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close