mysql - How to setup relationship between tables In phpMyAdmin -

the question have is, when create table example: table1 following columns:

• customerid
• customername
• address
• state

where customerid primary key auto_increment.

and table2 example columns:

• purchaseid
• customerid
• product
• cost

where primary key purchaseid , foreign key customerid table1.

it should mean established relationship between table1 , table2 using customerid.

both these tables empty wrote sql command:

insert table1 (customername,address,state) values('value1','value2','value3') 

this works fine, when try insert child table (table2) tells me:

error foreign key constraint

so want insert parent table child table customerid shows in table2 (child table) foreign key , corresponds customerid in table1 (parent table).

do have create 2 tables first without foreign key try establish relationship. keeps saying there constraint long relationship there.

the table2 foreign key constraint means table2 customerid value must appear customerid in table1. getting error because inserting customerid table2 doesn't appear in table1.

since dbms generating table1 customerids auto increment, if insert row have value in order insert row using customerid table2.

i guess "i established relationship between table1 , table2" mean "i declared foreign key constraint". , guess think means "after insert table1 dbms use auto-generated key value foreign key value when insert table2". doesn't mean that. have yourself. foreign key constraint means dbms checks every table2 customerid value appears table1 customerid value.

you can , must use inserted key value corresponding value when insert table foreign key key.

to auto incremented key value generated dbms use last_insert_id():

insert table1 (customername,address,state) values('value1','value2','value3'); insert table2 (customerid,product,cost) values(last_insert_id(),'valuea','valueb'); 

this for. here problems if don't use it.

first, if not in serialized transaction must use last_insert_id(). because after table1 insert before table2 insert others have added rows and/or deleted rows including new row and/or changed rows including new row. cannot rely on querying table1 after insert customerid value know added.

second, suppose in serialized transaction , don't use last_insert_id().

if (customername,address,state) is superkey of table1, ie values unique, ie sql unique/key/pk declared on or of columns, can use query associated new customerid:

set @customerid = (     select customerid     table1     customername = 'value1'     , address = 'value2'     , state = 'value3'); insert table2 (customerid,product,cost) values(@customerid,'valuea','valueb'); 

but if (customername,address,state) is not superkey of table1 cannot this. because other rows duplicates subrow in table1. multiple rows back. not know newest one. instead have query table1 before insert, insert, find difference between old , new sets of customerids:

create temporary table table1old (     customerid (int) primary key     ); insert table1old select customerid table1;  insert table1 (customername,address,state) values('value1','value2','value3');  set @customerid = (     select customerid     table1     customername not in table1old); insert table2 (customerid,product,cost) values(@customerid,'valuea','valueb'); 

just use last_insert_id().

ps: interestingly, given table definitions, ideally 1 write:

insert (     select customername,address,state,a,b     table1 join table2     using (customerid)) values('value1','value2','value3','valuea','valueb') 

since there 1 pair of new table1 & table2 values can result. there legal updates through views in sql, although none involving multiple tables in mysql currently