CODEWITHSUNDEEP
sql-serverconstraints
Sarah
Sarah

Reputation: 1293

MS SQL Constraint on a column

I have a table called Products this is how it looks like and I am trying to create a constraint on [IsDefaultProductKey] column, that any time a value is added to it, it needs be an active product key. 

CREATE TABLE [dbo].[Products](
    [ProductId] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](64) NOT NULL,
     [IsActive] [bit] NOT NULL,
    [IsDefaultProductKey] [int] NULL,
CONSTRAINT [PK_dbo.Products] PRIMARY KEY CLUSTERED 
(
    [ProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Products] ADD  CONSTRAINT [DF_products_IsActive]  DEFAULT ((1)) FOR [IsActive]
GO
ALTER TABLE [dbo].[Products]  WITH CHECK ADD  CONSTRAINT [FK_Products_Product_IsDefaultProductKey] FOREIGN KEY([IsDefaultProductKey])
REFERENCES [dbo].[Products] ([ProductId])
GO

ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_Product_IsDefaultProductKey]
GO

If these are the entries in the table, row 4 should not be allowed to have a value of 1, since 1 is inactive. How can I go about adding a constraint on the table for that

ProductId   Name    IsActive    IsDefaultProductKey
1            Test1  0             NULL
2            Test2  1             NULL
3            Test3  0                2
4            Test4   0             1 (Should not let me do this)

Based on suggestion, I created this UDF. But still not acting 100% the way I want it.. Please suggest.

CREATE TABLE [dbo].[Products]( [ProductId] [int] IDENTITY(1,1) NOT NULL, 
[Name] [nvarchar](64) NOT NULL, 
[IsActive] [bit] NOT NULL, 
[IsDefaultProductKey] [int] NULL, 
) 
go

Create FUNCTION dbo.CheckProduct (@IsDefaultProductKey int)
RETURNS int
AS 
BEGIN
  DECLARE @retval int
    SELECT @retval = 0
    Select @retval = 1
    FROM [Products]
    WHERE ProductId = @IsDefaultProductKey and IsActive = 1 
  RETURN @retval
END;
GO
--Select CheckProduct(1)

ALTER TABLE [Products] 
  ADD CONSTRAINT chkActiveProduct 
  CHECK (IsDefaultProductKey = null or dbo.CheckProduct(IsDefaultProductKey) = 1); 
go

Upvotes: 1

Views: 188

Answers (4)

Sarah
Sarah

Reputation: 1293

Thanks to Tab Allemnan, Here is the solution I found. Works both ways.

Create FUNCTION CheckProduct (@IsDefaultProductKey int, @ProductId int, @IsActive bit)
RETURNS bit
AS 
BEGIN
BEGIN  
    DECLARE @ret bit;
    if (@IsDefaultProductKey is not NULL)
    begin  
        SELECT @ret = 1  
        FROM [Products] p   
        WHERE p.ProductID = @IsDefaultProductKey   
            AND p.IsActive = 1; 
    end
    else   -- If @IsDefaultProductKey is null
         Select @ret = 1

    If (@IsActive = 0)  -- If Product is made inactive, make sure that its not a defaultkey for any product. 
    Begin
         SELECT @ret = 0  
        FROM [Products] p   
        WHERE p.IsDefaultProductKey = @ProductId   
    End


     IF (@ret IS NULL)   
        SET @ret = 0;  
    RETURN @ret;  
END; 
END;
--Select dbo.CheckProduct (2,1,0)
GO
ALTER TABLE [Products] 
  ADD CONSTRAINT chkActiveProduct 
  CHECK (dbo.CheckProduct(IsDefaultProductKey,ProductId, IsActive)=1); 
go

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

You can use a CHECK CONSTRAINT that calls a UDF that queries the table to see if the ProductId referenced by IsDefaultProductKey is Active or not.

EDIT:
Since you need the constraint to check both ways, you would create a UDF that has parameters for ProductId, IsActive and IsDefaultProductKey.

Inside the function, if there is a non-NULL value for IsDefaultProductKey, then you need to query the table to see if the row with that ProductId is Active. If not, then the function needs to return false.

ALSO, if the IsActive parameter is passed a value of 0, then you need to check the table to make sure that no row has a IsDefaultProductKey equal to the value of the ProductId parameter. If there is such a row, then the function needs to return false.

But if neither of those cases occur, the function returns true, and in the CHECK CONSTRAINT, you then just test to see if the function returns true.

Upvotes: 1

PhilS
PhilS

Reputation: 624

I think you need a trigger not a constraint to do this. Something like:

CREATE OR ALTER TRIGGER DefaultNotActive ON [dbo].[Products]
AFTER INSERT, UPDATE
AS
IF EXISTS (SELECT *
           FROM [dbo].[Products] p 
           JOIN inserted AS i 
           ON p.[ProductId] = i.[IsDefaultProductKey] 
           WHERE p.[IsActive] = 0
          )
BEGIN
RAISERROR ('Default Product is inactive.', 16, 1);
ROLLBACK TRANSACTION;
RETURN 
END;

It might need to be more complex if you also need the default product key to exist. Currently this will allow inserts\updates where the default product key is an id that does not have an existing row in the table

Upvotes: 0

Gaurav
Gaurav

Reputation: 623

I did not understand your questions completely. However looks like you want to apply a check constraint based on the value of other column. The issue which I see in your SQL is you are applying a column level constraint, while I think you need to apply a table level constraint. Please see below sample based on my understanding of your question. 

    CREATE TABLE [dbo].[Products](
    [ProductId] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](64) NOT NULL,
    [IsActive] [bit] NOT NULL,
    [IsDefaultProductKey] [int] NULL,
         CONSTRAINT ck_contraint CHECK
         (
             (IsActive = 1 AND (IsDefaultProductKey>0) ) 

         )
   )

Upvotes: 0

Related Questions