How to find candidate keys

Question

How to find candidate keys

Viewing 7 posts - 16 through 21 (of 21 total)

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

drew.georgopulos SSCarpal Tunnel Points: 4931 More actions · Answer 1

my feeling is that using an identity without a syntactic uniqueness check makes the primary key a tautology because there can exist syntactically identical rows (i mean two rows that say exactly the same thing) that are 'promoted' to uniqueness because they differ by the identity.

thanks very much

drew

drew.georgopulos SSCarpal Tunnel Points: 4931 More actions · Answer 2

there is the wrinkle of refills i did not expose earlier (and there are really two date fields at play, date written, date filled, with number of refills and days supply)...enforcing integrity declaritively turned out a little more complicated than it sounded back in the seminary :hehe:

Jared SSC Guru Points: 61793 More actions · Answer 3

I would think there would be a uniquely generated prescription_id for each entry. That makes logical sense to me. Hence, the same person could have 2 of the same script and the system itself would generate 2 unique ids for them.

Jared
CE - Microsoft

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 4

http://www.sqlservercentral.com/Forums/Topic1274466-391-1.aspx

drew.georgopulos (3/28/2012)
a candidate key is an attribute or attributes that do not have more than one row in a relation.

A candidate key has to be a bit more than just unique. It must be irreducibly unique (i.e. a minimal superkey) - meaning that no attribute can be removed from it without losing the uniqueness property. What is meant by uniqueness is that it must be unique for all possible values of a relation variable and not just for one possible relation value at some point in time. A candidate key must not include nulls in any of its attributes (a consequence of the uniqueness requirement).

In practice we are usually most interested in the keys we want to enforce in the database rather than ones that conjecturally could turn out to be present in data. For most purposes, if the key is not actually going to be enforced (either through integrity constraints or through the mechanisms used to update the database) then there probably isn't much use calling it a key.

drew.georgopulos (3/28/2012)
a primary key is the chosen candidate key that dovetails most closely with the language of the business drew

That's an interesting and probably rather controversial point of view. I may be wrong but I suspect that many people reading this would prefer to call a surrogate key of a table the "primary" one even though such a key is usually not part of the language used by the business. The key that the business is familiar with is very often not the one designated as "primary" by the database developer. In my opinion this is not a matter of any great importance however. There is no special property that a "primary" key has that can't also be true of other keys in the same table. Therefore there is no particularly strong reason to prefer any one key (or even more than one?!) as a "primary" one. Designating a primary key is only as important as you want it to be. A primary key is a candidate key and that's as much as needs to be said about it.

drew.georgopulos SSCarpal Tunnel Points: 4931 More actions · Answer 5

thanks very much, i appreciate the reminder.

would you mind please commenting on the idea that without a uniqueness check, surrogate keys promote duplicates to uniqueness? maybe that wasnt phrased in the most unambiguous way possible;

--drop table names

create table names (id int identity, name varchar(10) )

go

alter table names add constraint pk_names primary key (id)

go

insert names values('Drew'),('Drew'),('Drew')

go

select * from names

Without the surrogate key, i can't tell one from another.

thanks very much

drew

Jared SSC Guru Points: 61793 More actions · Answer 6

drew.georgopulos (3/29/2012)
thanks very much, i appreciate the reminder.
would you mind please commenting on the idea that without a uniqueness check, surrogate keys promote duplicates to uniqueness? maybe that wasnt phrased in the most unambiguous way possible;
--drop table names
create table names (id int identity, name varchar(10) )
go
alter table names add constraint pk_names primary key (id)
go
insert names values('Drew'),('Drew'),('Drew')
go
select * from names
Without the surrogate key, i can't tell one from another.
thanks very much
drew

1. If the table is only to hold distinct first names, why would you insert 3 Drews (you can have a uniqueness check without it being a primary key)? Why wouldn't you have a constraint on name?

2. If this was an employee table, it would be irrelevant to tell them apart based on name. You would tell them apart based on id because name is just a descriptor for the id.

Jared
CE - Microsoft

drew.georgopulos SSCarpal Tunnel Points: 4931 More actions · Answer 7

i was just trying to exercise an example.

sorry it didn't make it.

back to work <g>

thanks