Newbie SQL Server relational question

[cosmoh2o]

Please Help,

I am VERY new to SQL Server so this is probably a very basic question. Say I have two tables that are related. How can I "insert" values into both tables without violating "foriegn key" constraints with C# code?

Table One:
ISBN (text, Primary Key), code (text)
Table Two:
code (text, Primary Key - references One.code),
description (text)

Any help/hints would be great. Thanks to any one that helps.

 

[rac2]

I think, and I hope one of the true SQL experts will jump on me if I wrong, that there are is a fundamental point underlying this question. Thatis, INSERT statements only affect a single table, you can only insert a row into one table at a time. So insert the row in Table One first; that makes the new ISBN valid for any foreign key constraints. Then insert the new row in Table Two

 

[mrdenny]

rac2 correct.

Denny

Between the ESP=ON and the RUM (Read Users Mind) upgrade, I'm ready to go.

 

[cosmoh2o]

Thanks to rac2 and mrdenny for your help! All I have ever programmed for an RDBMS is MySQL and foriegn key constraits were really not true. This is my first attempt at MS SQL Server.

Is the "insert" method the most common way to populate records in related tables with MS SQL Server?

Once again, thank you both for your help.

 

[OhioSteve]

rac2 is correct. Let me add a bit to his statement.

Lets say that you have two tables: state and city. The primary key in the state table is "name". city has a foreign key called "stateName". The state table only includes 40 states, because no one has bothered to enter them all.

If you want to add a city to the city table, and you want to avoid the SQL server error messages, you will need to test the waters before you insert a record into city. You will need to do a select query to find the state listed in stateName. If it does not appear, you will need to add it to the state table. THEN you can add the city to the city table without fear.

 

[kmcsql]

Use the Dataset concepts available in C# (VS.net)

There are some very nice features of using datasets that allow you to accomplish this task (and many more tasks!)

Ken

 

[OhioSteve]

Yeah, there are alot of ways to automate the processes that we are describing, depending on what tools you have.

 

[cosmoh2o]

Thanks for all the great help! You guys know your stuff.