PK column don’t have constraint?

Question

PK column don’t have constraint?

SQL Galaxy

One Orange Chip

Points: 29472
More actions
March 8, 2014 at 4:37 am

#396388

Hi,
I verified the one of the vendor production database,
By default PK does not allowed duplicate values on table also not allow NULL value, Why again need to create ADD CONSTRAINT against to primary key filed? is the cause of clusetred Index to be fast?
Is the logic binding at application side, so that CONSTRAINT not included in PK filed?
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have a unique identity which helps to find a particular record in a table more easily and quickly for performance side.
Thanks
ananda

Viewing 9 posts - 1 through 8 (of 8 total)

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

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

ananda.murugesan (3/8/2014)
By default PK does not allowed duplicate values on table also not allow NULL value, Why again need to create ADD CONSTRAINT against to primary key filed?

You mean the primary key constraint? Or some other constraint?

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

Grant Fritchey SSC Guru Points: 398898 More actions · Answer 2

Maybe I don't understand the question, but you create a primary key using the add constraint command. It just so happens that any kind of unique constraint, and the primary key is a type of constraint, needs to have an index. It's just how SQL Server satisfies the constraint. Whether or not the primary key is clustered is something you can decide. It doesn't have to be. You can create a non-clustered primary key.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Anoo S Pillai Mr or Mrs. 500 Points: 547 More actions · Answer 3

By default as well as ALWAYS primary key is not null and unique, some other constraints (like a check constraint)on a primary key column may be reasonable.

What is the additional constraint that you noted on the table?

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

Let me explain the table defination

Table TBL_UploadData_TRAN

Data_ID - column is primary key already defined, but constraint is not added in that PK column. I verified no other columns does not included check and other constraint values. Does application reason vendor not added in constraint?

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

Still not following.

Are you talking about the primary key constraint? Or some other constraint? What exactly is the problem here?

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

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 6

GilaMonster (3/10/2014)
Still not following.
Are you talking about the primary key constraint? Or some other constraint? What exactly is the problem here?

Yes. I am talking about Primary Key constraint., Not for other constraint

Primary consrtaint not added into existing database design, what could be reason? or are they forget to add? or application using something without primarykey constraint?

thanks

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

Ok, so you don't actually have a PK at all. Could be anything. Ask the vendor, they're the only ones who can tell you why the constraint isn't there.

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

Grant Fritchey SSC Guru Points: 398898 More actions · Answer 8

ananda.murugesan (3/10/2014)
GilaMonster (3/10/2014)
Still not following.
Are you talking about the primary key constraint? Or some other constraint? What exactly is the problem here?
Yes. I am talking about Primary Key constraint., Not for other constraint
Primary consrtaint not added into existing database design, what could be reason? or are they forget to add? or application using something without primarykey constraint?
thanks

It's pretty likely they didn't add it on purpose through ignorance. It's shocking just how little knowledge organizations that build databases in SQL Server to support their applications have about how SQL Server works. But, to know for sure, Gail's right, you have to contact them.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning