Why must tables have PKs?

Question

Why must tables have PKs?

Viewing 9 posts - 16 through 23 (of 23 total)

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

andersg98 SSCertifiable Points: 5180 More actions · Answer 1

As strange as it may sound, people make multiple payments for the same amount on the same day to the same account.

tosh tosh SSC Enthusiast Points: 131 More actions · Answer 2

That doesn't sound strange. What does sound strange is that in this day and age there would be no other distinguishing characteristic for a transaction like say... the TIME

Antares686 SSC Guru Points: 125444 More actions · Answer 3

PK is always neccessary but to have some way to create relationships or ensure uniqueness of data you need to have at least a UNIQUE CONSTRAINT. PK's are primarily to enforce uniqueness of data.

SQLBill SSC Guru Points: 51440 More actions · Answer 4

Unless I'm wrong, can't you define a Primary Key as a combination of columns?

In your scenerio, you can't use the customer credit card number as a Primary Key because they can make payments more than once a day.

Why not have use credit card number and datetime of payment as the key? The combination should be unique unless you are only recording the date.

-SQLBill

Antares686 SSC Guru Points: 125444 More actions · Answer 5

Sorry should have read "isn't neccessary" in my statement.

andersg98 SSCertifiable Points: 5180 More actions · Answer 6

Yes, the date field does not include a time component. The duplicates are identical. The credit card payment data has far less chance of duplicates than another extract we have, checking account transactions. This table has many duplicate rows. The source we extract from does not provide a reliable unique identifier (according to the developer) so, I suggested an Identity field to make the rows unique within the table. Again, it is argued that we do not gain anything from this.

The question is not should all tables have PK constraints declared. It's, Must all tables have unique rows?

tosh tosh SSC Enthusiast Points: 131 More actions · Answer 7

tosh tosh

SSC Enthusiast

Points: 131

March 23, 2004 at 11:45 am

#500090

The* answer is no.

*my

chisholmd SSCrazy Points: 2490 More actions · Answer 8

My 2bits. Every table I ever create has as its first column "UID" which is an idenity column and is set a primary key.

In the very rare circumstance when I end up with a table without a primary key independent of the data I am always disappointed.

I have a friend and he consistantly primary keys on compound fields of actual data and it drives me nuts when I need to work with his schemes.

Is the performance hit of have having an "uneccessary" primary key even measurable?

In your case, You say that your table has duplicate rows, I assume that to mean "identical" rows. Where the values of all the fields are identical but represent two real world items/events or whatever. Without a primary key you can not differentiate between them (I suppose their argument is that they never have to.)

If you do have identical rows, you couldn't have anything but an identity column as your primary key. If you have rows that are identical then even if you created a compound key on all fields, you would still violation the uniquess of your key.

dave

Dave

Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)

Antares686 SSC Guru Points: 125444 More actions · Answer 9

Sorry there but as for unique rows I would have to say yes.

The reason is if you need to delete rows the compiler is unable to distinguish between two rows it will throw and error and not do the delete. At minimum have at least one unique row to avoid this issue unless the table will be truncated then all the data will go away. Plus if someone makes two of the same transactions in a system they will generally want to know the date and time of the occurrance so a datetime (even if not truely unique per row but per same row) will be of great importance then it may take 2 or 3 rows to have a true unique relationship but that is the key.