June 13, 2018 at 7:43 am
Hi ,
Below is my code. Using Check constraint , the aim is to throw an error whenever the insert statement tries to insert a duplicate value for the column XAN1 and Is_delete=0.
For example, suppose below 2 records are present in the table:
1,'DAT1','FAN1',0
2,'DAT2','FAN2',1
a> Now if I try to insert a new record
insert into test18.tab20 values(3,'DAT1','FAN1',0);
then it should give me error as DAT1 already exists + is_delete=0
b> and If I try to insert a new record like
insert into test18.tab20 values(3,'DAT2','FAN2',0);
then this record should get inserted because is_delete=1 even though DAT2 value already exists.
My function FN2 works fine when I call it using just the select statement (-> Select [test18].[FN2] ('DAT1') )
but using INSERT statement dont get the desired result.
Could you help tell if below code is correct or has some issues?
================ ==========
create schema test18;
CREATE TABLE TEST18.TAB20 (
XAID tinyint not null,
XAN1 nvarchar(20) not null,
XFN1 nvarchar(20) not null,
IS_DELETE tinyint not null
)
CREATE FUNCTION [TEST18].[FN2] (@AVAL nvarchar(20))
RETURNS INT
AS
BEGIN
DECLARE @Return_Value INT;
IF EXISTS(SELECT 1 FROM [TEST18].[TAB20] WHERE XAN1 = @AVAL AND IS_DELETE=0)
SET @Return_Value = 0
ELSE
SET @Return_Value = 1
RETURN @Return_Value
END
ALTER TABLE [TEST18].[TAB20] WITH CHECK ADD CONSTRAINT [CHKVDEL] CHECK (([TEST18].[FN2](XAN1))=1)
GO
=================== ===================
Thanks
Vijay
June 13, 2018 at 7:59 am
Just an idea. Can you achieve a similar outcome by creating a unique index on (XAN1, XFN1,IS_DELETE)? It should perform much better if you can.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 13, 2018 at 8:02 am
You shouldn't be able to add that constraint at all with the sample data you provided because the first record will already violate it. Or if the constraint is already there you wouldn't be able to add the first record at all.
June 13, 2018 at 8:04 am
Phil Parkin - Wednesday, June 13, 2018 7:59 AMJust an idea. Can you achieve a similar outcome by creating a unique index on (XAN1, XFN1,IS_DELETE)? It should perform much better if you can.
I think it'll need to be a filtered unique index, Phil. If my understanding is correct, there can't be duplicates of XAN1 and XFN1 when the value of IS_DELETE is 0, however, you can have many rows with the same values for XAN1 and XFN1 if the value of IS_DELETE isn't 0 (1?).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 13, 2018 at 8:16 am
Thom A - Wednesday, June 13, 2018 8:04 AMI think it'll need to be a filtered unique index, Phil. If my understanding is correct, there can't be duplicates of XAN1 and XFN1 when the value of IS_DELETE is 0, however, you can have many rows with the same values for XAN1 and XFN1 if the value of IS_DELETE isn't 0 (1?).
I was wondering about that, but I've never created a filtered unique index before, so didn't want to look silly by suggesting something impossible 🙂
Note to OP: if a filtered unique index does the job, it is a better solution than the proposed check constraint.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 13, 2018 at 8:38 am
If I understand correctly, this'll get you what you're after:
USE Sandbox;
GO
--Create sample table and data
CREATE TABLE SampleTable (ID int IDENTITY(1,1),
XAN1 char(4),
XFN1 char(4),
IS_DELETE bit);
INSERT INTO SampleTable (XAN1,XFN1,IS_DELETE)
VALUES('DAT1','FAN1','0'),
('DAT2','FAN2','1');
GO
--Create a duplicate
INSERT INTO SampleTable (XAN1,XFN1,IS_DELETE)
VALUES('DAT1','FAN1','0'),
('DAT2','FAN2','0'); --This will work right now
SELECT *
FROM SampleTable
ORDER BY ID;
GO
--Delete those records out and then add the INDEX
DELETE FROM SampleTable
WHERE ID IN (3,4);
CREATE UNIQUE INDEX Live_XAN1_XFN1_UQIX ON SampleTable(XAN1,XFN1,IS_DELETE)
WHERE IS_DELETE = 0;
GO
--And now try again (separately)
INSERT INTO SampleTable (XAN1,XFN1,IS_DELETE)
VALUES('DAT1','FAN1','0'); --This will fail
GO
INSERT INTO SampleTable (XAN1,XFN1,IS_DELETE)
VALUES('DAT2','FAN2','0'); --This will still work
INSERT INTO SampleTable (XAN1,XFN1,IS_DELETE)
VALUES('DAT2','FAN2','1'); --This will also work (and will be a duplicate of ID 2)
GO
SELECT *
FROM SampleTable;
GO
--Clean up
DROP TABLE SampleTable;
GO
Curse SSC paste formatting...
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 22, 2018 at 9:49 am
Indexing would be preferred over the constraint, especially if you might get lots of inserts occurring in a short period of time. I'd go with Thom's solution.
If you agree, please mark it as a solution.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply