Re: Insert in DB2 many-to-many table

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are. Sample data is also a good ideas, along with clear specifications.

1. Stop putting those silly, redundant "t_" prefixes to tell us that a table is a table. I am sorry that business is so bad that you have only one account instead of a talbe to model Accounts (note the use of plurals or collective nouns for sets). You need to read the ISO-11179 standards for data elements name

>> I have an t_account table with acc_id (identity type) as PK <<

An IDENTITY property can, by definition, never be a key. And it means that you have no checkdigits, no verification in the real world that your model is correct.

>> I have another table t_customer with cust_id (identity type) as PK.
<<

Ditto.

>> t_account and t_customer share a many-to-many relationship. So I
have a third table t_acc_cust to reflect the many to many relationship. <<

No DDL, so I can only hope that you have something like this:

CREATE TABLE CustAccts
(acct_nbr INTEGER NOT NULL

          REFERENCES Accounts(acct_nbr)
          ON UPDATE CASCADE
          ON DELETE CASCADE,
 cust_nbr INTEGER NOT NULL
          REFERENCES Customers(cust_nbr)
          ON UPDATE CASCADE
          ON DELETE CASCADE,

But I have the feeling that anyone who would use an IDENTITY for a key would not bother with DRI and might even have put an IDENTITY on this table as a pseudo-key.

>> How can I write the SQL statements so the the t_account, t_customer
tables get inserted with new values and also the t_acc_cust table gets populated with acc_id and cust_id. <<

You need three INSERT INTO statements. The account and the customer must exist in their tables before you can have a relationship between them. This will be easy once you have a proper design. Received on Sun Sep 14 2003 - 01:24:19 CEST