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_columnFROM ( 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_usageu 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_usageu 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
