Teiid imports the PK/Fk relationships during the import process in the Designer, however they are not used during the query optimizer currently. I believe this data is also in the metadata files.

Ramesh..

> Are they used for cascading deletes, helping the optomizer etc...?

Cascading deletes, no not currently.  To be handled automatically that would typically only be applicable in inherently updatable views that are using the default updating handling.  In which case our notion of pk/fk relationships on views is more informational rather than an actual integretity constraint.  There is room for an enhancement there, but it is also striaghtfoward for the user to handle the cascading manually in an update procedure (or with a containing view that acts like a before trigger).

Yes the optimizer uses pk/fk relationships.  pks are of course used during costing.  Relationships are used to determine if a table is key preserved through a join.  The notion of key preservation is used in subquery optimization and with inherently updatable views.

> When creating a new view which includes two tables which do have FK/PK relationship defined (as links), the view doesn't automatically add the JOIN clause, I thought it might?

There has been a feature request along these lines for years.  There is also a concept of a natural join that joins based upon same named columns, which simplifies some of responsibility of writing a join.