Re: GUID as primary key

Andreas Mosmann wrote:

> There are some reasons for not using sequences
> - a sequence looses the effect if only 1 row is inserted with a number
> higher than AnySequenceName.CurrVal, so you have to fix this in an
> application.

The same possibility exist with a GUID. A manual insert, bypassing the "column generator" for that column (e.g. the trigger/function calculating the GUID or SEQUENCE for the column) will cause problem either way. And you will have to fix the data - either way.

> - I do not know a possibility to generate keys that are composed by some
> other values (f.e. an object-number consists of 4digits for the actual
> department when it is created and 6 digits counting 0..999999). To do
> this I had to use a stored procedure, that evaluates the key

Relational theory says that
a) sequences have no meaning, excecpt as a (semi-random) unique identifier
b) a column describes a single value and only a single value

Thus having any column where the 1st n digits mean ABC and the last x digits means ZYX.. that is oh so wrong that it makes me want to reach for my lead pipe and start foaming at the mouth.

> - our special case:

There are no special cases in relational theory. IMO. You either do it correctly. Or you don't.

> 1 Database but Several Departments sometimes without connection
> So we will solve this problem by doing the following:
> * When connection exists the historical data are copied to the small dbs
> (XML or something like that) and the moving date (?) are copied to the
> database. Because of every department only inserts his own data there is
> no trouble with data but with a simple sequence (remember, sometimes
> there is no connection). If we use GUID we will (hopefully) fix this
> problem.

This architecture sounds very flawed and very problematic to me. Oracle connections can be stateless in the sense that they can service a stateless web client. I find it hard to see the logic in pumping data into XML and then pumping that into the database.

It is a fact that dealing with batch processing you will have exceptions that will screw up your data that will require a lot of frustration and sweat and tears (never mind the drastic increase in coffee consumption) to solve.

And what for? Not saying that this is it in your case Andreas.. heck, I have no idea what your arguments and reasons are for your XML decision - so do not take the following personally at all:

but too many times I have seem XML being used just because it is buzz word, it is "kewl" - and that is just plain and utter bullshit. (that said, XML is freakingly kewl when dealing with speech recognition rule sets - so I'm not anti-XML and pro-whatever)

> See the paragraphes above: If I could f.i. give sys_guid() or MyFunction
> as a Default value the application development is much easier. You could
> use this value directly for Master-Detail-Relations if you want, you
> could use sysdate ore som functions depending on sysdate by default ...
> AFAIK this feature is in more than 1 Database, why shouldn't it in
> Oracle?

True that it is a feature in other databases. Yes, I agree it should be one in Oracle too.

I'm simply questioning the reasoning for wanting to do it that way. I've heard some counter arguments why that is a bad idea and to be honest, I find it hard to say why it would be a good idea despite me always doing sequences via triggers in Oracle.

--
Billy