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