Re: ?? Getting info on foreign keys from sys*

Please note this addition to my reply to Mr. Muller yesterday. This is the correct patch to myschema.ec to the singleton select statement near line 2407 or so. Thanks Jonathon. You always keep me honest. I just never use foreign keys that refer to anything but primary keys. I have fixed it for good this time the correct final where clause phrase s/b: AND sc.constrid = :primconstr; Where primconstr was selected from sysreferences.primary where constrid = (the foreign key constraint id) rather than the check on constrtype I had added yesterday. Thanks again. ---- Original Msg from: Jonathan Leffler <jleffler@informix.com> At: 7/10 13:2 On Thu, 9 Jul 1998, Art S. Kagel wrote: > John Mullee wrote: > > > > How can I retieve, using something like the following query, > > a list of tables which reference a table 'X' via foreign keys? > [SNIP attempt to SELECT this info] > > Check the source code to the print_constraints function in my > myschema.ec program for how to do this. Myschema is part of the > utils2_ak package I submitted to the IIUG Software Archives. > > The quick and dirty is that for the columns in the referencing table > (the one containing the foreign key constraint) you go to the > sysindexes record for the index named in the sysconstraints record > corresponding to the foreign key. For the columns in the referenced > table (the one containing the corresponding primary key) you go to the > sysindexes record named in the sysconstraints record corresponding to > the primary key of the referenced table. It's a bit more involved than > that but feel free to lift the code from myschema.ec. Yes, especially since you can reference an alternative key -- a unique index which isn't the primary key. Take a relatively compelling example: CREATE TABLE Elements ( AtomicNumber INTEGER NOT NULL CHECK (AtomicNumber > 0 AND AtomicNumber < 110) PRIMARY KEY CONSTRAINT PK_Elements, Symbol CHAR(2) NOT NULL CHECK (Symbol MATCHES "[A-Z] " OR Symbol MATCHES "[A-Z][a-z]") UNIQUE, Element CHAR(20) NOT NULL UNIQUE ); INSERT INTO Elements VALUES(1, "H", "Hydrogen"); INSERT INTO Elements VALUES(6, "C", "Carbon"); INSERT INTO Elements VALUES(8, "O", "Oxygen"); CREATE TABLE Isotopes ( AtomicNumber INTEGER NOT NULL REFERENCES Elements, AtomicWeight INTEGER NOT NULL CHECK (AtomicWeight > 0) ); INSERT INTO Isotopes VALUES(1, 1); INSERT INTO Isotopes VALUES(1, 2); -- Deuterium (aka D) INSERT INTO Isotopes VALUES(1, 3); -- Tritium (aka T or Tr, I think) INSERT INTO Isotopes VALUES(6, 12); INSERT INTO Isotopes VALUES(6, 14); -- Carbon-14 dating... CREATE TABLE Chemicals ( ID SERIAL NOT NULL PRIMARY KEY, Name VARCHAR(60) UNIQUE ); INSERT INTO Chemicals VALUES(1, "Carbon Dioxide"); CREATE TABLE ChemicalFormulae ( ID INTEGER NOT NULL REFERENCES Chemicals, Element CHAR(2) NOT NULL REFERENCES Elements(Symbol), Number INTEGER NOT NULL CHECK(Number > 0) ); INSERT INTO ChemicalFormulae VALUES(1, "C", 1); INSERT INTO ChemicalFormulae VALUES(1, "O", 2); It makes sense for the isotope table to deal with the AtomicNumber column, though it could also use the Symbol column. It makes sense for the chemical tables to deal with Symbol column. Thus, there are two separate keys that can be referenced in a single table. For the referenced table, you need to find the index named in the sysconstraints record identified by the constraint ID specified by primary in the sysreferences record. > NOTICE! I spotted a bug in that code: The SELECT...INTO immediately > preceeding the 'EXEC SQL OPEN cols USING :ptabid;' statement needs an > additional where clause. Add: "AND sc.constrtype = 'P'" to that SELECT > to avoid getting the wrong column list if the referenced table itself > references another (grandparent) table. I'll fix the code and submit > an update to the Repository today but it will not show up for a week or > so. Anyone using myschema.ec note the fix goes at around line 2407. I'm not, therefore, convinced that this fix is a good idea... Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix -- see http://www.perl.com/CPAN