用SQL產生具有Referential Integrity的測試資料

題目：

假設有兩個表格parent_table及child_table，其結構如下

parent_table:

parent_id	child_count
101	2
102	1
103	3

child_table:

parent_id	child_id
101	11
101	12
102	13
103	14
103	15
103	16

其中，parent_id為parent_table的主鍵, child_id為child_table的主鍵 而child_count的數目表示該parent_id可以map到多少個child_id。試圖自動產生child_table的內容，使其能夠符合parent_table的child_count數目。也就是說，產生出來的child_table需要出現2次parent_id:101，1次parent_id:102以及3次parent_id:103，如同上圖所示child_table的內容。

解答：

1. 遇到這類的情況，必需把parent_table中的rows展開，產生一個中介的表格。方法為把parent_table與一個sequence table做cartesian join。所需的SQL及產生的結果如下
   select parent_table.parent_id,parent_table.child_count, sequence_tab.id
   from parent_table join (select row_number() over()  as id from syscat.tables) as sequence_tab on sequence_tab.id between 1 and parent_table.child_count
   
2. 接著，Select 上述結果的parent_id，進行primay key編號，即為chile_table的內容。所需的SQL如下
   select temp_tab.parent_id, row_number() over()+10 as child_id from (select parent_table.parent_id,parent_table.child_count, sequence_tab.id
   from parent_table join (select row_number() over()  as id from syscat.tables) as sequence_tab on sequence_tab.id between 1 and parent_table.child_count) as temp_tab
   
3. 最後將上述SQL放在INSET INTO Child_table 之後，即告完成
   INSERT INTO CHILD_TABLE
   select temp_tab.parent_id, row_number() over()+10 as child_id from
   (select parent_table.parent_id,parent_table.child_count, sequence_tab.id
   from parent_table join (select row_number() over()  as id from syscat.tables) as sequence_tab on sequence_tab.id between 1 and parent_table.child_count) as temp_tab
   

參考來源：http://www.ibm.com/developerworks/data/library/techarticle/dm-0405kuznetsov/index.html