I'm trying to help out a classmate from a Javascript class I'm taking. He created a website with a MySQL database and had problems with foreign constraints. About 5 years ago, I had been using phpMyAdmin to do some testing of INSERT statements. I was able to insert a “user name” and other personal information into a “user” table, and then take the “user_id” (auto incremented) and insert that into a second table where there was a relationship between the two tables.
As I recall, there was a built-in MySQL function that supported this. I have looked for it and all I have found is the LAST_INSERT_ID() function. It doesn't look quite right to me, and the simple insert statement I ran throws and error.
Here's the SQL to create the database:
Code: Select all
--
-- Database : test
--
DROP DATABASE IF EXISTS `test`;
CREATE DATABASE `test`;
USE test;
SET FOREIGN_KEY_CHECKS=0;
--
-- table structure for `clients`
--
CREATE TABLE users (
user_id int NOT NULL auto_increment,
email Varchar(50) NOT NULL default '',
user_name Varchar(16) NOT NULL default '',
password Varchar(40) NOT NULL default '',
user_group Varchar(16) NOT NULL default 'User',
first_name Varchar(25) NOT NULL default '',
last_name Varchar(25) NOT NULL default '',
street_nmb int(7) NOT NULL default '0',
st_name Varchar(50) NOT NULL default '',
city Varchar(50) NOT NULL default '',
state ENUM ('AA','AE','AL','AK','AP','AR','AS','AZ','CA','CO','CT','DC','DE','FL','FM','GA','GU','HI','ID','IL','IN','IA','KS','KY','LA','MA','ME','MD','MH',
'MI','MN','MO','MP','MS','MT','NE','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA','PR','PW','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY') NOT NULL,
zipcode Mediumint(5) Unsigned NOT NULL default '00000',
reg_date DateTime,
last_edit DateTime NOT NULL,
last_editby Varchar(20) NOT NULL default '',
PRIMARY KEY (user_id)
) Engine=InnoDB;
--
-- table structure for `otherTb`
--
CREATE TABLE otherTb (
user_id Int NOT NULL default '0',
instance_id int NOT NULL auto_increment,
category_id Tinyint(2) Zerofill NOT NULL default '00',
insert_date DateTime NOT NULL,
PRIMARY KEY (instance_id),
KEY (user_id),
FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE
) Engine=InnoDB;
SET FOREIGN_KEY_CHECKS=1;Code: Select all
SET AUTOCOMMIT=0;
INSERT INTO `users` (`user_id`, `email`, `user_name`, `password`, `user_group`, `first_name`, `last_name`, `street_nmb`, `st_name`, `city`, `state`, `zipcode`, `reg_date`, `last_edit`, `last_editby`) VALUES
(NULL, 'henry@localhost', 'henryhill', '285d0c707f9644b75e1a87a62f25d0efb56800f0', 'Forum Type Three', 'henry', 'hill', 9876, 'adams st', 'youngstown', 'OH', 54321, NOW(), NOW(), 'henryhill');
VAL = LAST_INSERT_ID();
INSERT INTO `otherTb` (VAL, `instance_id`, `category_id`,) VALUES (NULL, NULL, 04);
SET AUTOCOMMIT=1;Cheers,
Rick