TSQL Function and Check Constraint to Ensure that a Parent Value is Present in the Table


/ Published in: SQL
Save to your folder(s)

If a "InReplyToID" is provided, it must be a valid MessageId


Copy this code and paste it in your HTML
  1. ALTER TABLE [dbo].[Message] WITH CHECK ADD CONSTRAINT [CK_Message] CHECK (([InReplyToId] IS NULL OR [dbo].[CheckInReplyToMessageId]([InReplyToId])>=(0)))
  2. GO
  3. ALTER TABLE [dbo].[Message] CHECK CONSTRAINT [CK_Message]
  4.  
  5.  
  6.  
  7. CREATE FUNCTION [dbo].[CheckInReplyToMessageId]
  8. (
  9. @MessageId BIGINT
  10. )
  11. RETURNS bit
  12. AS
  13. BEGIN
  14. DECLARE @RetVal bit
  15.  
  16. SET @RetVal = (SELECT MessageId
  17. FROM Message
  18. WHERE MessageId = @MessageId)
  19.  
  20. RETURN isnull(@RetVal, 0)
  21.  
  22. END

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.