Advanced Search

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

Expand | Embed | Plain Text

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.