locked
Struggling with a CHECK CONSTRAINT RRS feed

  • Question

  • Hi!

    I'm trying to create a Constraint on my table that prevents me from adding a new row if a row with certain values already exist. It may not be the correct way to go, but I created a function to help me check if there exists a row with the specific values and then basing my constrain on this.

    Here's a simplified version of table, the function and the check constraint I've written:

    CREATE TABLE [dbo].[Media](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [VareNr] [int] NULL,
        [BildeType] [int] NULL,
        [ErAktiv] [bit] NOT NULL
    )
    CREATE FUNCTION fn_FinnesAktivtProduktbilde
    (@nobbNr int)
    RETURNS bit
    AS
    BEGIN
    
    DECLARE @returnValue bit
    IF EXISTS(
    SELECT Id
    FROM Media
    WHERE VareNr = @nobbNr AND BildeType = 1 AND ErAktiv = 1
    )
    SET @returnValue = 1
    ELSE
    SET @returnValue = 0
    
    RETURN @returnValue
    END
    ALTER TABLE [dbo].[Media]
    	ADD CONSTRAINT [Media_EksisterendeProduktbildeVare_Check] 
    	CHECK (dbo.fn_FinnesAktivtProduktbilde(VareNr)=0)

    So, the function checks if there exists a row with the same "VareNr" as the one that is to inserted and where BildeType = 1 and ErAktiv = 1.

    Now, as hopeful as I was, I thought this would work. It does of course not.

    With an empty table I cannot insert a new row like this:

    INSERT INTO Media(VareNr, BildeType, ErAktiv) VALUES (10 , 1, 1 )

    and I can't really see why since if I run:

    SELECT dbo.fn_FinnesAktivtProduktbilde(10)

    it returns 0.

    Am I just missing a detail or is there something else going on that I don't know of?

    Regards,
    Mats




    Wednesday, March 3, 2010 1:12 PM

Answers

  • Mats,

    The function should be looking for a row with different [ID] than the one being inserted, right?


    CREATE FUNCTION dbo.fn_FinnesAktivtProduktbilde (
    @nobbNr int,
    @ID int
    )
    RETURNS bit
    AS
    BEGIN
    
    DECLARE @returnValue bit;
    
    IF EXISTS(
    SELECT 1
    FROM dbo.Media
    WHERE
        [ID] <> @ID
        AND VareNr = @nobbNr 
        AND BildeType = 1 AND ErAktiv = 1
    )
    SET @returnValue = 1;
    ELSE
    SET @returnValue = 0;
    
    RETURN @returnValue;
    END
    GO
    ALTER TABLE [dbo].[Media]
    ADD CONSTRAINT [Media_EksisterendeProduktbildeVare_Check] 
    CHECK (dbo.fn_FinnesAktivtProduktbilde(VareNr, [ID])=0);
    GO

    If you are using SQL Server 2008, then you could use a filtered index.

    create unique nonclustered index ix_u_nc_Media_VareNr
    on dbo.Media(VareNr)
    where BildeType = 1 AND ErAktiv = 1;
    GO

    For SS 2005 / 2000, you could also use an indexed view.

    create view dbo.MyView
    with schemabinding
    as
    select VareNr
    from dbo.Media
    where BildeType = 1 AND ErAktiv = 1
    GO
    create unique clustered index ix_u_c_Media_VareNr
    on dbo.MyView(VareNr);
    GO

    Now try to insert duplicated rows.


    AMB


    • Marked as answer by Mats Aubell Wednesday, March 3, 2010 1:46 PM
    Wednesday, March 3, 2010 1:25 PM

All replies

  • Mats,

    The function should be looking for a row with different [ID] than the one being inserted, right?


    CREATE FUNCTION dbo.fn_FinnesAktivtProduktbilde (
    @nobbNr int,
    @ID int
    )
    RETURNS bit
    AS
    BEGIN
    
    DECLARE @returnValue bit;
    
    IF EXISTS(
    SELECT 1
    FROM dbo.Media
    WHERE
        [ID] <> @ID
        AND VareNr = @nobbNr 
        AND BildeType = 1 AND ErAktiv = 1
    )
    SET @returnValue = 1;
    ELSE
    SET @returnValue = 0;
    
    RETURN @returnValue;
    END
    GO
    ALTER TABLE [dbo].[Media]
    ADD CONSTRAINT [Media_EksisterendeProduktbildeVare_Check] 
    CHECK (dbo.fn_FinnesAktivtProduktbilde(VareNr, [ID])=0);
    GO

    If you are using SQL Server 2008, then you could use a filtered index.

    create unique nonclustered index ix_u_nc_Media_VareNr
    on dbo.Media(VareNr)
    where BildeType = 1 AND ErAktiv = 1;
    GO

    For SS 2005 / 2000, you could also use an indexed view.

    create view dbo.MyView
    with schemabinding
    as
    select VareNr
    from dbo.Media
    where BildeType = 1 AND ErAktiv = 1
    GO
    create unique clustered index ix_u_c_Media_VareNr
    on dbo.MyView(VareNr);
    GO

    Now try to insert duplicated rows.


    AMB


    • Marked as answer by Mats Aubell Wednesday, March 3, 2010 1:46 PM
    Wednesday, March 3, 2010 1:25 PM
  • Hi Hunchback

    The filtered index worked like a charm. I'm glad there's people like you around  :)

    Mats
    Wednesday, March 3, 2010 1:48 PM
  • Thanks, Mats.


    AMB
    Wednesday, March 3, 2010 2:02 PM