[KCross]

Greetings!

I'm trying to do something that I'm fairly certain is impossible - perhaps by design.

I'll try to explain my issue.

Table 1 (tblType): type_id (PK), type, manufacturer_id (FK)

Table 2 (tblManufacturer): manufacturer_id (PK), manufacturer, type_id (FK)

As you can see, I have tables that cross-reference each other in foreign keys. I realize this is probably bad coding practice, but I wasn't sure how else to solve a previous problem related to bidirectionally populating drop down lists (by selecting a value from ddlType, it populates ddlManufacturer, and by making a selection from ddlManufacturer, it will autopopulate the contents of ddlType). The reason they are bi-directional is because we will have a Manufacturer who makes multiple products, and we have the same products by multiple manufacturers.

Now, as you can probably guess, when I try to INSERT into either tblType or tblManufacturer, I must use an already existing *_id from the alternate table. This is not good.

A solution would be to insert data into both tables simultaneously. But, again, I don't even think that's possible.

Is there another way for me to go about this? If I remove the FK constraints, I'm not certain how to get those dropdown lists to populate. But if I leave the FK constraints there, I can't seem to add new values. I don't know much about SQL - I'm just starting. Could a trigger be used in a situation like this?

Thank you!
kx

[AdaHacker]

This is easily done without any need for simultaneous inserts. You simply need to use the fact that standard SQL allows foreign key columns to be NULL. Start by running your INSERT into tblType and tblManufacturer and setting the foreign key fields to NULL. Then, once both rows exist, run an UPDATE on each table to set the correct foreign key values. You just need to make sure you don't have any NOT NULL constraint on your foreign key fields.