How to insert into a relational database ?

[ProgMan]

Hi everyone,

Can anyone tell me please how I can insert a new record in a database if it has many tables linked with each other by primary/foreign keys ? Can I do it with one sql command or do I have to use the INSERT statement for every single table its linked to ?

Thank you

 

[jimmajsterski]

You'll need to know the relationship(s) if any, between the table you want to insert into,and other tables in the database.
If the table you want to work on has a parent table, your table is a child, and if the relationship between your table and the parent table has referential integrity enforced, you must insert a record in the parent table first, so your insert in the child table will work.
There may be other considerations, which I'm sure one
of the DB people will explain.

 

[ProgMan]

Thank you jimmajsterski for your reply.

Hmmm....Not sure what you meant by parent/child tables. Can't remember if I've come across those terms before

The database I've got has about 7 tables and has relationships using keys with one another. I know the primary / foreign keys of each table.

Say I want to enter a new record of which data will go to those 7 tables. Then will I need 7 INSERT statements to update each table or can I update all tables with fewer INSERT statements?

(FYI, the primary keys are not auto generated)

 

[lingsn]

You can string multiple sql insert statements together separated by a
semicolon and execute that in one round trip to the database.

If you are using the adapter objects, then there is no way in 1.1, however
2.0 has improvements in this area. I believe it allows batch statements and
bulk inserts.

 

[cjard]

If there are 7 tables, you msut do 7 inserts
The 7 inserts must be run in a certain order with respect to the primary and foreign keys

For example, if table B has a foreign key to table A then you must insert into table A first. This is so that when you insert into B, there is a relevant parent record already existing in A. If you inserted into B first, there would be no parent record and the relationship would fail, killing the insert.

This is what lingsn meant by parent and child:

parent = the table with the primary key that must exist before a record can be inserted into the child
child = a table with a foreign key to a parent table

It is recommended that you use a transaction for multiple inserts so that the action succeeds or fails as a unit, rather than succeeding partially

 

[ProgMan]

Thank you cjard for the clarification.

Could you please give a small example (or direct me towards an example somewhere else) about how to actually do multiple inserts with primary foreign keys? I mean, I seem to understand the concept but its the keys that I'm a bit confused about. So, a real example would be of great help. I tried google but couldn't find any specific example I'm looking for.

Thank you

 

[cjard]

ehrmmm...

It sounds silly, but not really. You cant find stuff like this on google because there's nothing more than a simple concept: run insert queries, in a certain order..

Why dont ywe start by you telling me what the names of your 7 tables are, and which tables have foreign keys to which other tables

 

[ProgMan]

Thanks cjard for your attention.


Ok..Here's the ER diagram of the database

So, If I want to add a new election record to this database, what would be the sql statement(s)? (The ELECTION table is the main/parent table here)


