Need way to see all relations?
Is there a way with InnoDB tables to see all related tables/columns? Basically what I want to do is make a script that somehow will dynamically create a backup of a single user record. But I don't want to have to manually add a new table or column everytime to the script. So for example, I have a 'users' table. And there are all kinds of related tables like 'logs', 'preferences', etc. that all have a FK reference back to this user ID. I want to make a query or use some PHP to build a list of all tables in my DB that are references. Then I can just dump out that user ID (via this script) and it will backup that user and all related table data across all tables. ÐÆ5ÏÐ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need way to see all relations?
That is, as far as I know, impossible. Mysql does not know which tables are related to which ones. Peter On 6/30/06, Daevid Vincent [EMAIL PROTECTED] wrote: Is there a way with InnoDB tables to see all related tables/columns? Basically what I want to do is make a script that somehow will dynamically create a backup of a single user record. But I don't want to have to manually add a new table or column everytime to the script. So for example, I have a 'users' table. And there are all kinds of related tables like 'logs', 'preferences', etc. that all have a FK reference back to this user ID. I want to make a query or use some PHP to build a list of all tables in my DB that are references. Then I can just dump out that user ID (via this script) and it will backup that user and all related table data across all tables. ÐÆ5ÏÐ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need way to see all relations?
That doesn't seem right. I know that MYISAM tables don't understand relations, but INNODB tables most certainly understand foreign key constraints and in fact cause me a lot of grief sometimes when trying to insert a record or create a new table that violates said constraints ;-) DÆVID -Original Message- From: Peter Van Dijck [mailto:[EMAIL PROTECTED] Sent: Friday, June 30, 2006 6:32 PM To: Daevid Vincent Cc: mysql@lists.mysql.com Subject: Re: Need way to see all relations? That is, as far as I know, impossible. Mysql does not know which tables are related to which ones. Peter On 6/30/06, Daevid Vincent [EMAIL PROTECTED] wrote: Is there a way with InnoDB tables to see all related tables/columns? Basically what I want to do is make a script that somehow will dynamically create a backup of a single user record. But I don't want to have to manually add a new table or column everytime to the script. So for example, I have a 'users' table. And there are all kinds of related tables like 'logs', 'preferences', etc. that all have a FK reference back to this user ID. I want to make a query or use some PHP to build a list of all tables in my DB that are references. Then I can just dump out that user ID (via this script) and it will backup that user and all related table data across all tables. ÐÆ5ÏÐ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need way to see all relations?
Oh, InnoDB tables? I was assuming MyISAM tables.. :) And I'm not a total expert, but that's my understanding. Peter On 6/30/06, Daevid Vincent [EMAIL PROTECTED] wrote: That doesn't seem right. I know that MYISAM tables don't understand relations, but INNODB tables most certainly understand foreign key constraints and in fact cause me a lot of grief sometimes when trying to insert a record or create a new table that violates said constraints ;-) DÆVID -Original Message- From: Peter Van Dijck [mailto:[EMAIL PROTECTED] Sent: Friday, June 30, 2006 6:32 PM To: Daevid Vincent Cc: mysql@lists.mysql.com Subject: Re: Need way to see all relations? That is, as far as I know, impossible. Mysql does not know which tables are related to which ones. Peter On 6/30/06, Daevid Vincent [EMAIL PROTECTED] wrote: Is there a way with InnoDB tables to see all related tables/columns? Basically what I want to do is make a script that somehow will dynamically create a backup of a single user record. But I don't want to have to manually add a new table or column everytime to the script. So for example, I have a 'users' table. And there are all kinds of related tables like 'logs', 'preferences', etc. that all have a FK reference back to this user ID. I want to make a query or use some PHP to build a list of all tables in my DB that are references. Then I can just dump out that user ID (via this script) and it will backup that user and all related table data across all tables. ÐÆ5ÏÐ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need way to see all relations?
Daevid, I have a 'users' table. And there are all kinds of related tables like 'logs', 'preferences', etc. that all have a FK reference back to this user ID. I want to make a query or use some PHP to build a list of all tables in my DB that are references. Query to find tables with FK references to $db.$table: SELECT c.table_schema,u.table_name,u.column_name,u.referenced_column_name FROM information_schema.table_constraints AS c INNER JOIN information_schema.key_column_usage AS u USING( constraint_schema, constraint_name ) WHERE c.constraint_type = 'FOREIGN KEY' . AND u.referenced_table_schema='$db' AND u.referenced_table_name = '$table' ORDER BY c.table_schema,u.table_name; PB Daevid Vincent wrote: Is there a way with InnoDB tables to see all related tables/columns? Basically what I want to do is make a script that somehow will dynamically create a backup of a single user record. But I don't want to have to manually add a new table or column everytime to the script. So for example, I have a 'users' table. And there are all kinds of related tables like 'logs', 'preferences', etc. that all have a FK reference back to this user ID. I want to make a query or use some PHP to build a list of all tables in my DB that are references. Then I can just dump out that user ID (via this script) and it will backup that user and all related table data across all tables. ÐÆ5ÏÐ -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.8/380 - Release Date: 6/30/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need way to see all relations?
EXCELLENT! Thanks! -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Friday, June 30, 2006 8:31 PM To: Daevid Vincent Cc: mysql@lists.mysql.com Subject: Re: Need way to see all relations? Daevid, I have a 'users' table. And there are all kinds of related tables like 'logs', 'preferences', etc. that all have a FK reference back to this user ID. I want to make a query or use some PHP to build a list of all tables in my DB that are references. Query to find tables with FK references to $db.$table: SELECT c.table_schema,u.table_name,u.column_name,u.referenced_column_name FROM information_schema.table_constraints AS c INNER JOIN information_schema.key_column_usage AS u USING( constraint_schema, constraint_name ) WHERE c.constraint_type = 'FOREIGN KEY' . AND u.referenced_table_schema='$db' AND u.referenced_table_name = '$table' ORDER BY c.table_schema,u.table_name; PB Daevid Vincent wrote: Is there a way with InnoDB tables to see all related tables/columns? Basically what I want to do is make a script that somehow will dynamically create a backup of a single user record. But I don't want to have to manually add a new table or column everytime to the script. So for example, I have a 'users' table. And there are all kinds of related tables like 'logs', 'preferences', etc. that all have a FK reference back to this user ID. I want to make a query or use some PHP to build a list of all tables in my DB that are references. Then I can just dump out that user ID (via this script) and it will backup that user and all related table data across all tables. ÐÆ5ÏÐ -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.8/380 - Release Date: 6/30/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
