Re: constraints and sql92 information_schema compliance

On Tue, Mar 14, 2006 at 10:01:16PM -0800, Stephan Szabo wrote:
| The point is that because rows in a table don't have order (unless
| information_schema has special rules) the two constraints above seem to
| look the same to me in their representation in
| information_schema.constraint_column_usage.  If that's true then forcing
| the referenced columns to match exactly doesn't actually fix the problem
| with the representation in infomration schema.  The same ambiguity exists.

Actually, there is no ambiguity; it's just that constraint_column_usage
view is absolutely useless.  You want to be using key_column_usage.

--
-- Create the test tables, taking particular care to name the
-- constraints so that they are unique within the schema.
--
create table ta(a int, b int);
alter table ta add constraint ta_pk primary key (a,b);
create table tb(a int, b int);
alter table tb add constraint tb_ta_fk foreign key (a,b) references ta;
create table tc(a int, b int);
alter table tc add constraint tc_ta_fk foreign key (b,a) references ta;

--
-- Return the pairing between the foreign-key column, and
-- the canidate-key columns they refer to.
--
SELECT fk.table_name AS fk_table, fk.column_name AS fk_column,      uk.table_name AS uk_table, uk.column_name AS uk_column FROM ( SELECT c.constraint_schema, c.constraint_name,          c.table_schema, c.table_name,          u.column_name, u.ordinal_position     FROM information_schema.table_constraints c     JOIN information_schema.key_column_usage u ON (               u.constraint_schema = c.constraint_schema           AND u.constraint_name = c.constraint_name           AND u.table_schema = c.table_schema           AND u.table_name = c.table_name)    WHERE c.constraint_type in ('UNIQUE', 'PRIMARY KEY') ) AS uk, ( SELECT c.unique_constraint_schema, c.unique_constraint_name,          u.table_schema, u.table_name,          c.constraint_schema, c.constraint_name,          u.column_name, u.ordinal_position     FROM information_schema.referential_constraints c     JOIN information_schema.key_column_usage u ON (               c.constraint_schema = u.constraint_schema           AND c.constraint_name = u.constraint_name ) ) AS fk WHERE uk.constraint_schema = fk.unique_constraint_schema   AND uk.constraint_name = fk.unique_constraint_name   AND uk.ordinal_position = fk.ordinal_position ORDER BY fk.table_name, fk.ordinal_position;

I hope this helps! (and that it's even remotely correct)

Best,

Clark