SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Primary Key v. Unique Index
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 12/11/2004 :  20:36:21  Show Profile  Visit nr's Homepage  Reply with Quote
>> If I have a new house, then why does my kitchen still need a fresh coat of paint?
How do you find the house? The number has changed so that can't have been how you identify it.
The kitchen that needs a coat of paint is in a different house.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 12/11/2004 :  20:50:31  Show Profile  Reply with Quote
I guess that we all agree that housenumber is a very poor pk for a house !?
A house can be torn down / rebuilt and still have the same housenumber, but it is a different house.
.housenumber has houses.
.house has housenumber.
house is one entity, housenumber is another entity designating a location for houses.

rockmoose
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 12/11/2004 :  21:15:42  Show Profile  Visit nr's Homepage  Reply with Quote
The question is if you change the pk can you talk about it being the same entity.
If you can then you are identifying it by some other means than the pk i.e. the thing that was changed wasn't the pk. (or you could say shouldn't have been).

In your example if the number is the pk then tearing down the house and rebuilding doesn't matter - it's still the same house, just has different attributes.
If the number changes then it's a different house, just happens to have the same attributes.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 12/11/2004 :  21:30:42  Show Profile  Reply with Quote
A PK uniquely identifies an entity/object/thing.
A PK is stable if the probability of PK update -> 0%.

If we have attribute(s) A of entity E that are considered as PK for E.
Then if an update of A implies a logical delete+insert, A is not a good candidate for PK.
Simply because we are removing a "old" instance of E and replacing with a "new" instance.
If an update of A would imply a logical alteration of the original value and the old value is of
no importance then A is candidate for PK.

David,
Auditing PK updates is a bad sign, because it implies that the value of the audited PK is actually
just an attribute of the "true" PK (whatever that is) that needs to be kept track off?

Anyway I find this interesting, and I hope I managed to make some sense...

rockmoose
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 12/11/2004 :  21:44:18  Show Profile  Reply with Quote
quote:
Originally posted by nr

The question is if you change the pk can you talk about it being the same entity.
If you can then you are identifying it by some other means than the pk i.e. the thing that was changed wasn't the pk. (or you could say shouldn't have been).

In your example if the number is the pk then tearing down the house and rebuilding doesn't matter - it's still the same house, just has different attributes.
If the number changes then it's a different house, just happens to have the same attributes.

Well, yes, do we have a misunderstanding here?
The tearing down example was given to point out that housenumber is a bad key. ( housenumber = number on street, physical location of house, which is just a temporal property of the actual house ).

rockmoose
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 12/12/2004 :  05:56:57  Show Profile  Reply with Quote
quote:

The question is if you change the pk can you talk about it being the same entity.



It all depends on the language.

To the database model it definately is not the same entity, but to the outside observer it can be interpreted as the same.

In my case, it is an event that is taking place. The natural key is the Client and the Time of the Event. Prior to actually performing the event, things about the event may change, including the time but never the client. According to the client, the "event" before or after the update is still considered the same only now it is "rescheduled" (A time update and hence the audit stuff)

Right now I have the EventID purely as an artifical key (for audit only) and the natural key is cascaded to the pre-event tables.

To get the best of both worlds, I should add EventID as the FK in the pre-event tables and the natural key for post-event RI?






DavidM

"Always pre-heat the oven"
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 12/12/2004 :  09:43:09  Show Profile  Visit nr's Homepage  Reply with Quote
>> To the database model it definately is not the same entity, but to the outside observer it can be interpreted as the same.
Can it?
Sounds like in your example you are missing some info.
The pk is the client and time. You have two events with same client different times. They both change attributes including times - how do you match up the new entries with the old. The only way is via other attributes indicating that client, time isn't the pk.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 12/12/2004 :  12:50:05  Show Profile  Reply with Quote
>> The pk is the client and time.
Clients cannot have more than 1 event at the same time?
An event can only include 1 client?

rockmoose
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 12/12/2004 :  13:36:22  Show Profile  Visit nr's Homepage  Reply with Quote
If that's the case then sounds like the pk should be client, event. Time being an attribute.


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 12/12/2004 :  16:01:27  Show Profile  Reply with Quote
Thankyou both again,

There is no doubt that Client and Time are a candidate key and MUST be marked unique. There's an overlap constraint being enforced as well.

Let's go back to the key stability issue..

Prior to the event taking place (Pre-Event), the event itself is "unstable", it moves and can be cancelled. When the event is/has taken place (Post-Event), the event is 100% stable.

As I stated earlier, multi-row update auditing (the pre-event instability) has been resolved with the artifical key EventID. EventID is not considered part of the DB model, just an artifact to get a particular reporting requirement filled.

