May 14, 2014 at 7:33 pm
Need help with creating a unique index with clause (SQL Server 2005 SP2).
Below is my requirement:
CREATE TABLE #test
(id INT NOT NULL,
ProductID int NOT NULL,
Datecreated DATETIME NULL ,
DateExpired DATETIME NULL
)
INSERT INTO #test
SELECT 1,10, '2014-03-15 01:26:05',NULL
UNION
SELECT 2,20, '2014-03-15 02:26:05','2014-05-15 04:26:05'
UNION
SELECT 2,20, '2014-04-15 02:26:05','2014-05-15 01:26:05'
UNION
SELECT 2,20, '2014-05-15 01:26:05',NULL
UNION
SELECT 3,30, '2014-06-15 02:26:05',NULL
UNION
SELECT 4,50, '2014-08-15 02:26:05',NULL
SELECT * FROM #test
-- DROP TABLE #test
--Need to create an unique constraint on fields ID,Productid and DateExpired field only when DateExpired is null
CREATE UNIQUE INDEX IDX_TEST ON #test(ID,DateExpired) WHERE DateExpired is NULL -- how do i do it ?
thanks in advance
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
May 14, 2014 at 11:01 pm
First the DDL
USE tempdb;
GO
CREATE TABLE dbo.TestConstraint
(
id INT NOT NULL
,ProductID int NOT NULL
,Datecreated DATETIME NULL
,DateExpired DATETIME NULL
);
GO
ALTER TABLE [dbo].[TestConstraint] ADD CONSTRAINT CNSTR_UNQ_DBO_TESTCONSTRAINT_PROD_CRE_EXP UNIQUE
( ProductID, Datecreated, DateExpired )
GO
Then some data
INSERT INTO dbo.TestConstraint
SELECT 1,10, '2014-03-15 01:26:05',NULL
UNION
SELECT 2,20, '2014-03-15 02:26:05','2014-05-15 04:26:05'
UNION
SELECT 2,20, '2014-04-15 02:26:05','2014-05-15 01:26:05'
UNION
SELECT 2,20, '2014-05-15 01:26:05',NULL
UNION
SELECT 3,30, '2014-06-15 02:26:05',NULL
UNION
SELECT 4,50, '2014-08-15 02:26:05',NULL
SELECT * FROM dbo.TestConstraint
To test it, try insert a duplicate
INSERT INTO dbo.TestConstraint
SELECT 1,10, '2014-03-15 01:26:05',NULL
The test fails with a message
[font="Courier New"]Msg 2627, Level 14, State 1, Line 2
Violation of UNIQUE KEY constraint 'CNSTR_UNQ_DBO_TESTCONSTRAINT_PROD_CRE_EXP'. Cannot insert duplicate key in object 'dbo.TestConstraint'. The duplicate key value is (10, Mar 15 2014 1:26AM, <NULL>).[/font]
😎
PS Cleanup
DROP TABLE dbo.TestConstraint
May 14, 2014 at 11:10 pm
Hey Mate..
Thanks for the response but my requirement is to have a unique constraint create when the Dateexpired field is null ..
Let say below are my insert statements then it shouldnt error
INSERT INTO dbo.TestConstraint
SELECT 1,10, '2014-03-15 01:26:05','2014-03-15 02:26:05'
UNION
SELECT 1,10, '2014-03-15 01:26:05','2014-03-15 02:26:05'
It should error only when its trying to have duplcicates when the dateexpired is null
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
May 14, 2014 at 11:31 pm
Annoyingly hard in SQL 2005, trivial in SQL 2008. 2005 doesn't have filtered indexes
Is the ID column unique? The pk?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2014 at 11:41 pm
No, the ID column is not unique..
i know we can create clause with sql 2008 and later versions
Create unique index IDX_1 on Table1(Column) where column2 is null
The above query is what i am trying to do on sql 2005 server . .Is there any other way ?
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
May 14, 2014 at 11:44 pm
There's workarounds, they're sometimes a pain
Are there any column(s) that are guaranteed unique in that table? Can you add an identity column?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 15, 2014 at 12:00 am
Yes, I do have an identity field which i havent put in the example.. Can you please let me know the workaround.
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
May 15, 2014 at 2:26 am
Sorry about the misunderstanding, the first post was pre industrial strength tanker sized load of espresso:-P
One solution is to use a trigger
😎
USE tempdb;
GO
CREATE TABLE dbo.TestConstraint
(
id INT IDENTITY(1,1) NOT NULL
,ProductID int NOT NULL
,Datecreated DATETIME NULL
,DateExpired DATETIME NULL
);
GO
CREATE TRIGGER TRG_MY_CONSTRAINT
ON dbo.TestConstraint
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS
( SELECT *
FROM dbo.TestConstraint TC
INNER JOIN inserted IX
ON TC.ProductID = IX.ProductID
AND TC.Datecreated = IX.Datecreated
WHERE TC.DateExpired IS NULL
AND IX.DateExpired IS NULL
--AND TC.id < IX.id
)
BEGIN
RAISERROR ( 'CONSTRAINT VIOLATION! DUPLICATE VALUES WHERE DateExpired IS NULL',1,1)
END
ELSE
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.TestConstraint
(
ProductID,Datecreated,DateExpired
)
SELECT
ProductID,Datecreated,DateExpired
FROM inserted
END
END
May 15, 2014 at 3:14 am
Eirikur Eiriksson (5/15/2014)
Sorry about the misunderstanding, the first post was pre industrial strength tanker sized load of espresso:-POne solution is to use a trigger
You need an update trigger as well if you go that route. We don't know whether or not the id or product id can be updated, we don't know if there's a use case for setting expired to null.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 15, 2014 at 3:39 am
Option with a unique index. This assumes you're OK adding an extra column to the table. It's computed, so no storage, but you can't insert into it or update it.
CREATE TABLE #test (
id INT NOT NULL,
ProductID int NOT NULL,
Datecreated DATETIME NULL ,
DateExpired DATETIME NULL,
TheIdentityColumn INT IDENTITY
)
INSERT INTO #test
( id ,
ProductID ,
Datecreated ,
DateExpired
)
SELECT 1,10, '2014-03-15 01:26:05',NULL
UNION
SELECT 2,20, '2014-03-15 02:26:05','2014-05-15 04:26:05'
UNION
SELECT 2,20, '2014-04-15 02:26:05','2014-05-15 01:26:05'
UNION
SELECT 2,20, '2014-05-15 01:26:05',NULL
UNION
SELECT 3,30, '2014-06-15 02:26:05',NULL
UNION
SELECT 4,50, '2014-08-15 02:26:05',NULL
----
ALTER TABLE #test
ADD FakeUniqueColumn AS (CASE WHEN DateExpired IS NULL THEN CAST(id AS VARCHAR(10)) + '|' + CAST(ProductID AS VARCHAR(10)) ELSE '|'+ CAST(TheIdentityColumn AS VARCHAR(10)) END)
CREATE UNIQUE INDEX idx_IDProductIDNullExpiry ON #test (FakeUniqueColumn)
To test...
This succeeds
INSERT INTO #test
( id ,
ProductID ,
Datecreated ,
DateExpired
)
SELECT 1,10, '2014-03-15 01:26:05', '2014-03-15 01:26:05'
This fails
INSERT INTO #test
( id ,
ProductID ,
Datecreated ,
DateExpired
)
SELECT 1,10, '2014-03-15 01:26:05', NULL
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 15, 2014 at 5:06 am
A trigger with insert and update
😎
CREATE TRIGGER [dbo].[TRG_MY_CONSTRAINT]
ON [dbo].[TestConstraint]
INSTEAD OF INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS
( SELECT *
FROM dbo.TestConstraint TC
INNER JOIN inserted IX
ON TC.ProductID = IX.ProductID
AND TC.Datecreated = IX.Datecreated
WHERE TC.DateExpired IS NULL
AND IX.DateExpired IS NULL
--AND TC.id < IX.id
)
BEGIN
RAISERROR ( 'CONSTRAINT VIOLATION! DUPLICATE VALUES WHERE DateExpired IS NULL',1,1)
END
ELSE
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS (SELECT * FROM deleted)
BEGIN
INSERT INTO dbo.TestConstraint
(
ProductID,Datecreated,DateExpired
)
SELECT
ProductID,Datecreated,DateExpired
FROM inserted
END
ELSE
BEGIN
UPDATE T
SET T.ProductID = IX.ProductID
,T.Datecreated = IX.Datecreated
,T.DateExpired = IX.DateExpired
FROM inserted IX
INNER JOIN dbo.TestConstraint T
ON IX.id = T.ID
END
END
END
May 15, 2014 at 1:06 pm
Question for Gail, on a 2005 platform, which solution of these two would you prefer and why? The question of course is a simplification of course, more like penny for your thoughts.
😎
May 15, 2014 at 5:04 pm
Thanks Guys for the workarounds.. I will look into it and implement the one with less changes to my existing schema.
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
May 16, 2014 at 12:48 am
Eirikur Eiriksson (5/15/2014)
Question for Gail, on a 2005 platform, which solution of these two would you prefer and why?
Constraint. Less overhead, less chance of it getting altered later by a junior dev and breaking somewhere subtle. Possible for the optimiser to use to help with plan generation
If a constraint isn't possible (can't create another column), then I'd go for an indexed view. If I can't create an indexed view, then I'd consider a trigger.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2014 at 11:13 pm
Sri8143 (5/15/2014)
Thanks Guys for the workarounds.. I will look into it and implement the one with less changes to my existing schema.
You might want to correct the problem at schema level rather than implement either of the two workarounds. Here is one suggestion:
- Introduce an EventType (Created, Expired) table
- Remove one date column and add a foreign key to the EventType
- Replace the original table with a view
+--------------+ +---------------+
| ProductEvent | | EventType |
+--------------+ +---------------+
| ProductId | ,-|-| EventTypeId |
| EventTypeId |>|--' | EventTypeName |
| EventDate | +---------------+
+--------------+
This would eliminate the need for any kind of a workaround.
Also, as Gail has pointed out, the trigger should be considered as the least desirable option.
😎
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply