[ic] referential integrity/table constraints? - 4.8.6 and 4.9.5 for postgresql

[Doug Alcorn]

"Andrew C." <linuxdev@musicalheritage.org> writes:

> I was just wondering if there is a way to enforce referential
> integrity in interchange databases if I am using Postgresql with
> Interchange 4.8.6 and also 4.9.5?  Can foreign keys be used and if
> so how are they declared in the table configuration files in
> /dbconf/

Someone smart may need to confirm or deny this; however, I thought I
would answer just to get things rolling.  Another caveat is that I
don't have any _formal_ relational database training.  Just good
ol'fashioned OJT.

That having been said, I don't think think the database design of IC
really supports referential integrity.  Interchange was originally
designed to work with flat text files.  I don't know how much time
passed before the database backends were added; maybe no time lag or
maybe a long time.  However, Mike has said before he's not a big
database kind of guy.

So, I think using Postgres' referential integrity would be a
non-trivial task.  If you're smart on that kind of stuff I would guess
it could be done.  The database scheme files in /dbconf/* aren't going
to support it though.  I don't think there is any syntax for them that
would help with that.  What you'd need to do is allow IC to create the
databases the first time the catalog is loaded then go into Postgres
and add the RI "behind Interchange's back".
-- 
 (__) Doug Alcorn - Unix/Linux/Web Developing
 oo / PGP 02B3 1E26 BCF2 9AAF 93F1  61D7 450C B264 3E63 D543
 |_/  mailto:doug@lathi.net http://www.lathi.net
      mailto:tarpit@lathi.net is a spam trap