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/.
Posted by at

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

22:10
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 ;-).

22:58
rpbouman 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?

14:50
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 ;-).

15:36

Post a Comment

Subscribe to: Post Comments (Atom)