Geek asks and answers

Deny rows with the same col1 and different col2

Is it possible to create a restriction to prevent different ones Col2 on the same Col1where the first column cannot be NULLunlike the second?

To clarify my requirement, look at the example data with a single line:

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

Can a second line be prevented with the same MaterialNumberbut different fiModel?

Here is the sql script, the second one INSERTshould fail, as this is a different model with the same number.

In the case of a rot connection:

Table (simplified):

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

Two lines, the second insertion may not be possible:

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

, fiModel NULL, null, fiModel. MaterialNumber + fiModel ( null) . , fiModel.

+3
2

. , , , ChkMaterialNumber .

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

.

, ( fiModel1) UNIQUEIDENTIFIER, NEWID() .

, :

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

, :

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

, 3 , :

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

,

0

More articles:


All Articles