Re: [SQLObject] Re: SQLObject

[Edmund L. <el...@in...>]

On 04/10/2003 02:06:44 AM Ian Bicking wrote:

>On Thu, 2003-04-10 at 00:38, Edmund Lian wrote:
>> Composite keys are used an awful lot in any schema of moderate
complexity,
>> and they are important (when used with foreign key constraints) for
>> enforcing relational integrity. If the aim is for SQLObject to support
>> complex projects, then composite key support would be pretty important.
>
>Can you describe some situations where they'd be used?  For instance, if
>either key was modified at any time that'd cause a lot of problems for
>SQLObject.  If not, then what would be the problem with a third simple
>ID/PRIMARY KEY column?

Hmmm... Here's a contrived example that typifies how I use them.

create table category (
    name     varchar(100),
    --
    constraint category_pk
        primary key (name)
);

create table item (
    name    varchar(100),
    --
    constraint item_pk
        primary key (name)
);

create table category_item_map (
    category    varchar(100),
    item        varchar (100),
    --
    constraint category_item_pk
        primary key (category, item),
    constraint category_item_category_fk
        foreign key (category) references category (name)
        on update cascade
        on delete cascade,
     constraint category_item_item_fk
        foreign key (item) references item (name)
        on update cascade
        on delete cascade
);


The argument against using real rather than surrogate primary keys (which
is what you are doing) is typically that primary keys  have business
meaning, and that changing them becomes difficult because the changes in
one table must be somehow cascade through all the other tables that use the
primary keys of one table as foreign keys.

There are really two separate issues here. Let's look at the first
one--that it is bad to have primary keys which have business meaning. Yes,
they do have business meaning, because they are integrity constraints.
That's what you want. Without integrity constraints, the data in the DB has
no meaning.

Constraints are to data as are grammar is to words. Without grammar, a page
of written text would be just a jumble of words. You derive meaning from
written text only if the reader and writer agree to certain rules of
grammar, which constrains the words to have certain order, roles, etc. To
push the point further, consider three words: "eat", "I", and "meat".
Without grammatical constraints, and of the following is possible:

meat eat I
eat meat I
meat I eat
meat eat I
I meat eat
I eat meat

Now suppose we agree to some constraints--grammatical rules. The rules we
agree to are:

Phrases must be in subject-verb-object order
Action words are verbs
Pronouns are subject nouns
+ a few other rules which I won't bore you with.

Once we constrain the words with grammar, the only valid combination is: I
eat meat

So, it is critically important to have constraints in the DB. They
determine the semantics of the data almost completely. To use surrogate
keys instead of real keys weakens the integrity constraints substantially.


The second issue--that real primary keys are troublesome because when the
business meaning changes, they have to be changed, and this is tough to do.
This argument might be true in Oracle, but it is not true in PostgreSQL and
a few other databases that support the "on update cascade" clause in
foreign key constraints.

With the table definitions I have above, an update or deletion to a primary
key in the category or item table cascade through to all the tables that
use the updated primary key as a foreign key. This is so very nice, and is
one reason why I say that Oracle is actually more backward than PostgreSQL
in some respects.

...Edmund.