I suppose that is the problem.. the physical implementation of the model.

Now given that the artifical key is in the physical table, I may as well leverage it to eliminate cascading to the pre-event tables (is that a good reason?), but I see no reason at all to use it in the post-event table where it will be 100% stable.

Do you still place a unique constraint on all your candidate key that are "unstable" and not used in RI? If so, then when we say, "poor key choice", we really mean "poor RI key choice".

Oh yeah... damn I hate the term PK!


DavidM

"Always pre-heat the oven"
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 12/12/2004 :  19:04:01  Show Profile  Reply with Quote
>> Do you still place a unique constraint on all your candidate key that are "unstable" and not used in RI?
Yes.

Could it be argued that a "scheduled event" is not the same entity as an "event" that actually has occurred ?
-- actual events that have taken place
Event
(
	ClientId not null references Client(ClientId),
	EventDate not null,
	unique(ClientId,EventDate)
)
-- scheduled events
Scheduled_Event
(
	EventId not null unique,
	ClientId not null references Client(ClientId)
)
-- the dates chosen for this scheduled event, can be several
Scheduled_Event_Dates
(
	EventId not null references Scheduled_Event(EventId),
	TimeOfSchedule not null, -- when the scheduled date was set by someone, last is valid schedule date
	ScheduledDate not null,
	unique(EventId,TimeOfSchedule)
)
-- link between the scheduled events and the actual events that have taken place
Event_Scheduled_Event
(
	EventId not null unique,
	ClientId not null,
	EventDate not null,
	unique(ClientId,EventDate),
	foreign key(EventId,EventDate) references Scheduled_Event_Dates(EventId,ScheduledDate),
	foreign key(ClientId,EventDate) references Event(ClientId,EventDate)
)


rockmoose
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1600 Posts

Posted - 12/12/2004 :  19:09:33  Show Profile  Reply with Quote
Remember that we are modeling the REAL world. The extent to which our model mirrors the real world is how useful our model is. In the real world, changing the number does NOT make it a new house.

What we have is a house. The number (and street and city, etc.) is a characterisitc of the house. The house number is useful as a component of the house's characterisitics because it is unique to houses on this street, in this city, etc. This is what lets us use the house number as (at least part) of the primary key. But the fact that it is unique in no way implies that it is immutable. I can change the number, as long as it remains unique. I can't, for example, change it to be my neighbor's number.

How do I find the same house that has a different number? By using the new number - which is unique. Why? Because the house number is a characteristic of the house and it is the house that I am modeling.

HTH

=================================================================

Happy Holidays!
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 12/12/2004 :  19:45:50  Show Profile  Reply with Quote
quote:

Could it be argued that a "scheduled event" is not the same entity as an "event" that actually has occurred ?



Absolutely! And this is already reflected in another table (one-to-one)

That's a pretty good schema Rockmoose, considering the level of detail I have given you!
Are you sure about the RI to Event from Event_Scheduled_Event? And where would I implement a time overlap constraint? Don't answer mate.. just me thinking out loud...

If I incorporate the audit table into the schema, it matches "fairly" closely.

In summary... It depends.

DavidM

"Always pre-heat the oven"
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 12/13/2004 :  18:48:17  Show Profile  Reply with Quote
Nigel definitely has a point on PK not being updateable.
And here I differentiate between PK and UNIQUE CONSTRAINT,
A PK is thee immutable identifier of an Entity/Object/Event.
A UC is a constraint saying that for any given time there cannot exist >1 instance with the same values.

If one could postulate that PK's are invariant, then a lot of problems with cascading RI would be gone!

>> Oh yeah... damn I hate the term PK!

Speaking relationally, CANDIDATE KEY = PK, right ?
(but in sql we can only have 1 PK!)

>> Do you still place a unique constraint on all your candidate key that are "unstable" and not used in RI? If so,
then when we say, "poor key choice", we really mean "poor RI key choice".

The "unstable" key is maybe not a key at all, but just a UC.

-----------------------------------------------------------------------------

Now to the House example:
I think that number(street) is not a good pk for house.
The physical location and the actual house are different things.
streetnumbers can change, houses can be replaced or moved (at least in my country!)
and these events can occur independently of each other, suggesting that the PK of House
is something else than the number(street).
( We should of course put a UC constraint on it though, because that is the "REAL world model" ! )

.... hmm ....

rockmoose
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 12/13/2004 :  19:12:43  Show Profile  Reply with Quote
>>>>A PK is the immutable identifier of an Entity/Object/Event.
I don't buy the term "immutable". Why does identity have to be fixed? It might make it "easier" to track but it will still be unique and hence identifiable.

