Foreign key support in TDataset
lazarus / 16-Databases

----------------------------------------------------------------------
Leledumbo · 2010-03-13 04:53

How can I create table with foreign keys using TDataset descendants
(without direct SQL)?

----------------------------------------------------------------------
mrmikehicks · 2010-03-15 04:28

Not sure if this will help, but I grabbed a copy of a free program
called sqlitestudio that I use to examine and create tables for my
sqlite3 program. It will allow you to setup foreign keys and select
what table and field to reference it to, etc. Neat program. Really
helps me with the development of my sqlite database program.

----------------------------------------------------------------------
davesimplewear · 2010-03-15 04:44

AFAIK you can use MySQLAdmin and the equivalent for Postgresql to
enter foriegn keys, not sure if you can programmatically, although I
can't see why not.

Regards
Dave

----------------------------------------------------------------------
Leledumbo · 2010-03-25 12:07

Yes, I know there are many 3rd party programs to help. But it's for my
own program, where I want the schema is created automatically for
every new file.

----------------------------------------------------------------------
davesimplewear · 2010-03-25 21:44

Just a thought, if you use MySQLAdmin to set up a dummy db with tables
and foreign keys and export the sql, it will give you the construct
for the schema, I have used this method in SqlLite to get a construct
of a schema when I was unsure of the syntax.


Regards
Dave

----------------------------------------------------------------------
Leledumbo · 2010-03-26 05:20

It looks like I didn't state what I want to achieve. The purpose of
TDataset is to abstract the underlying dbms, so moving from one dbms
to another is relatively simple. If I specify the SQL directly,
there's a chance that the SQL isn't supported by some dbms. So, I hope
there's a construct such as:
Code: [Select]var
  ds1,ds2: TDatasetDescendant;
...
ds1.FieldDefs.Add('pkey',ftInteger);
...
ds2.FieldDefs.AddForeign('fkey',ftInteger,ds1.FieldByName('pkey'));
...
assuming ds2 and ds1 points to different table, fkey is a foreign key
defined in ds2 table to pkey defined in ds1 table.

----------------------------------------------------------------------
davesimplewear · 2010-03-26 08:09

Sorry, I don't know if it is possible, maybe someone else can help.

Regards
Dave