January 4, 2013 at 12:35 am
Dineshbabu (1/3/2013)
oh.. unique constraint can allow more than one null value.. I don't think so.. Please give me some xample...
Here you are:
create table test (
id int not null,
ref int null
);
alter table test
add constraint uk_test unique (id, ref);
go
insert into test values
(1,null),
(2,null),
(2,1);
go
drop table test
NULL is treated just like any other value, and the combination of the values must be unique. If a unique constraint is defined on a single column, that means the column can only have one NULL value, in a composite key, this is however not so.
January 4, 2013 at 12:50 am
okbangas (1/4/2013)
Dineshbabu (1/3/2013)
oh.. unique constraint can allow more than one null value.. I don't think so.. Please give me some xample...Here you are:
create table test (id int not null,
ref int null
);
alter table test
add constraint uk_test unique (id, ref);
go
insert into test values
(1,null),
(2,null),
(2,1);
go
drop table test
NULL is treated just like any other value, and the combination of the values must be unique. If a unique constraint is defined on a single column, that means the column can only have one NULL value, in a composite key, this is however not so.
Hi,
I accept on multiple columns it is possible.
then with same schema. Is this possible to insert below value?
insert into test values
(NULL,null),
(NULL,null)
No, Not at all..
If i'm not wrong, this cleary shows IN Unique constraint, SQL server considering NULL as other values not as NULL. It just trying to enforce uniqueness of the Key columns. It might be anything NULL or some value..
--
Dineshbabu
Desire to learn new things..
January 4, 2013 at 12:53 am
Dineshbabu (1/4/2013)
okbangas (1/4/2013)
Dineshbabu (1/3/2013)
oh.. unique constraint can allow more than one null value.. I don't think so.. Please give me some xample...Here you are:
create table test (id int not null,
ref int null
);
alter table test
add constraint uk_test unique (id, ref);
go
insert into test values
(1,null),
(2,null),
(2,1);
go
drop table test
NULL is treated just like any other value, and the combination of the values must be unique. If a unique constraint is defined on a single column, that means the column can only have one NULL value, in a composite key, this is however not so.
Hi,
I accept on multiple columns it is possible.
then with same schema. Is this possible to insert below value?
insert into test values
(NULL,null),
(NULL,null)
No, Not at all..
If i'm not wrong, this cleary shows IN Unique constraint, SQL server considering NULL as other values not as NULL. It just trying to enforce uniqueness of the Key columns. It might be anything NULL or some value..
Sorry okbangas..
I think u too xplained the same..
--
Dineshbabu
Desire to learn new things..
January 4, 2013 at 12:56 am
Let me quote myself, NULL is treated just like any other value in a unique constraint. It is the combination of values that has to be unique. It is wrong to say that a single value can only exist once in a column in a unique index, as the combination must be unique, not the individual values. In the same way, it is wrong to say that NULL can only exist once in a column, it is the combination that has to be unique. So, no, you cannot have (null, null) twice in two columns if there is a unique constraint on these two columns, just as you cannot have (1,1) twice.
January 4, 2013 at 1:36 am
Very confusing question, as it could definately be worded better.
The question asked for differences, but the answers were only statements about different properties of a unique index/constraint (what is a unique key?) or a primary key.
And then there are 5 correct answers. Luckily I guessed correctly.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 4, 2013 at 1:53 am
okbangas (1/4/2013)
Let me quote myself, NULL is treated just like any other value in a unique constraint. It is the combination of values that has to be unique. It is wrong to say that a single value can only exist once in a column in a unique index, as the combination must be unique, not the individual values. In the same way, it is wrong to say that NULL can only exist once in a column, it is the combination that has to be unique. So, no, you cannot have (null, null) twice in two columns if there is a unique constraint on these two columns, just as you cannot have (1,1) twice.
+1
Please, my point back!
:-):(:-):(:-):(:-):(:-):(:-):(:-):(:-):(:-):(:-):(
January 4, 2013 at 2:46 am
Not happy with the question. Most of my problems have already been discussed - UNIQUE constraints do allow NULL values (and the "choose 4" made me think that the second option was not marked correct for beiing "too vague", or for not actually describing a difference, but a similarity, so my answer was marked as wrong) and the wording of the answer options could have been better.
<nitpicking>
One comment I have not yet seen is that there actually is no such thing as a "unique key" in SQL Server. There is a PRIMARY KEY constraint, a UNIQUE constraint (note the absence of the word "key" here), and a UNIQUE index.
I din;t think annyone will misunderstand the question based on this wrong term, but I still wanted to point out the correct terms.
</nitpicking>
January 4, 2013 at 3:42 am
The only option that is definitely wrong is number 1.
As has been said, there's not really any such thing as a Unique Key in T-SQL.
'List all the differences...' - there are more than 4 differences (e.g. you can only have one primary key!).
Options 1 and 2 are not differences.
The second reference quoted says: 'UNIQUE constraints allow for the value NULL' - but the 'correct' answer doesn't include option 5.
Option 2 should say 'column(s)' not 'column'.
Apart from that it's a great question.
January 4, 2013 at 6:04 am
Since 5 of the 6 are correct, any combination of 4 of the 5 correct answers should have scored the point. Will those of us penalized for choosing correct answers but not the 'preferred' correct answers be awarded the point?
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
January 4, 2013 at 6:59 am
Poorly worded question. The 4 items I selected were the BEST possible of the 5 that ARE ACTUALLY CORRECT answers AS STATED and has been pointed out by everyone else here already. Sadly, his choice of what is CORRECT are not mine and thus I am WRONG. I'll miss my point. :Whistling:
January 4, 2013 at 7:15 am
Poor wordy weasle.
I like question where the wording is not at issue only the facts.
-1 point for poor question to SQL Server Central.
January 4, 2013 at 7:22 am
This was removed by the editor as SPAM
January 4, 2013 at 7:22 am
SQLRNNR (1/3/2013)
I have to disagree with the correct answer(s). The question asks for 4 correct answers yet 5 are presented. This causes a bit of a guessing game.
+1
I had to guess which 4 of the 5 correct answers were expected. I chose incorrectly.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 4, 2013 at 7:24 am
I don't really like this question.
"They are not the same, but they both enforce uniqueness of the column on which they are defined"
assumes that they can both only be defined on a single column, whereas they can be multiple columns.
If this assumption is made then "The Unique key allows NULL values as a value" is indeed false, as you can have only one. But a unique constraint on 2 columns lets you have null in both values so the statement is true.
"by default a Primary key creates a Clustered Index"
is false. The default is only clustered if there is not already a clustered index.
create table test
(a int primary key,
b int unique clustered
)
will create a nonclustered primary key even though this is not specified.
January 4, 2013 at 7:27 am
SQLRNNR (1/3/2013)
dineshbabus (1/3/2013)
oh.. unique constraint can allow more than one null value.. I don't think so.. Please give me some xample...Here is the example
I must concur, I wasn't thinking exactly along those lines, but those are an even better example.
Since Uniqueness constraints basically enforce what they do with Unique Indexes, I was thinking that nobody uses Unique constraints anymore and just uses unique indexes since they can be filtered. 🙂
Viewing 15 posts - 16 through 30 (of 70 total)
You must be logged in to reply to this topic. Login to reply