How can I get a list of a table's foreign keys, in Ingres 9.1?

The documentation for 9.1 is no longer on line, and my client
apparently has none, either.

Thanks in anticipation
lee

HELP CONSTRAINT <table_name> will list the constraints on a table,
including the foreign key ones.

HTH
Paul

Thanks, Paul, but I have to use SQL to perform this task -- via the
Perl DBI. So 'HELP' is a syntax error.

On Jul 29, 12:44*pm, "Paul Mason" <Paul.Ma... (AT) ingres (DOT) com> wrote:

Lee wrote:

Rather annoyingly you can't get that information in a convenient form.

The best you can do AFAIK is:

select schema_name, table_name, text_segment
from iiconstraints
where constraint_type = 'R'
order by constraint_name, schema_name, table_name, text_sequence;

You then have to concatenate and parse the text_segments for each
constraint.

It sucks, desperately. :-(

--
Roy

UK Ingres User Association Conference 2012 will be on Tuesday June 19 2012.
*NOTE THE CHANGED DATE* See www.uk-iua.org.uk

Hi Lee,

You can use sc930 to get the SQL generated by HELP CONSTRAINT
<tablename>.

Jeremy


--
jruffer

Then you'll have to use system catalogs. Start with these: iiintegrity,
iikey, iiintegrityidx .
Maybe you'll need more, depends what you want to achieve...


--
dejan

Roy Hann wrote:

Correction, make that last line:

...order by schema_name, table_name, constraint_name, text_sequence;

--
Roy

UK Ingres User Association Conference 2012 will be on Tuesday June 19 2012.
*NOTE THE CHANGED DATE* See www.uk-iua.org.uk

Roy Hann wrote:

In my eagerness to moan about how inconveniently encoded that
information is, I forgot to point out the (possibly obvious) limitation
that you will still only see the foreign keys that have been declared.
There may well be any number of other foreign keys that are not
identified as such; you just have to divine their existence or ask a
savant.

--
Roy

UK Ingres User Association Conference 2012 will be on Tuesday June 19 2012.
*NOTE THE CHANGED DATE* See www.uk-iua.org.uk

I would look then at iiconstraints and iiconstraint_indexes catalogs

Hope this helps
A
On Jul 29, 2011, at 8:06 AM, Lee wrote:

Ingres Forums wrote:

iiintegrity is used for constraints created using the deprecated CREATE
INTEGRITY statement (a refugee from QUEL).

However you've mentioned iikeys and motivated me to do some more
digging, and I wonder if this might suit the OP's requirements:

select k.schema_name, k.table_name, k.column_name
from iikeys k join iiconstraints c
on k.constraint_name = c.constraint_name
and k.schema_name = c.schema_name
and k.table_name = c.table_name
where constraint_type = 'R'
and k.table_name = :table_name
order by k.key_position;

In which case I withdraw my earlier grumbles about how it sucks to have
to parse the text_segment. I do apologise.

--
Roy

UK Ingres User Association Conference 2012 will be on Tuesday June 19 2012.
*NOTE THE CHANGED DATE* See www.uk-iua.org.uk