Prevent rows with same col1 and different col2

Question

Is it possible to create a constraint to prevent different Col2 on same Col1 where the first column cannot be NULL as opposed to the second?

To clarify my requirement, consider this sample data with a single row:

MaterialNumber(varchar50, not null)    fiModel(int, null, fk)
1234-4321                              1

Is it possible to prevent a second row with the same MaterialNumber but a different fiModel?

Here's a sql-fiddle, the second INSERT should fail since it's a different model with the same number.

In case of link rot:

The (simplified) table:

CREATE TABLE [dbo].[tabSparePartMasterData](
    [MaterialNumber] [varchar](50) NOT NULL,
    [fiModel] [int] NULL)

Two rows, the second insert should not be possible:

INSERT INTO tabSparePartMasterData(MaterialNumber,fiModel)
VALUES('1234-4321', 1);
INSERT INTO tabSparePartMasterData(MaterialNumber,fiModel)
VALUES('1234-4321', 2);

Note that fiModel can be null, but if it's not null it should not be possible to add another row with the same or different fiModel. I have already solved the unique index on MaterialNumber + fiModel(not null) with a computed column. But i'm stuck on how to prevent a different fiModel.

Answer 1

You can add a persisted column to the table to support this conditional constraint. If you dont want to alter this table you can implement the same strategy using a view that projects the ChkMaterialNumber column and slapping a unique constraint on that.

CREATE TABLE [dbo].[tabSparePartMasterData]
(
    [YourPK] int identity(1,1) not null primary key,
    [MaterialNumber] [varchar](50) NOT NULL,
    [fiModel] [int] NULL
);
go

--add a computed column here to enforce the conditional constraint:
alter table [dbo].[tabSparePartMasterData] add [ChkMaterialNumber] as ( case when fiModel is null then cast(YourPK as varchar) else MaterialNumber end)

--now add unique constraint to the computed column:
create unique index ux_SparePartMasterData on [dbo].[tabSparePartMasterData]([ChkMaterialNumber]);
go



-- OK
INSERT INTO tabSparePartMasterData(MaterialNumber,fiModel)
VALUES('1234-4321', 1);

-- FAILS
INSERT INTO tabSparePartMasterData(MaterialNumber,fiModel)
VALUES('1234-4321', 2);

--OK
INSERT INTO tabSparePartMasterData(MaterialNumber,fiModel)
VALUES('1234-4321', null);