Constraint/identity which allows duplicate null fields

hi,

I've done Googling and forum hunting but haven't had success finding a simple answer... My table schema is such that it requires the (int) LinkedItemID field to be nullable but still those fields which are set must not be duplicates. I see constraint is out of question and also identity doesn't seem to fit since I'm not using autofill for this particular field. Is there some other way doing this on Sql Server 2005?

Thank you.

Make your value a foreign key constraint you can take the IDENTITY property of another table make sure your table is UNION compatible, create an index on another column in the same table and add that column in it through the new feature called INDEX Column include turn on the IGNORE_DUP_KEY option and you have a duplicate proof column. Now let me explain it UNION is a SET operator that performs implicit distinct by eliminating duplicates, and by adding it to an index it can also use the physical IGNORE_DUP_KEY which also eliminates duplicates and your column can get the benefits of an index without actually being the index. Run a search for UNION,IGNORE_DUP_KEY and INDEX COLUMN include in the BOL(books online). I am assuming you know you have to spend time with Management Studio to create this, post again if you still have question. Hope this helps.|||

Can't you just check before you do an INSERT?

IF NOT EXISTS( SELECT * FROM YourTable WHERE LinkedItemID = @.somevalue)

BEGIN

-- do the insert

END

|||

ndinakar:

Can't you just check before you do an INSERT?

IF NOT EXISTS( SELECT * FROM YourTable WHERE LinkedItemID = @.somevalue)

BEGIN

-- do the insert

END

That will do what IGNORE_DUP_KEY will do but a UNION will also eliminate duplicates in a Query of more than one table, the main difference between UNION and UNION ALL. It is the reason UNION comes with more restriction than UNION ALL.

|||

I am confused why we need a UNION, foreign key constraint, INDEX COLUMN etc in this scenario. IGNORE_DUP_KEY will work if the index has already been created with that option.

|||

(My table schema is such that it requires the (int) LinkedItemID field to be nullable but still those fields which are set must not be duplicates. I see constraint is out of question )

This was the original poster's need I just showed all can be achived with a combination of SET algebra and the physical. UNION will eliminate duplicates in a query with another column that will introduce duplicates in the result and it comes with compatibility requirement in the table definition, you are checking for duplicates only during insert. Index column include will let that column to be included in a Unique index which require NOT NULL by default, so use the IGNORE_DUP_KEY option and SQL Server just toss the duplicates and continue any insert because ADO.NET does more than one insert. One more thing foriegn key constraints are nullable by ANSI SQL definition.

http://msdn2.microsoft.com/en-us/library/ms190806.aspx

|||

hi,

thanks all of you for your assistance. I still think your solution is far too complicated - there must be some easier way? Why I don't do a check before INSERT - it's because there are about 10 stored procs in my database which work with this particular field and it would be so much easier to do this by some kind of constraint (or even trigger?!)...

|||

EDIT

The quick solution is Unique Constraint because it allows NULLs and the other easy option is through the index column include in a Unique index, you just include it and use the IGNORE_DUP_KEY option, it is not complicated. UNION is complicated to set up if it was not included during the database design. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms191166.aspx