|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!
|
|
#1
May 4th, 2008, 11:07 AM
|
|||
|
|||
|
Help with Foreign Key
I'm brand new to MySql so please be patient with me as I learn from all you experts!
I have two tables: order_master and order_detail. order_master contains a primary key of 'id' and order_detail has a field of 'order_id" which should contain the same value as 'id' in order_master. I don't know how to recreate the create table query (if someone could tell me, that would be great!), but here are the results from describing my tables: Code:
describe order_master; +---------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+----------------+ | id | int(12) | NO | PRI | NULL | auto_increment | | order_date | datetime | YES | | NULL | | | ship_date | datetime | YES | | NULL | | | customer_name | varchar(40) | NO | | | | | address | varchar(100) | NO | | | | | item_total | float(10,2) | NO | | | | | total_amt_due | float(10,2) | NO | | | | +---------------+--------------+------+-----+---------+----------------+ and order_detail: Code:
describe order_detail; +----------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+-------------+------+-----+---------+----------------+ | order_id | int(12) | NO | MUL | NULL | auto_increment | | item_name | varchar(20) | NO | | | | | quantity | int(6) | NO | | | | | price_per_unit | float(10,2) | NO | | | | +----------------+-------------+------+-----+---------+----------------+ Here is the mysql command to insert data into the order_master table and it works great: Code:
mysql_query("INSERT INTO order_master (order_date, customer_name, address, item_total, total_amt_due) " . "VALUES ('$order_date','$customer_name', '$address', '$item_total', '$total_amt_due')", $db);
I'm getting a 'Cannot add or update a child row: a foreign key constraint fails' error when trying to insert into the order_detail table though. Here is my mysql commend for that: Code:
mysql_query("INSERT INTO order_detail (item_name, quantity, price_per_unit) " . "VALUES ('calendar', '$calqty', '10')", $db);
Can someone spot my (hopefully) simple error?
|
|
#2
May 4th, 2008, 11:28 AM
|
||||
|
||||
|
There is a small trick, a function that retrieves the last autoincrement value assigned by the database (because your order_master.id is an autoincrement field) and lets you reuse that.
See this small example Code:
mysql> use test;
Database changed
mysql> CREATE TABLE `test`.`order_header` (
-> `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
-> `order_date` DATETIME NOT NULL,
-> PRIMARY KEY (`id`)
-> )
-> ENGINE = InnoDB;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE `test`.`order_details` (
-> `order_id` INTEGER UNSIGNED NOT NULL,
-> `item_name` VARCHAR(45) NOT NULL,
-> `quantity` INTEGER UNSIGNED NOT NULL,
-> PRIMARY KEY (`order_id`, `item_name`)
-> )
-> ENGINE = InnoDB;
Query OK, 0 rows affected (0.05 sec)
mysql> ALTER TABLE `test`.`order_details` ADD CONSTRAINT `FK_order_details_1` FO
REIGN KEY `FK_order_details_1` (`order_id`)
-> REFERENCES `order_header` (`id`)
-> ON DELETE RESTRICT
-> ON UPDATE RESTRICT;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into order_header(id, order_date) values (NULL, now());
Query OK, 1 row affected (0.00 sec)
mysql> select * from order_header;
+----+---------------------+
| id | order_date |
+----+---------------------+
| 1 | 2008-05-04 18:23:06 |
+----+---------------------+
1 row in set (0.00 sec)
mysql> insert into order_details (order_id, item_name, quantity) values (last_in
sert_id(), 'an item', 6);
Query OK, 1 row affected (0.02 sec)
mysql> select * from order_details;
+----------+-----------+----------+
| order_id | item_name | quantity |
+----------+-----------+----------+
| 1 | an item | 6 |
+----------+-----------+----------+
1 row in set (0.00 sec)
mysql>
Note that both inserts should be wrapped inside a transaction (but this is another story).
__________________
My blog about OpenSource Databases PDF tutorials about OSS databases, DBMonster ... Please contribute to Open Source Development, fill bug reports!!! Developer Shed eSupport Commented my.ini/my.cnf (PLEASE ADD YOUR OWN CONFIG TRICK) Understanding SQL Joins An introduction to database normalization Natural or Surrogate key Custom ordering for your results Correlated and uncorrelated subqueries
|
|
#3
May 4th, 2008, 01:50 PM
|
|||
|
|||
|
Thanks! I think I figured it out.
I used mysql_insert_id() versus last_insert_id() -- are they pretty much the same thing? And for such a simple application, was I over thinking the need for foreign keys? Once I removed that relationship, I didn't get that error anymore. I see in your example you still are using foreign keys...? Let me know your thoughts and thanks for helping a newbie!
|
|
#4
May 4th, 2008, 02:32 PM
|
||||
|
||||
|
Quote:
Quote:
|
|
#5
May 4th, 2008, 02:58 PM
|
|||
|
|||
|
Thanks again... turns out I wasn't quite using mysql_insert_id() correctly, but I figured that out and the order ids between tables match like they should!!
I also reapplied the foreign keys (recall I had removed them) and this time made them RESTRICT per your example instead of CASCADE which caused foreign key errors. If I may ask of you, could you enlighten me as to why RESTRICT works and CASCADE doesn't?
|
|
| Viewing: Dev Shed Forums > Databases > MySQL Help > Help with Foreign Key |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
