CODEWITHSUNDEEP
sql-servert-sqlsql-server-2005constraints
Tim Schmelter
Tim Schmelter

Reputation: 460238

Prevent rows with same col1 and different col2

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.

Upvotes: 3

Views: 114

Answers (2)

Nizam
Nizam

Reputation: 4699

Editing my answer after understading the problem.

I think the best solution, instead of using trigger or function is to create a new column (I named it as fiModel1) of type UNIQUEIDENTIFIER the should be filled with NEWID() always.

Then create a computed column just like:

ALTER TABLE [dbo].[tabSparePartMasterData]
    ADD UNIQUECONST as cast([MaterialNumber] as varchar(150)) + case when fiModel is null then    cast(fiModel1 as varchar(150)) else '' end PERSISTED

After that you create a constraint for this column like:

ALTER TABLE [dbo].[tabSparePartMasterData]
ADD CONSTRAINT AK_MaterialNumber UNIQUE (UNIQUECONST);

Finally you can will be able to add the first 3 records but not the last one:

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

I think this solve your problem

Upvotes: 0

nathan_jr
nathan_jr

Reputation: 9292

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);

Upvotes: 3

Related Questions