Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

UNIQUE constraint Expand / Collapse
Author
Message
Posted Thursday, December 20, 2012 12:33 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 11:44 PM
Points: 2,423, Visits: 754
Got it wrong for the same reason. I thought about unique filtered indexes but that is not the same as unique constraints which allow one null only.
Post #1398826
Posted Thursday, December 20, 2012 12:35 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:20 PM
Points: 17,600, Visits: 15,462
Stewart "Arturius" Campbell (12/20/2012)
IMHO, I think the correct answer here will be "It depends".
It depends on whether filtered indexes are utilised or not, etc.

However, it could be that the PO intended that the use of filtered index(es) is implied (or should be assumed)...


I disagree that "It depends" with this question. It was asked if it was simply possible. No need to consider filtered indexes, you just need to know if there is any possible way to insert multiple nulls into a unique constraint - imho.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1398827
Posted Thursday, December 20, 2012 12:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:29 AM
Points: 12,945, Visits: 10,716
SQLRNNR (12/20/2012)
Stewart "Arturius" Campbell (12/20/2012)
IMHO, I think the correct answer here will be "It depends".
It depends on whether filtered indexes are utilised or not, etc.

However, it could be that the PO intended that the use of filtered index(es) is implied (or should be assumed)...


I disagree that "It depends" with this question. It was asked if it was simply possible. No need to consider filtered indexes, you just need to know if there is any possible way to insert multiple nulls into a unique constraint - imho.


A unique constraint is not the same as a unique index.

Anyway, your explanation of a constraint over multiple columns demonstrates that the question has the correct answer (albeit a little tricky ), but just an incorrect explanation.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1398828
Posted Thursday, December 20, 2012 12:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:05 PM
Points: 157, Visits: 292
Hello,
"Data Integrity" can be enforced in sql server through
PRIMARY KEY Constraint
FOREIGN KEY Constraint
UNIQUE ConstraintCHECK Constraint
Default Definition
so on..

My point here is all about enforcing "Unique Constraint".
This can be done in several ways.

Query 1. Add Unique Constraint
ALTER TABLE dbo.<tablename> ADD CONSTRAINT
<constraint_name> UNIQUE NONCLUSTERED
(
<columnname>
) ON [PRIMARY]

Query 2. Add Unique Index
CREATE UNIQUE NONCLUSTERED INDEX
<index_name> ON dbo.<tablename>
(
<columnname>
) ON [PRIMARY]


Technically there is no difference between Unique Index and Unique Constraint. Even though syntax are different the effect is the same.
Unique Constraint creates Unique Index to maintain the constraint to prevent duplicate keys. Unique Index also creates index that are physical structure that maintain uniqueness. It is a convenient way to enforce a Unique Constraint for SQL Server.
In above case "Unique Constraint" allows one "NULL".

But my question was how the above Unique Constraint can be created but allowing multiple NULL values and not the RULES of Unique constraint.

I just want to bring that it is possible to enforce Uniqueness in a column but allowing multiple "NULL" values and one of the way to implement this is using filtered index.

Ref: http://blog.sqlauthority.com/2007/04/26/sql-server-difference-between-unique-index-vs-unique-constraint/
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/a298b63b-e1eb-4b31-a2d7-64e1fe493b0a
Thanks
Gopi
Post #1398830
Posted Thursday, December 20, 2012 12:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
SQLRNNR (12/20/2012)
Stewart "Arturius" Campbell (12/20/2012)
IMHO, I think the correct answer here will be "It depends".
It depends on whether filtered indexes are utilised or not, etc.

However, it could be that the PO intended that the use of filtered index(es) is implied (or should be assumed)...


I disagree that "It depends" with this question. It was asked if it was simply possible. No need to consider filtered indexes, you just need to know if there is any possible way to insert multiple nulls into a unique constraint - imho.


Let us all "Agree" to "Disagree" and come to a point that today's Question has a take-away on Filter Index


~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1398831
Posted Thursday, December 20, 2012 12:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 11:44 PM
Points: 2,423, Visits: 754
SQLRNNR (12/20/2012)
I don't disagree with the correct answer. I disagree with the explanation. I also do disagree with many of the complaints thus far about the question. You can most certainly have multiple null values in a unique constraint. Don't constrain yourselves to a narrow scope of a constraint on a single column - where you can have only one null value.

ALTER TABLE sometest
ADD CONSTRAINT someconstraint UNIQUE (testid,col1,col2);


Disagree!
This constraint can not represent value of Null even if all columns are populated with Nulls.
Besides, only one occurrence of Nulls in all columns is allowed.
Post #1398832
Posted Thursday, December 20, 2012 12:47 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:20 PM
Points: 17,600, Visits: 15,462
dawryn (12/20/2012)
SQLRNNR (12/20/2012)
I don't disagree with the correct answer. I disagree with the explanation. I also do disagree with many of the complaints thus far about the question. You can most certainly have multiple null values in a unique constraint. Don't constrain yourselves to a narrow scope of a constraint on a single column - where you can have only one null value.

...
Besides, only one occurrence of Nulls in all fields is allowed.


True only one occurence of null in all fields is allowed. But that is not the question. The question was simply if multiple nulls could be inserted.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1398833
Posted Thursday, December 20, 2012 12:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:05 PM
Points: 157, Visits: 292
So far Jason is following me
Post #1398835
Posted Thursday, December 20, 2012 12:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:29 AM
Points: 12,945, Visits: 10,716
Gopi S (12/20/2012)

Technically there is no difference between Unique Index and Unique Constraint. Even though syntax are different the effect is the same.
Unique Constraint creates Unique Index to maintain the constraint to prevent duplicate keys. Unique Index also creates index that are physical structure that maintain uniqueness. It is a convenient way to enforce a Unique Constraint for SQL Server.
In above case "Unique Constraint" allows one "NULL".


Technically, there's a difference. Logically yes, they achieve the same, but technically they are different. An index is not a constraint and vice versa.

I know the first hit on Google is the blog post of Pinal Dave who says they are the same, but if you look a bit further on the search results, you'll also find posts that contradict this. By the way, to avoid copyright infringement or plagiarism, you should include a reference to the blog of Pinal Dave, not just copy paste its contents and pretend it's yours.

Difference Between Unique Index vs Unique Constraint




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1398836
Posted Thursday, December 20, 2012 12:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:29 AM
Points: 12,945, Visits: 10,716
Gopi S (12/20/2012)
So far Jason is following me


Yes, by saying your explanation is insufficient...

edit: I forgot to mention: thanks for the effort to create and submit a question. Hopefully you keep this up and hopefully there won't be any confusion on your next question




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1398837
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse