foreign keys auto insert

[dweeb]

Hi 

 

I need some help with the idea of foreign keys please and how they work when inserting data.

 

I have two tables

 

CLIENTS

client_id   PK AUTOINCREMENT

client_name

address_id

 

ADDRESS

address_id  PK AUTOINCREMENT

building_name

street_name

town

 

I want to relate the two via the address_id which I  think I have to make a foreign key in the clients table but am unsure of how to do this. I believe that this will also mean I cannot add a client without a related address. Is this correct? how do I make the address_id in the clients table a foreign key? I am using phpmyadmin via cpanel on my hosting account, do i need to declare it like a primary key?

 

Also when I add a new client with address at the same time via an insert statement how would i get the new address_id to insert into the clients table or would this happen automatically?

 

Thanks

[gizmola]

1. A foreign key is a column in a table that is NOT part or whole of the primary key of that table, but instead is the primary key of ANOTHER table. This is why it is called a "foreign" key. It's not *my* key --- it's that foreign tables' key. Logically, you have accomplished this by creating the address_id column in client, that will let you relate a single foreign address row to a client. Just make sure that the datatype of the address_id column in client is exactly the same datatype as it is in Address. (Signed vs Unsigned, int vs bigint)

 

2. Automatic behavior associated with foreign keys is accomplished with constraints. With mysql you need to use a storage engine that supports foreign key constraints. This means using the InnoDB engine for your tables.

 

3. The constraint is responsible for enforcing/constraining the relationship between the tables. If you are using the MyISAM engine for your tables, you can still relate clients and addresses and get the benefit of a proper relational design. In this case, the implied benefit is that many clients will share the same address. If that is the case, you require less storage.

 

4. Assuming you are using Innodb, this page in the mysql manual covers the syntax for creating constraints: http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html In your case, if you've already created these tables, then you want to alter the client table, using the syntax in the manual page. Something like this is probably what you want:

ALTER TABLE CLIENTS
ADD CONSTRAINT FK_Address
FOREIGN KEY (address_id) REFERENCES ADDRESS(address_id)

5. In this case, you really don't want anything to happen automatically when you delete or change an address row, other than to prevent a client having an address_id that does not exist in the address table, or to prevent the deletion of an address that is related to an existing client. So you won't want to specify any CASCADE clauses for this relationship.

 

6. As long as the address_id column in CLIENT isn't set to NOT NULL, you will be able to utilize the "0 or 1 to many relationship", allowing you to create a client row that does not have an associated address.

 

7. There is no magic statement that will set the address_id in the client table. You need to set the value of address_id in your INSERT or UPDATE statements that involve clients.