>>>Speaking relationally, CANDIDATE KEY = PK, right ? (but in sql we can only have 1 PK!)

Sort of... A Candidate Key is any set of attributes that uniquely identify the entity. RM does not have a concept of PK, only SQL does. To overcome this, we use UC's. If a UC is marked as NOT NULL, then it is exactly the same as a PK from a data integrity point of view. I would be shattered to hear otherwise.

In my situation, I have a artifical candidate key (EventID) that is used in RI for Pre-Event changes and use the natural candidate key (Client,Time) for RI in post event tables where its stability is guaranteed. It "looks" kind of strange but seems to satisfy the requirements at the moment


DavidM

"Always pre-heat the oven"
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 12/13/2004 :  20:34:30  Show Profile  Reply with Quote
>>> I don't buy the term "immutable". Why does identity have to be fixed? It might make it "easier" to track but it will still be unique and hence identifiable.

I think this boils down to this:
The values are taken from a certain domain, when a value is changed in the domain is it logically an insert/delete or a straight update ?
{"red"} -insert> {"red","OxFF0000"} -delete> {"OxFF0000"}
OR
{"red"} -update> {"OxFF0000"}
Is it the value or concept that is unique within the domain ?

>> Sort of... A Candidate Key is any set of attributes that uniquely identify the entity. RM does not have a concept of PK, only SQL does. To overcome this, we use UC's. If a UC is marked as NOT NULL, then it is exactly the same as a PK from a data integrity point of view. I would be shattered to hear otherwise.

True, in RM one can possibly designate/"say" one of the CK to be a PK, but it's mathematically the same thing.
Ok, so then you never had a problem about "choosing pk"
You don't have to be shattered, I cannot think up any counterexamples. A key is a key.

>>In my situation, I have a artifical candidate key (EventID) that is used in RI for Pre-Event changes and use the natural candidate key (Client,Time) for RI in post event tables where its stability is guaranteed. It "looks" kind of strange but seems to satisfy the requirements at the moment

Doesn't seem so strange right now.
But logically for Pre-Event "re-schedules" are you not inserting a new scheduled time and deleting the old one ?

rockmoose
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1600 Posts

Posted - 12/14/2004 :  16:43:59  Show Profile  Reply with Quote
>>>RM does not have a concept of PK, only SQL does.<<<

WHAT ????? Ouch!

From Codd's Law:

2. The Guaranteed Access rule: Each item of data in an RDBMS is guaranteed to be logically accessible by resorting to a combination of table name, PRIMARY KEY VALUE, and column name.

(Quoted from The Relational Model for Database Management, Version 2; Addison-Wesley; 1990; AUTHOR Codd, E.F.)

I've heard you say a lot of pretty smart things, RockMoose, but that sure wasn't one of them.

HTH

=================================================================

Happy Holidays!
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 12/14/2004 :  16:56:16  Show Profile  Reply with Quote
quote:

I've heard you say a lot of pretty smart things, RockMoose, but that sure wasn't one of them.



Ummm... that was me who said that.

Let me rephrase that.. the current RM does not have a concept of a Primary Key. All keys are considered equally important. "Primary" is an old term that SQL has run with...

With no disrepect to the late Dr Codd, the original model has changed a fair bit... NULLs where dropped, 6NF was proposed and the definition of "simple data types" was redefined.

The 12 laws are not a definition of RM, but more a "checklist" for RDBMSs

DavidM

"Always pre-heat the oven"
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 12/14/2004 :  17:28:22  Show Profile  Reply with Quote
Now i can quote myself...
quote:
...in RM one can possibly designate/"say" one of the CK to be a PK, but it's mathematically the same thing.

What byrmol says, a Relation can have a number of CANDIDATE KEYS, they are considered equally important.
One of the CANDIDATE KEYS can be chosen as PK
quote:
I've heard you say a lot of pretty smart things, RockMoose, but that sure wasn't one of them

Thank You Bustaz , well this thread has been a bit flaky and contradictory...
and I apologize for any confusion that has arisen due to that.



rockmoose
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 12/14/2004 :  18:01:28  Show Profile  Reply with Quote
Nicely put...There is a bucket of confusion out there.. From you links...

"one or more attributes which will uniquely identify one tuple in a relation. A candidate key is a potential primary key."

and

"Every relation has at least one candidate key (the primary key); some have more than one candidate key"

and my favourite confusing definition (from a SQL vendor of course)...

" A candidate key is similar to a primary key... When more than one primary key exists, the alternatives are called candidate keys. Of the two or more candidate keys, a primary key is selected, with the remaining keys becoming secondary keys."

But how can you have "more than one primary key"?

DavidM

"Always pre-heat the oven"
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000