CodeIgniter using MySQL

[eluser]MaxEisley[/eluser]
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.

[eluser]toopay[/eluser]
use 'LAST_INSERT_ID()' as 'addressID' value at second mysql insert statement.

[eluser]MaxEisley[/eluser]
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 ?

[eluser]mihaibaboi[/eluser]
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.

[quote author="MaxEisley" date="1302367420"]address table
------------
addressID PK (AutoInc)
Town
Street
ZIP

customer table
------------
customerID (AutoInc)
Name
addressID FK
[/quote]

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.

[eluser]toopay[/eluser]
[quote author="MaxEisley" date="1302377089"]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 ?[/quote]

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.

[quote author="MaxEisley" date="1302377089"]Is good to use foreign keys in MySQL ?[/quote]
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.

[eluser]InsiteFX[/eluser]
If you want to use foreign keys you have to use the InnoDB engine MySQL doe's not have foreign keys!

InsiteFX

[eluser]toopay[/eluser]
[quote author="InsiteFX" date="1302381799"]...MySQL doe's not have foreign keys!...[/quote]

What are you talking about? :bug: 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.

[eluser]mihaibaboi[/eluser]
[quote author="toopay" date="1302382670"]Maybe you pointing to 'MyISAM engine'.[/quote]

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

[eluser]InsiteFX[/eluser]
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

[eluser]toopay[/eluser]
@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!