Related Table Problem 1

[ZOR]

I am trying to follow an example I found for creating two tables in a "related form" The code creates the two tables, however if I use PhpMYadmin to enter primary records in the first table, I get an error on trying to add a second record. Can someone advise whats wrong, thanks

CREATE TABLE customer
(
customer_id INT NOT NULL,
name VARCHAR(30),
PRIMARY KEY (customer_id)
) TYPE = INNODB;

CREATE TABLE customer_sales
(
transaction_id INT NOT NULL,
amount INT,
customer_id INT NOT NULL,
PRIMARY KEY(transaction_id),
INDEX (customer_id),
FOREIGN KEY (customer_id) REFERENCES customer (customer_id)
) TYPE = INNODB;

#1062 - Duplicate entry '0' for key 1


Also, is there an easy way to convert code into MySql_query lines. Thanks

 

[TonyGroves]

The error message says that the value you're supplying for the customer_id field of the new record is already being used by another record, and since that field is a primary key, duplicate values are not allowed, so the record is being rejected. You need to supply a unique value for customer_id.

 

[ZOR]

Thanks Tony, is that due to auto increment being adopted in the code, or have I to input numbers manually each time. Thanks

 

[TonyGroves]

customer_id is not declared to be AUTO_INCREMENT, so you must supply a value each time.

If you want customer_id to be auto_increment, you can say so:[tt]
ALTER TABLE customer MODIFY customer_id INT AUTO_INCREMENT
[/tt]

 

[ZOR]

Thanyou very much, that sorted out the primary table. Am I correct in thinking that when I add records to the second (child) table, the primary record has to be identified in some way in order for the correct parent record to get the additional child record. Also as child records get entered will that table need to be auto incrented on its primay key. Sorry to ask so much as comming from access one does not have to think so much. Regards

 

[TonyGroves]

When you insert customer_sales records, you must supply a value for customer_id; that's enough to identify the parent record.

The same logic applies to transaction_id in the customer_sales table as to customer_id in the customer table - either make the field auto_increment or supply a unique value each time.

 

[ZOR]

Excellent Tony, thanks very much for getting me off the ground. There seems little easy to find, easy to follow information on the subject of related tables. I came accross this link which looks good, and may help someone else down the road

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

Have a star, good weekend. Thanks