Question on primary key and 2 tables

  • Thread starter Thread starter Doug
  • Start date Start date
D

Doug

Hi,

I have 2 tables (in SQL Server) and I need the primary key from the first
table to be the foreign key in the second table. If the user is entering
data that will be inserted into each table on the same form, is it the best
way to insert the values into the first table, then read it for the primary
key, store that value and build the SQL statement for the second insert
afterwards? If not, what would be a better way? Thank.

Respectively,
Doug
 
Doug:

You may want to use a DataRelation to relate the two tables initially.
Remember that this relation doesn't need but mimic a hard relationship in
your actual DB although most of the time it probably will.
http://www.knowdotnet.com/articles/datarelation.html

Now, you have a DataAdapter for the parent table and one for the child(ren).
The record will obviously need to be added to the parent first, and
depending on how you set up your controls (or inserts if you aren't using
many/any of them), then to the child. IF you have a datagrid for instance
and use a BindingContext, when you change a record in your parent table, the
Grid will populate itself with the matching child data. If you add a record
into the grid, it will add in the related value for you.

You can do this manually as well if you want. Now, you should have update
logic in both the Adapters for both parent and child. Call the update on
the parent table first, then the child. If you have autoincrement fields or
fields assigned by the db, you'll want to get those back and update the
parent, which (provided you have cascading on for the datacolumn) will
automatically update the local copy of the child records. Then call the
update on the child table. If you don't have autoincremented fields or
fields that are based on the database itself, this wouldn't be necessary.
Bill Vaughn's article on Managing an @@IDentity Crisis
http://www.betav.com/msdn_magazine.htm shows you how to do this.

HTH,

Bill
 
William,

Thank you - was not 100% what I was looking for, but that's more to do with
my inability to articulate my question. I didn't actually setup a relation
between to the 2 tables (probably because I was fat and lazy and didn't
think about it). But I'll review your article and give it a try.

Thanks,
Doug
 
Setting the relation is only part of the problem, but it will effectively
get you on the way. You could also manually write the parent record, then
make another trip and manually write the child(ren) records but I think this
approach is much cleaner. Moreoever, you can also write your datasets to
XML in case anything fails and update them later, and wrap them in
transactions which can be very helpful.

Let me know if you have any problems...

Bill
 
Back
Top