If you are planning to make a table constraint that is more complex than a simple check against a field or two then I recommend using functions. By creating a function you can make complex logic by which you can return True Or False states that indicate to a constraint if the data is what you need it to be. You can also add parameters to a function giving you the dynamic possibility to process data being processed.
Example:
Lets say that you have function that wants to check if a ID value is a certain numeric ID and then you want to make sure that a row being inserted does not have a NULL value in a certain field.
Here is some sample code:
CREATE FUNCTION [dbo].[CheckForNullByID]
(
— Add the parameters for the function here
@someID int,
@someFieldNotNULL char(11)
)
RETURNS bit
AS
BEGIN
DECLARE @retValue bit = 0
IF @someID = 40
BEGIN
IF @someFieldNotNULL IS NOT NULL
BEGIN
SET @retValue = 1
END
ELSE
SET @retValue = 0
END
ELSE
SET @retValue = 1
RETURN @retValue
END
GO
Then you call the function in your constraint like the following expression:
([dbo].[CheckForNullByID]([rowIDValue],[someFieldValueThatIsNotToBeNULL])>(0))
Thats it :)!!!
Hope this helps 🙂