March 16, 2004 at 6:33 am
Hi,
I have a scenario where I have to have a unique combination on two columns and One of the columns is Nullable and the other one is not null, Now my problem is the uniqueness has to come into force only when the nullable column has some data in it.
Like I have one OPID and FKEY_DOCTOR
OPID is Nullable and FKEY_DOCTOR is NOT NULL, if OPID has some data entered then the combination of OPID and FKEY_DOCTOR should be unique, but I want to allow NULL for OPID and same FKEY_DOCTOR more than once, is it possible.
Thanks
Prasad Bhogadi
www.inforaise.com
March 16, 2004 at 10:45 am
Hi,
You would have to ensure that the values in the 2 columns are together unique, so what you are suggesting wont work.
You could include a "token" extra column that is just an INT incremented with each new row. If the unique constraint included this then I suppose you could have a table like this:
OPID FKEY_DOCTOR TOKEN
null hello 1
null hello 2
null goodbye 1
You see by including an extra column the other 2 can have the same values but still be unique.
Not too sure this is what your looking for though.
Hope this helps.
March 16, 2004 at 12:10 pm
Although it's easy enough to write a trigger to enforce this, I prefer to use an indexed view for this in SQL Server 2000.
--Jonathan
March 17, 2004 at 6:56 am
Why an Indexed View? Can someone explain the differences between the indexed view and a Trigger?
1.) Is an Indexed View faster?
2.) What are the advantages of the View over a Trigger?
March 17, 2004 at 9:29 am
Adding to what Jonathan said, here's an example:
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO
DROP VIEW vdoctest
DROP TABLE doctest
GO
CREATE TABLE doctest( FKEY_DOCTOR int NOT NULL, OPID int NULL)
GO
CREATE VIEW vdoctest WITH SCHEMABINDING
AS
SELECT FKEY_DOCTOR, OPID
FROM dbo.doctest
WHERE OPID is NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX ix_vdoctest ON vdoctest (FKEY_DOCTOR, OPID)
GO
INSERT doctest (FKEY_DOCTOR, OPID) VALUES (1, null)
INSERT doctest (FKEY_DOCTOR, OPID) VALUES (1, null)
INSERT doctest (FKEY_DOCTOR, OPID) VALUES (2, 1)
INSERT doctest (FKEY_DOCTOR, OPID) VALUES (3, 1)
INSERT doctest (FKEY_DOCTOR, OPID) VALUES (4, null)
INSERT doctest (FKEY_DOCTOR, OPID) VALUES (4, null)
INSERT doctest (FKEY_DOCTOR, OPID) VALUES (4, null)
INSERT doctest (FKEY_DOCTOR, OPID) VALUES (4, 1)
INSERT doctest (FKEY_DOCTOR, OPID) VALUES (4, 1) -- this row will fail
INSERT doctest (FKEY_DOCTOR, OPID) VALUES (5, 1)
INSERT doctest (FKEY_DOCTOR, OPID) VALUES (5, 2)
SELECT * FROM doctest
SELECT * FROM vdoctest
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply