Monday, February 06, 2006

Foreign key dependencies

This blog post has moved. Please find it at:

http://www.mpopp.net/2006/02/foreign-key-dependencies/.

4 comments:

Beat Vontobel said...

Hi Markus,

thanks for your nice example. It inspired me to write another procedure delDeps to automatically remove/delete foreign key dependencies/constraints. To achieve this, due to current MySQL restrictions (as of 5.0.18), you need to do it without prepared statements (which are actually superfluous in your example).

Regards,
Beat

Markus Popp said...

Yep, you're right - this would work without prepared statements as well. They would only be necessary if the parameters should be inserted e.g. for the table name (information_schema.KEY_COLUMN_USAGE in this example) or on other places where parameters can't be used directly (like the LIMIT clause - I filed a feature request to remove this limitation a while ago).

Anyway, it's great that my example inspired you to extend its functionality to create another useful example of how stored procedures and information_schema play together nicely ;-).

Roland Bouman said...

Hey Markus,

no it's supposed NOT to be a bug (..yeah right..) But feel free to join my campaign:

http://bugs.mysql.com/bug.php?id=14642

BTW Can't you do the query without dynamic sql? It makes it simpler in my opinion. Somthing like this:

create procedure bogus(
p_schema varchar(64)
, p_table varchar(64)
)
select concat(
table_schema
, '.'
, table_name
, ' ('
, constraint_name
, ') '
, group_concat(
column_name
, ' -> '
, referenced_column_name
order by ordinal_position
)
) as dependencies
from information_schema.key_column_usage kcu
where kcu.referenced_table_schema = p_schema
and kcu.referenced_table_name = p_table
group by constraint_schema
, constraint_name
, table_schema
, table_name
;

Or am i missing something?

Markus Popp said...

I tried to change it, but then it worked once but crashed the server when I ran the procedure a second time. Seems like there's still a bug.

Your feature request is a good thing - I already added a comment that I'd like this feature, too ;-).