Skip to content

Instantly share code, notes, and snippets.

@avstudnitz
Last active February 10, 2020 09:08
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save avstudnitz/ed98bb721d26695d5067 to your computer and use it in GitHub Desktop.
Save avstudnitz/ed98bb721d26695d5067 to your computer and use it in GitHub Desktop.
Magento: Create SQL queries to recreate all foreign keys which exist in database magento_source but not in database magento_dest
SELECT
CONCAT('ALTER TABLE ', u1.table_name, ' ADD CONSTRAINT ', u1.CONSTRAINT_NAME, ' FOREIGN KEY (', u1.column_name, ') REFERENCES ', u1.referenced_table_name, ' (', u1.referenced_column_name, ') ON DELETE CASCADE ON UPDATE CASCADE;') as 'sql_query'
FROM information_schema.key_column_usage u1
LEFT JOIN information_schema.key_column_usage u2
ON
u1.table_name = u2.table_name
AND u1.column_name = u2.column_name
AND u2.table_schema = 'magento_dest'
AND u2.referenced_table_name IS NOT NULL
WHERE
u1.referenced_table_name IS NOT NULL
AND u1.table_schema = 'magento_source'
AND u2.table_name IS NULL;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment