List foreign keys
Lee
The documentation for 9.1 is no longer on line, and my client
apparently has none, either.
Thanks in anticipation
lee
Paul Mason
including the foreign key ones.
HTH
Paul
> _______________________________________________
> Info-Ingres mailing list
> Info-...@kettleriverconsulting.com
>
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres
Lee
Perl DBI. So 'HELP' is a syntax error.
On Jul 29, 12:44 pm, "Paul Mason" <Paul.Ma...@ingres.com> wrote:
> HELP CONSTRAINT <table_name> will list the constraints on a table,
> including the foreign key ones.
>
> HTH
> Paul
>
>
>
> > -----Original Message-----
> > From: info-ingres-boun...@kettleriverconsulting.com [mailto:info-
> > ingres-boun...@kettleriverconsulting.com] On Behalf Of Lee
> > Sent: 29 July 2011 12:01
> > To: info-ing...@kettleriverconsulting.com
> > Subject: [Info-Ingres] List foreign keys
>
> > 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
> > _______________________________________________
> > Info-Ingres mailing list
> > Info-Ing...@kettleriverconsulting.com
>
> http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres
Roy Hann
> How can I get a list of a table's foreign keys, in Ingres 9.1?
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
Roy Hann
> 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;
Correction, make that last line:
...order by schema_name, table_name, constraint_name, text_sequence;
Roy Hann
> Lee wrote:
>
>> How can I get a list of a table's foreign keys, in Ingres 9.1?
>
> Rather annoyingly you can't get that information in a convenient form.
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.
Ingres Forums
Hi Lee,
You can use sc930 to get the SQL generated by HELP CONSTRAINT
<tablename>.
Jeremy
--
jruffer
------------------------------------------------------------------------
jruffer's Profile: http://community.ingres.com/forum/member.php?userid=504
View this thread: http://community.ingres.com/forum/showthread.php?t=13540
Ingres Forums
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
------------------------------------------------------------------------
dejan's Profile: http://community.ingres.com/forum/member.php?userid=13077
Armand Pirvu (gmail)
Hope this helps
A
Roy Hann
>
> Then you'll have to use system catalogs. Start with these: iiintegrity,
> iikey, iiintegrityidx .
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.
Lee
Roy - this is great news, thanks! I had almost given-up hope.
I shall give it a try on Monday.
Cheers
lee
Joner Cyrre Worm
> Roy Hann wrote:
> > Lee wrote:
>
> >> How can I get a list of a table's foreign keys, in Ingres 9.1?
>
> > Rather annoyingly you can't get that information in a convenient form.
>
> 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; ...
Would you mind to elaborate a bit on this, Roy, please...
> ...you just have to divine their existence or ask a
Roy Hann
>> There may well be any number of other foreign keys that are not
>> identified as such; ...
>
> Would you mind to elaborate a bit on this, Roy, please...
A foreign key in the conceptual model exists whether it has been
declared as an SQL constraint or not. Programmers and users familiar
with a particular conceptual model (and therefore with its common keys)
will write joins just as well whether the foreign keys are declared or
not.
The query I suggested to identify foreign keys will report only the
foreign keys that have actually been declared. It can't report the
foreign keys that exist but haven't been declared.
As I said, if they're not declared you just have to "know" about them,
by divination or by asking someone else who knows. (Obviously word of
mouth is a very poor way to run a system....)
Paul White
I believe Roy is referring to foreign keys maintained by application code or
some other assumed business logic.
Hopefully your schema has naming standards or internal structure help you
locate foreign keys by means other than a bit of willow.
Do have a copy of the application code? You can extract SQL statements from
the code to identify where tables are used in the same statement. If the
code is of good standard, it is possible to semi automate the task giving
you a sets of relationships (table1, table2... Table1.field1,
table2.field2...). You can also determine if tables are very likely related
because they are in the same code block/module. For example you can derive a
list of likely tables "belonging" to a module eg (Accounts Payable:
Supplier, Invoice, Payments...). This can be automated too. (if it is
OpenROAD or ABF I can help).
Recently I completed a data conversion from an ERP called Greentree -
written an object oriented DB called Jade. I didn�t have the application
code, just a partial data extract into a MSSQL database. Each object class
(table) was identified by a unique 5 digit number and each instance (record)
identified by a 16 digit number. By looking at the value of an attribute, I
could easily identify the relationships between the object classes. More
often, the name of the attribute directly identified the relationship. The
scheme also allowed for optional referential integrity and overloading
references. eg:
Object classes
notes 10000
customer 20000
invoice 30000
status 50000
Notes
id 10000.1234567890123456
myParent 20000.6543210987654321
theText This is a customer note
myStatus 50000.1111111111111111
id 10000.1234567890123457
myParent 30000.5432109876543210
theText This is a related to an invoice
myStatus 50000.1111111111111112
id 10000.1234567890123458
myparent NULL
theText This is just a note
myStatus 50000.1111111111111113
Customer
id 20000.6543210987654321
Name White
myAddress 40000.8888888888888888
myStatus 50000.1111111111111118
Invoice
id 30000.5432109876543210
amount 123.45
myStatus 50000.1111111111111121
Consistent naming standards in Greentree helped make reverse engineering
task so much easier. I found the most difficult part of the extraction
dealing with a the partial data set. Once I found a missing reference value
I could go back to the source and start looking for it. (like the addresses
in the example above) It was sometimes a bit tricky dealing with overloaded
references where I used multiple SQLs or rather complicated CASE
expressions. But on the whole it was a beautiful relational transformation.
Paul
_______________________________________________
DougI
FWIW, VectorWise introduces a new catalog that records all referential
relationships, 1 per row. The row contains the referenced table ID,
the referencing table ID, the constraint ID and the mapping of foreign
key columns to corresponding primary/unique columns. I expect it'll
soon be available for native Ingres as well (10.0 or later). It is
true that getting the info prior to this is a total PITA.
Doug.
Ingres Forums
This looks like something to be solved in the next sprint, does it, Roy?
:)
Roy Hann
> On Jul 29, 7:00�am, Lee <lee...@gmail.com> wrote:
> FWIW, VectorWise introduces a new catalog that records all referential
> relationships, 1 per row. The row contains the referenced table ID,
> the referencing table ID, the constraint ID and the mapping of foreign
> key columns to corresponding primary/unique columns. I expect it'll
> soon be available for native Ingres as well (10.0 or later).
Ooh, I look forward to that.
> It is
> true that getting the info prior to this is a total PITA.
--
Roy
UK Ingres User Association Conference 2012 will be on Tuesday June 19 2012.
*NOTE THE CHANGED DATE* See www.uk-iua.org.uk
Lee
> Roy Hann wrote:
> > The best you can do AFAIK is:
>
> > select schema_name, table_name, text_segment
> > from iiconstraints
> > where constraint_type = 'R'
> order by schema_name, table_name, constraint_name, text_sequence;
Thanks, Roy - I'll give a try.
Cheers
Lee