Sql updating tables with foreign keys

Rated 3.83/5 based on 534 customer reviews

I've too much tables to execute the foreign key dependency checking script by hand. /bin/sh# check_constraints.sh# --------------------# Check foreign key contraints on My SQL database.# # Written by Frank Vanderhallen, licensed under GPL.if [ -z "

I've too much tables to execute the foreign key dependency checking script by hand. /bin/sh# check_constraints.sh# --------------------# Check foreign key contraints on My SQL database.# # Written by Frank Vanderhallen, licensed under GPL.if [ -z "$1" ]then echo "\n Usage:\n\t./`uname $0` ]\n" exitfi CONSTRAINTS=`mysqldump $* | grep "CREATE\|CONSTRAINT" | sed 's/ / /g'`for c in $CONSTRAINTSdo if [ "`echo $c | cut -d ' ' -f 3`" = "CONSTRAINT" ] then CONSTRAINT=`echo $c | cut -d ' ' -f 4 | tr -d '\`'` CHILD_KEY=`echo $c | cut -d ' ' -f 7 | tr -d '()\`,'` PARENT_TABLE=`echo $c | cut -d ' ' -f 9 | tr -d '\`'` PARENT_KEY=`echo $c | cut -d ' ' -f 10 | tr -d '()\`,'` QUERY="select c.$CHILD_KEY from $CHILD_TABLE as c left join $PARENT_TABLE as p on p.$PARENT_KEY=c.$CHILD_KEY where c.$CHILD_KEY is not null and p.$PARENT_KEY is null;" echo "Checking table '$CHILD_TABLE' constraint '$CONSTRAINT'" mysql -verbose $* -e "$QUERY" else CHILD_TABLE=`echo $c | cut -d ' ' -f 3` fidone The fact that "NO ACTION" and "RESTRICT" should be treated equally means that there is no way to delete a parent row without deleting the child row unless you disable the foreign key check.The author of the document is only dependant on the doc ID, not the language of a particular translation. Although this makes sense, the restraint acts a little funny.Say you have the following data:doc tabledoc ID lang Code title1 hu A Szamitogep1 en The Computerauthor tableauthor ID doc ID name7 1 Kaposzta Csaba Deleteing any version of the document will delete the entry in the author table.You find the rows like this:select from child left join parent on (child.parent_id=parent.id) where is not null and is null; There may be rare cases where circular dependencies would make sense.

||

I've too much tables to execute the foreign key dependency checking script by hand. /bin/sh# check_constraints.sh# --------------------# Check foreign key contraints on My SQL database.# # Written by Frank Vanderhallen, licensed under GPL.

" ]then echo "\n Usage:\n\t./`uname

In this case, you have Store ID as an attribute of Employee, and Employee ID (the Manager) as an attribute of Store. There are likely better ways to handle such cases, but if your business rules requires such circular dependencies, then it happens.

Here is the modified query for SQL Server 2005 and later. NAME), FKTABLE_QUALIFIER = CONVERT(SYSNAME, DB_NAME()), FKTABLE_OWNER = CONVERT(SYSNAME, SCHEMA_NAME(O2. OBJECT_ID,'Cnst Is Update Cascade') WHEN 1 THEN 0 ELSE 1 END), DELETE_RULE = CONVERT(SMALLINT, CASE OBJECTPROPERTY(F.

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.

In order to get this to work for SQL Server 2000 an adjustment was made to the sp_fkeys SP to cursor through all of the user tables.

CONSTRAINT_TYPE = 'FOREIGN KEY' With SQL Server 2000 it is not as straight forward as using a simple SQL statement as shown above.

` ]\n" exitfi CONSTRAINTS=`mysqldump $* | grep "CREATE\|CONSTRAINT" | sed 's/ / /g'`for c in $CONSTRAINTSdo if [ "`echo $c | cut -d ' ' -f 3`" = "CONSTRAINT" ] then CONSTRAINT=`echo $c | cut -d ' ' -f 4 | tr -d '\`'` CHILD_KEY=`echo $c | cut -d ' ' -f 7 | tr -d '()\`,'` PARENT_TABLE=`echo $c | cut -d ' ' -f 9 | tr -d '\`'` PARENT_KEY=`echo $c | cut -d ' ' -f 10 | tr -d '()\`,'` QUERY="select c.$CHILD_KEY from $CHILD_TABLE as c left join $PARENT_TABLE as p on p.$PARENT_KEY=c.$CHILD_KEY where c.$CHILD_KEY is not null and p.$PARENT_KEY is null;" echo "Checking table '$CHILD_TABLE' constraint '$CONSTRAINT'" mysql -verbose $* -e "$QUERY" else CHILD_TABLE=`echo $c | cut -d ' ' -f 3` fidone The fact that "NO ACTION" and "RESTRICT" should be treated equally means that there is no way to delete a parent row without deleting the child row unless you disable the foreign key check.The author of the document is only dependant on the doc ID, not the language of a particular translation. Although this makes sense, the restraint acts a little funny.Say you have the following data:doc tabledoc ID lang Code title1 hu A Szamitogep1 en The Computerauthor tableauthor ID doc ID name7 1 Kaposzta Csaba Deleteing any version of the document will delete the entry in the author table.You find the rows like this:select from child left join parent on (child.parent_id=parent.id) where is not null and is null; There may be rare cases where circular dependencies would make sense.

[[

In this case, you have Store ID as an attribute of Employee, and Employee ID (the Manager) as an attribute of Store. There are likely better ways to handle such cases, but if your business rules requires such circular dependencies, then it happens.

Here is the modified query for SQL Server 2005 and later. NAME), FKTABLE_QUALIFIER = CONVERT(SYSNAME, DB_NAME()), FKTABLE_OWNER = CONVERT(SYSNAME, SCHEMA_NAME(O2. OBJECT_ID,'Cnst Is Update Cascade') WHEN 1 THEN 0 ELSE 1 END), DELETE_RULE = CONVERT(SMALLINT, CASE OBJECTPROPERTY(F.

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.

In order to get this to work for SQL Server 2000 an adjustment was made to the sp_fkeys SP to cursor through all of the user tables.

CONSTRAINT_TYPE = 'FOREIGN KEY' With SQL Server 2000 it is not as straight forward as using a simple SQL statement as shown above.

||

In this case, you have Store ID as an attribute of Employee, and Employee ID (the Manager) as an attribute of Store. There are likely better ways to handle such cases, but if your business rules requires such circular dependencies, then it happens.Here is the modified query for SQL Server 2005 and later. NAME), FKTABLE_QUALIFIER = CONVERT(SYSNAME, DB_NAME()), FKTABLE_OWNER = CONVERT(SYSNAME, SCHEMA_NAME(O2. OBJECT_ID,'Cnst Is Update Cascade') WHEN 1 THEN 0 ELSE 1 END), DELETE_RULE = CONVERT(SMALLINT, CASE OBJECTPROPERTY(F. 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. In order to get this to work for SQL Server 2000 an adjustment was made to the sp_fkeys SP to cursor through all of the user tables. CONSTRAINT_TYPE = 'FOREIGN KEY' With SQL Server 2000 it is not as straight forward as using a simple SQL statement as shown above.

]]

Leave a Reply