Subject RE: [firebird-support] Primary keys - was Copy records
Author Planles
> > Table1:
> > M_id, M_desc, M_desc2,...
> So M_ID is Unique PRIMARY_KEY = M_id


> > Table2:
> > MC_id, MC_key, MC_qty,...
> >
> > Each record in Table1 can have multiply records in Table2.
> So MC_key may not be unique?


> But MC_id + MC_key is unique ? ( Different MS_id's could have the same
> MC_key ? )

Pretty basic stuff, as you see.

> > Now I had M_id in Table1 as primary key, and MC_id and MC_key
> from Table2 as
> > primary key. MC_id is equal to M_id, and MC_id and MC_key is a
> unique pair.
> >
> > Could you explain me, how should I proper do this relationship.
> Table2 PRIMARY_KEY = ( MC_id, MC_key )

I don't understand the difference in your "PRIMARY_KEY = ( MC_id, MC_key )"
or if I say, that table2 has two primary keys.

> I have records in master table 1 to n
> and sub records in detail table 1(1 to n)
> so the the detail table uses record and subrecord to create a primary
> key. All details will have a sub record of 1, but only one for each
> master record.
> Hope that covers it ;)

In theory I understand it now. Also in praxis it works in my project. But
I'm still not sure, if I'm doing it the right way, so I'll put some more

I'm using CB6, and for database creation IBeasy+. I don't create database
tables programatically, since I don't know how to (yet).

I have two datasets, DS1 and DS2. DataSource from DS2 is connected to

sql in DS1:

select * from TABLE1

sql in DS2:

select * from TABLE2 where MC_ID = :M_ID;

This works fine: I select the record in Table1, and in table2 are all proper
records displayed.

And for example, delete sql in DS2 is following:

delete from TABLE2
MC_ID = :OLD_MC_ID and
MC_key = :OLD_MC_key

modify sql in DS2:

update TABLE2
MC_key = :MC_key,
MC_qty = :MC_qty
MC_ID = :OLD_MC_ID and
MC_key = :OLD_MC_key

Is this the "normal" way to create a simple master-detail relationship, or
did I completely missed something.
Can you recommend me some good tutorials on this ?