UNIQUE NOT NULL vs PRIMARY KEY

Question

Post reply

UNIQUE NOT NULL vs PRIMARY KEY

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Also, back to the question: UNIQUE NOT NULL vs PRIMARY KEY

PRIMARY KEY term has the clear definition -it's contstraint

But "UNIQUE NOT NULL" what do you exactly refer to?

Do you refer to UNIQUE constraint on not-nullable column ?

or

Do you refer to UNIQUE index on not-nullable column ?

or

Do you refer to filtered UNIQUE index on nullable column which applies to not-null values?

There are differences between constraints and indexes:

If you add UNIQUE constraint, the UNIQUE index will be created by default

If you create UNIQUE index, no constraint will be created by default

Also, indexes allow to include other columns (CREATE INDEX with INCLUDE), but constraints (eg.PK) don't.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

pietlinden SSC Guru Points: 63503 More actions · Answer 2

Create two tables with the same basic structure, one for each design. then run the same INSERT script against each one and do performance testing. The only way to learn SQL is to get your hands dirty.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

deepikamm (3/6/2012)
Please do not comment without knowing the fact that whether i have googled or not.
i thought that the experts in sqlservercentral would give me a better clear answer thats y posted it here.
sorry if am wrong!!

Problem here is that we don't know what you have done on your own to answer this question as you don't say anything in that regard. In addition, your initial question was quite open ended suggesting that you hadn't done any research.

If you are going to ask a question regarding your homework, please show us what you have discovered on your own and what you are having trouble understanding. We are more than willing to help you but we shouldn't have to do the research for you.

Please remember that we are volunteers on this site trying to help others on our free time. The more you show us that you are doing to answer your own questions, the more we will help you fill in the blanks in your understanding.

deepikamm SSCommitted Points: 1681 More actions · Answer 4

/*Problem here is that we don't know what you have done on your own to answer this question as you don't say anything in that regard. In addition, your initial question was quite open ended suggesting that you hadn't done any research.

If you are going to ask a question regarding your homework, please show us what you have discovered on your own and what you are having trouble understanding. We are more than willing to help you but we shouldn't have to do the research for you.

Please remember that we are volunteers on this site trying to help others on our free time. The more you show us that you are doing to answer your own questions, the more we will help you fill in the blanks in your understanding*/

This is a much better answer!

deepikamm SSCommitted Points: 1681 More actions · Answer 5

deepikamm

SSCommitted

Points: 1681

March 6, 2012 at 10:03 pm

#1455728

@all : Thanks for the time!

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 6

Eugene Elutin (3/6/2012)
Much better now!
So, what else we can add:
1. they differ in purpose:
We are using PK for referential integrity (when paired with FK)
While UNIQUE NOT NUL index(key) we use for data consistency and validation
2. Replication will not work on tables without PK even if they have UNIQUE NOT NUL index

To clarify : Transactional Replication will not work, but snapshot replication will work just fine

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

MyDoggieJessie (3/7/2012)
Eugene Elutin (3/6/2012)
Much better now!
So, what else we can add:
1. they differ in purpose:
We are using PK for referential integrity (when paired with FK)
While UNIQUE NOT NUL index(key) we use for data consistency and validation
2. Replication will not work on tables without PK even if they have UNIQUE NOT NUL index
To clarify : Transactional Replication will not work, but snapshot replication will work just fine

So will merge replication and so, interestingly enough, will Change Data Capture even though it uses the transactional replication log reader to read changes.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 8

Yep that is right, transactional replication will not work.

Snapshot and merge replications will still work just fine.

Just was working on setting up one...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]