Hi all !!

I have this question:

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



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



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.



t_acc_cust(acc_id,cust_id).



In my database iserts, I have an account and a number of customers. 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.



Regards and thanks


--
Posted via http://dbforums.com

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

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.


Ditto.

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,
PRIMARY KEY(acct_nbr, cust_nbr));

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.

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.