Thread

Hello,
i have this answer, i have 2 tables, address and customer, down you can see structure:

address table
——————
addressID PK (AutoInc)
Town
Street
ZIP

customer table
——————
customerID (AutoInc)
Name
addressID FK

Ok, i will work with these 2 tables. There is a form where you can fill customer Name and address. After filling form i want to add information to 2 tables, but i am not sure how FK works.

Firstly add address info to address table, addressID will be generated with autoincrement.
Then get actualy addressID and add customer info to customer table.

How can i get actually addressID ? Is there some fast way ? 

Thanks for help.

use ‘LAST_INSERT_ID()’ as ‘addressID’ value at second mysql insert statement.

Thanks for help, i have another question.

I really cannot delete any row in my tables, it still write something about foreign key, i tried truncate table and it doesnt work, how can i solve this problem ?

Is good to use foreign keys in MySQL ?

It’s very important that you use foreign keys in MySQL. They keep you data integrity. The error that you get is from the foreign keys, but it’s correct. Whenever you want to delete a row, you must first delete it’s dependencies.

address table
——————
addressID PK (AutoInc)
Town
Street
ZIP

customer table
——————
customerID (AutoInc)
Name
addressID FK

This means that whenever you want to delete a customer row, you must first delete it’s address because otherwise it will become an “orphan” row.

I really cannot delete any row in my tables, it still write something about foreign key, i tried truncate table and it doesnt work, how can i solve this problem ?

It ussually happened when you’re not using cascade delete on your foreign key. It seems another key relationship prevents the deletion. For example, lets say you have 3 table

1. ‘country’ table have ‘id’, ‘name’
2. ‘city’ table have ‘id’, ‘name’ and ‘country_id’ which foreign key reference country(‘id’)
3. ‘maps’ table have ‘id’, ‘lat’,‘lng’ and ‘country_id’ which foreign key reference country(‘id’)

Lets say you have one row of data on each that table, like

1. country = (1, ‘Andorra’)
2. city = (1, ‘Andorra’ , 1)
3. maps = (1, ‘37,84934983’ , ‘7,493794739’, 1)

as you can see, both of city and maps ‘country_id’ field is pointing to same ‘id’ of country table. In this case, you cannot just delete city and country record because another table (map) have a record which pointing to ‘id’ field in country table.

To avoid above problems, you can use cascade delete to your foreign key, so that when you (for example) delete some ‘id’ record in country table, it automaticly deleting corresponding value in it’s child table.

Is good to use foreign keys in MySQL ?

Depending on your needs, but in my opinion, foreign key is helpfull concept in MySQL. It keep you from making mistake such as :
1. You cannot insert some value to child table, which doesn’t already exist in parent table.
2. You cannot deleting relational record, as i ilustrated above.

If you want to use foreign keys you have to use the InnoDB engine MySQL doe’s not have foreign keys!

InsiteFX

...MySQL doe’s not have foreign keys!...

What are you talking about? 🐛  I believe MySQL DOES!  :coolhmm:

Maybe you pointing to ‘MyISAM engine’. Its true that in MyISAM engine, foreign key has no actual effect, and serves only as a memo or comment to you that the column which you are currently defining is intended to refer to a column in another table.

Maybe you pointing to ‘MyISAM engine’.

Looking at the context, I think it’s safe to assume that it was an oversight. It’s MyISAM not MySQL 😊

1.8.5.4. Foreign Keys

The InnoDB storage engine supports checking of foreign key constraints, including CASCADE, ON DELETE, and ON UPDATE. See Section 13.6.4.4, “FOREIGN KEY Constraints”.

For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it. In the future, the implementation will be extended to store this information in the table specification file so that it may be retrieved by mysqldump and ODBC. At a later stage, foreign key constraints will be implemented for MyISAM tables as well.

So if you want true foreign keys you have to use InnoDB!

InsiteFX

@InsiteFX

What you said, to use InnoDB Engine instead other engine (MyISAM for example), is right. I forgot to mention it earlier in this thread, and you make it clear now. No one denied or reject that.

The only things that make me little bit surprised, is you said ‘...MySQL doe’s not have foreign keys!...’. That’s statement contain an understanding (especially for me, i guessed for other too) that MySQL doesnt supporting foreign keys concept, which we all already know that it doesn’t right!

@toopay
I said that because no one here mentioned the InnoDB.

MySQL defaults to the InnoDB database now when you create a new database.

But you should still specify the Engine=InnoDB for the type.

Just like a lot of users create their database and then wonder why utf8 and utf8_general_ci doe’s not work!

And the answer is because when you create a new database MySQL sets the collation to a default of latin1_swedish_ci !

With phpMyAdmin you have to click on the new database before entering any records and then click on the Operations tab, then if you look at the bottom of the page you will see the collation is set to latin1_swedish_ci. To change enter a new one either utf8_general_ci or utf8_unicode_ci click GO!

If the database is already containing records then you need to make a SQL dump change the collation and the use the dump to put them back or they will get corrupted.

InsiteFX

But i cannot delete ID adress or customer ID, all writes error.

Are you sure you’re giving us the whole table structure? If your tables look exactly like you posted at the beginning of the discussion, you should be able to delete the address entry without any problems. If it’s dependent on another table that I don’t know about, I can’t help you.

DELETE FROM `dbs`.`address` WHERE `address`.`address_ID` =1

MySQL show: Documentation
#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`dbs`.`customer`, CONSTRAINT `FK_customer_address` FOREIGN KEY (`addressID`) REFERENCES `address` (`address_ID`))

This calls for some more research. I’ll whip up some test case tonight when I get home from the office.