Thread: Foreign Key Constraints
07-07-06, 13:47 #1Registered User
- Join Date
- Mar 2005
Unanswered: Foreign Key Constraints
UDB : 8.1 FP7
How do i get all the foreign key state ments to and for a table?
Here is what i am trying to do . I am dropping a table and recreating by modifying the order of the columns . But i see a lot of foreign key statemtns
fromother table to this table. Do they get effected when i drop the tables.
How do i take care of it.
07-07-06, 17:33 #2Registered User
- Join Date
- Jul 2005
- Irvine, CA
Yes, if you drop a table the associated RI drops too. You have to preserve the RI in DDL to be run either before or after you re-load the data in the new table (your choice).
An easy and cheap way to extract RI DDL is to use the IBM DB2 Command Center and right click on the table in question - you'll see "generate DDL" - do this and you will get the RI DDL you need. You may need to get the ddl from the other associated tables. To get this info - right click on the table - then click "show related" and look at the table tab.
You can use the db2look command too. Do some research on that one. You can also buy a tool like Quest Central for thousands of dollars and it do 99.9% of the work for you - including generating the entire export, drop table, build, import, and runstats, etc.
The manual way is to find all associated RI to the table and write your own DDL.
Hope this helps,
Certified DB2 LUW DBA
07-07-06, 18:50 #3Registered User
- Join Date
- Sep 2004
Of course you can also find out all dependencies (RI), indexes, views, check constraints, authorizations etc. on your table by using plain SQL instead of using the Control Center.
E.g., to find all indexes defined on your table:Code:
SELECT 'CREATE ' || CASE uniquerule WHEN 'D' THEN '' ELSE 'UNIQUE' END || 'INDEX ' || name || ' ON ' || tbcreator || '.' || tbname FROM sysibm.sysindexes WHERE tbname = 'YOURTABLE' AND tbcreator = 'TABLEOWNER'
Finding out about referential constraints in other tables is similar: query SYSIBM.SYSRELS (one line per foreign key) and SYSIBM.SYSKEYCOLUSE (one line per column of each FK). Don't forget to add the appropriate REFERENCES authorizations back (see SYSIBM.SYSTABAUTH) ...
Also make sure to place the table in the same tablespace as the old one.
Last edited by Peter.Vanroose; 07-07-06 at 19:03.--_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting