Mysql automatically updating foreign keys
COLUMN_NAME; SELECT PKTABLE_QUALIFIER = CONVERT(SYSNAME, DB_NAME()), PKTABLE_OWNER = CONVERT(SYSNAME, SCHEMA_NAME(O1. OBJECT_ID,’Cnst Is Delete Cascade’) WHEN 1 THEN 0 ELSE 1 END), FK_NAME = CONVERT(SYSNAME, OBJECT_NAME(F. NAME), DEFERRABILITY = CONVERT(SMALLINT,7) — SQL_NOT_DEFERRABLE FROM SYS.
My knowledge of the database that is on a piece of code I am not able to find all Relationships database.
Hi I have a My SQL database that consists of 40 tables and is located on.
And thank you for the initial share, which gave me the start way to find my solution. Thanks for all your help over the years by the way. To make it relational you should not use the ordinal position of the column, you should use the column name or alias.
KEY_COLUMN_USAGE ccu ON rc.unique_constraint_catalog = ccu.constraint_catalog AND rc.unique_constraint_schema = ccu.constraint_schema AND rc.unique_constraint_name = ccu.constraint_name AND kcu.ordinal_position = ccu.ordinal_position WHERE tc.constraint_type = ‘FOREIGN KEY’ AND tc.table_name = ‘some_table_name’ AND tc.constraint_name = ‘some_fk_name’ AND tc.constraint_catalog = ‘some_catalog_name’Of course, configure the WHERE with your own conditions.
Question: I have more tables in my database than the query resulted.
There is a FK I specifically wanted to find the relations to but the table in which it is a FK didn’t even show up in results.
Hi Pinal, I use this to get most of the needed metadata if I don’t have access to the Diagramselect ic.
DELETE_RULE from information_schema.columns ic left join information_schema.
KEY_COLUMN_USAGE kcu ON tc.constraint_catalog = kcu.constraint_catalog a ND tc.constraint_schema = kcu.constraint_schema AND tc.constraint_name = kcu.constraint_name INNER JOIN INFORMATION_SCHEMA. REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.