CODEWITHSUNDEEP
mysqlsqlrefactoring
通りすがりのおっさん
通りすがりのおっさん

Reputation: 699

How to simplify my SQL requests?

I have two tables here. One is Items and other is Parts.

Items have a part_id and Parts have an item_id.

When a user press on the submit button from the ItemDetail view, data are sent to the server and inserted into those two tables.

Here is how my code works :

  1. Insert to Items table first and get the id of new Item data
  2. Insert to Parts table with this item_id and other Part data
  3. Update to Items table using new part_id

But can I write those three SQL requests in just one request ?

Here is the structure of my tables:

Items

Field | Type | Null | Key | Default | Extra |
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
| price | int(11) | YES | | NULL | |
| part_id | int(10) unsigned | YES | | NULL | |
| type | varchar(255) | YES | | NULL | |

Parts

Field | Type | Null | Key | Default | Extra |
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| item_id | int(10) unsigned | NO | | NULL | |
| name | varchar(255) | NO | | NULL | |
| number | varchar(255) | YES | | NULL | |

Upvotes: 1

Views: 58

Answers (2)

Jester
Jester

Reputation: 1408

You shouldn't have 2 tables pointing to each other like this, only one of the tables should have a foreign key, not both.

Then what you are looking for is this: http://dev.mysql.com/doc/refman/5.7/en/commit.html

Transactions make sure that either all queries are executed, or if there is an error somewhere all changes will be reverted.

Upvotes: 1

Caz1224
Caz1224

Reputation: 1569

Looking at the logic you are using, you are doing it correctly.

As they are two separate tables you will need to do two separate insert statements in SQL. Of course you can use a stored procedure so that you only need to call one item in your code and the SP will do two inserts.

A question here is what code are you using? If you are using something like entity framework and your relationships are defined between your elements such as

Items

-Field 1

-Parts (FK) List<Parts>

That would work, but looking at what you have tagged I am guessing your not using a ASP language?? If you are let me know and I may have a better solution for you.

Upvotes: 0

Related Questions