BTY, how do I attach an image to the post as a link? (when I upload the image, I don't see any option to attach it as a link.)

 

[cjard]

If you attach a GIF, PNG or JPEG image, it is automatically shown. Screens should be posted as PNG, as JPEG compression is intended for photos where colours blend smoothly. JPEG cannot deal with harsh changes in contrast well, and considerably adds noise to high contrast images such as computer graphics, sometimes destroying necessary detail

Here are the rules determinable from looking at the relationships:
____________________________________________________
The record in Party must exist before the record in Candidate
The record in People must exist before the record in Candidate
The record in Election must exist before the record in Candidate
The record in Constit must exist before the record in Election
The relationship between Constit and Region_Constit_Links is not clear
The record in Region_G must exist before the record in Region
The record in Region_G and Region must exist before the record in Region_Consit_Links

Now, your learning excercise is to turn this into a set of statement IN ORDER of insertion to satisfy these rules. That, is the answer to your question.


Note that more powerful database systems like Oracle can have deferred primary keys. Relationships are not assessed until the transaction within which an insert occurs, completes. This greatly simplifies the job because you jsut insert in whatever order you like, and the rules are only checked at the end. Your misfortune in this regard, is that Access is your chosen database. I dont think it does relationship deferral.

 

[ProgMan]

Thanks again for your reply.

I'll come back later with the sql statements so that you can check whether I'm on the right track or not.

 

[ProgMan]

Ok...

Here are some sql statements (in no particular order) that I can think of for entering a new record to those tables. The statements here insert into three table only but what I'm worried about that whether its the correct way of doing this or not. In particular I was thinking of the key ('5906' in this case)

[B][COLOR=navy][COLOR=navy][B]INSERT INTO election ( EL_id, EL_constituency_id, EL_election_type, EL_election_year, EL_poll_date, EL_voting_population, EL_turn_out, EL_winning_votes, EL_percent_win, EL_winner_nm, EL_winner_id, EL_last_incumbant_id, EL_bye_election_reason, EL_winning_pt_id, EL_winning_pt_nm, EL_last_incumbant_pt_id, EL_last_incumbant_pt_nm )[/B][/COLOR]
[COLOR=navy][B]VALUES ([/B][/COLOR][COLOR=navy]3365, 5906, 'B', 1995, '29/09/2005', 29477, 38.6, 2680, 9.1, 'Jim Devine', 10210, 5490, 'Death', 2, 'Lab', 2, 'Lab');[/COLOR]
[/COLOR][/B]

[COLOR=navy][B]INSERT INTO candidat ( can_id, can_con_id, can_known_as, can_pp_id, can_pt_id, can_election_id, can_votes, can_pcnt, can_swing, can_flag, can_lostdeposit )[/B][/COLOR]
[COLOR=navy][FONT=Times New Roman][B]VALUES [/B][/FONT][/COLOR][COLOR=navy][FONT=Times New Roman](15602, 5906, 'Jim Devine', 10240, 2, 3365, 12319, 41.8, -9.3, NULL, 'n'[/FONT][/COLOR][COLOR=navy][FONT=Times New Roman][B]);[/B][/FONT][/COLOR]

[B][COLOR=blue][COLOR=blue][B]INSERT [/B][/COLOR][COLOR=navy][B]INTO people ( PP_lname, PP_fname, PP_sex, PP_contituency_id, PP_party_id, PP_title, PP_honor, PP_known_as, PP_aka, PP_id, NT_id ) VALUES [/B][/COLOR][COLOR=navy]('Devine', 'Jim', 'M', 5906, 2, 'Mr.', NULL, 'Jim Devine', NULL, 10240, NULL);[/COLOR]
[/COLOR][/B]

Can anyone please tell me whether I'm doing it right or not ?

Thank you.

 

[lingsn]

The code looks fine and it will work in the normal way, but please do consider this: I believe most of the time the data is input by user and the program will use the above method to insert to the database. What if let's say user input 'Devine' instead of 'Dev'ine', which is got an additional ' in the sql statement?

1) 1 way to solve this is to use the String.Replace() to replace the single ' to be double ''.

2) Use the code somethings like this

' Create INSERT statement with named parameters
nonqueryCommand.CommandText = _
"INSERT INTO Employee VALUES (@MyID, @MyFirstName, @MyLastName)"

' Add Parameters to Command Parameters collection
nonqueryCommand.Parameters.Add("@MyFirstName", SqlDbType.VarChar, 30)
nonqueryCommand.Parameters.Add("@MyLastName", SqlDbType.VarChar, 30)
nonqueryCommand.Parameters.Add("@MyID", SqlDbType.Int)

' Prepare command for repeated execution
nonqueryCommand.Prepare()

 

[cjard]

I was recently told that, for access, using parameters named as @PARAMETER_NAME will work fine, but if for any reason they do not, you can also use ? marks as parameter placeholders.

Note that lingsn code says this:

[FONT=Courier New]nonqueryCommand.Parameters.Add[COLOR=#000000]([/COLOR][COLOR=#2a00ff]"@MyFirstName"[/COLOR][COLOR=#000000], [B]SqlDbType[/B].VarChar, [/COLOR][COLOR=#990000]30[/COLOR][COLOR=#000000])[/COLOR]
[COLOR=#000000]nonqueryCommand.Parameters.Add[/COLOR][COLOR=#000000]([/COLOR][COLOR=#2a00ff]"@MyLastName"[/COLOR][COLOR=#000000], [B]SqlDbType[/B].VarChar, [/COLOR][COLOR=#990000]30[/COLOR][COLOR=#000000])[/COLOR]
[COLOR=#000000]nonqueryCommand.Parameters.Add[/COLOR][COLOR=#000000]([/COLOR][COLOR=#2a00ff]"@MyID"[/COLOR][COLOR=#000000], [B]SqlDbType[/B].Int[/COLOR][COLOR=#000000])[/COLOR][/FONT]
[FONT=Courier New]

[/FONT]

But because you are using Access, your types will be OleDbType.Varchar or something like that..



INSERT INTO table(col1, col2) VALUES(?, ?)

the order that you add parameters to the command.Parameters collection is thus of paramount importance and you cannot re-use parameters (with named parameters you can reuse them just by putting the same name in the SQL code over and over)