MySQL Foreign Keys

[suley04]

Hi everyone,

First of all let me just tell you that I have never done database programming myself, and this is the first time I'm setting up a MySQL database via phpMyAdmin, so I could be missing something small but I'm sure someone will know the answer, anyways...

I have created and set up a MySQL database, and have created two tables ("Users" and "Payment Info"). I have added a unique ID field (along with other fields) in Users AND Payment Info. The Unique ID field in both tables are supposed to link both sets of records.

Both tables are set up using the InnoDB engine and I have tried to set up the Foreign Key but it wont work, here is the error message I'm getting:

"#1452 - Cannot add or update a child row: a foreign key constraint fails (`package5_PPS/Users`, CONSTRAINT `Users_ibfk_1` FOREIGN KEY (`Unique ID`) REFERENCES `Payment Info` (`Unique ID`) ON DELETE CASCADE ON UPDATE CASCADE)"

Please can you help, or please can you let me know if you need any more information before you can solve the problem.

This is for my full time job so please could you reply ASAP. All help is very much appreciated. Thanks.

[smantscheff]

You are trying to enter a payment with a user ID for which there exists no user.
Show us the table contents and your insert statement for further help.

[suley04]

Well, that is the problem. It wont let me add anything because of that error. I was just trying to insert any old sample data, which still falls into the constraints of each field (e.g. business name = varchar and 20 characters size limit, etc).

When I was trying to insert the data to see if the foreign key was working or not, that's when I got the error message posted previously.

The insert statement I am using is as follows:

INSERT INTO `package5_PPS`.`Users` (

`Unique ID` ,
`Date` ,
`Completed By` ,
`Business Name` ,
`Address` ,
`Contact Name` ,
`Postcode` ,
`Telephone` ,
`Fax` ,
`Mobile` ,
`E-Mail` ,
`Payment Name` ,
`Phone No.` ,
`Delivery Address` ,
`Opening Times` ,
`Business Type` ,
`Home Address` ,
`Nature of Business` ,
`Years Trading` ,
`Registration Number` ,
`Registered Office` ,
`VAT No.` ,
`Trade Reference 1` ,
`Trade Reference 2`
)
VALUES (
'45', '2011-09-29', 'Joe Bloggs', 'bloggs inc', '45 waste of time street', 'joseph bloggs', 'HS7 5NS', '454561', '54564', '7454561', '[e-mail]', 'joe bloggs', '7865513', '8 sjkas road', '9 - 5', 'Sole trader', '894 asjkasdkjlm road', 'aquatic shop', '45', '1231', '27 boring street', '454482', 'asjkdjkasdnkjn', 'wyaohsitn'
)

[suley04]

Well, as it turns out I have managed to add a record to both tables, using the same Unique ID. However, it seems as though you cannot add anything into the Users table without there first being a record in the Payment Info table, AND they both must have the same ID...

So, somehow, the logic is back to front. How can I put it the other way round, so that there first has to be something in the Users table in order to be able to add something to the Payment Info table?

[smantscheff]

You seem to have set up the foreign key for the wrong table. Since user:payments is a 1:n relation, payments needs the user id as a foreign key into the user table.
Show the output of

show create table users;
show create table `Payment info`

[suley04]

Ok, Below are the two outputs of those commands:

SHOW CREATE TABLE `Users` (
`Unique ID` int(11) NOT NULL auto_increment,
`Date` date NOT NULL,
`Completed By` varchar(20) NOT NULL,
`Business Name` varchar(60) NOT NULL,
`Address` varchar(100) NOT NULL,
`Contact Name` varchar(20) NOT NULL,
`Postcode` varchar(8) NOT NULL,
`Telephone` varchar(20) NOT NULL,
`Fax` varchar(16) NOT NULL,
`Mobile` varchar(16) NOT NULL,
`E-Mail` varchar(40) NOT NULL,
`Payment Name` varchar(20) NOT NULL,
`Phone No.` varchar(16) NOT NULL,
`Delivery Address` varchar(100) NOT NULL,
`Opening Times` varchar(15) NOT NULL,
`Business Type` varchar(15) NOT NULL,
`Home Address` varchar(100) NOT NULL,
`Nature of Business` varchar(20) NOT NULL,
`Years Trading` int(3) NOT NULL,
`Registration Number` int(10) NOT NULL,
`Registered Office` varchar(100) NOT NULL,
`VAT No.` int(11) NOT NULL,
`Trade Reference 1` varchar(100) NOT NULL,
`Trade Reference 2` varchar(100) NOT NULL,
PRIMARY KEY (`Unique ID`),
CONSTRAINT `Users_ibfk_1` FOREIGN KEY (`Unique ID`) REFERENCES `Payment Info`
(`Unique ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin

=============================================================================

SHOW CREATE TABLE `Payment Info` (
`Unique ID` int(11) NOT NULL auto_increment,
`Bank Name` varchar(20) NOT NULL,
`Address` varchar(100) NOT NULL,
`Account No.` int(8) NOT NULL,
`Sort Code` int(6) NOT NULL,
PRIMARY KEY (`Unique ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

Hope that helps. Can anyone recognise where the problem is coming from?
All help is very much appreciated, and thanks in advance.

[smantscheff]

This is exactly the wrong way round. You need a reference field for the user id in the payment table, too. Change your tables to

CREATE TABLE `Users` (
`Unique ID` int(11) NOT NULL auto_increment,
`Date` date NOT NULL,
`Completed By` varchar(20) NOT NULL,
`Business Name` varchar(60) NOT NULL,
`Address` varchar(100) NOT NULL,
`Contact Name` varchar(20) NOT NULL,
`Postcode` varchar(8) NOT NULL,
`Telephone` varchar(20) NOT NULL,
`Fax` varchar(16) NOT NULL,
`Mobile` varchar(16) NOT NULL,
`E-Mail` varchar(40) NOT NULL,
`Payment Name` varchar(20) NOT NULL,
`Phone No.` varchar(16) NOT NULL,
`Delivery Address` varchar(100) NOT NULL,
`Opening Times` varchar(15) NOT NULL,
`Business Type` varchar(15) NOT NULL,
`Home Address` varchar(100) NOT NULL,
`Nature of Business` varchar(20) NOT NULL,
`Years Trading` int(3) NOT NULL,
`Registration Number` int(10) NOT NULL,
`Registered Office` varchar(100) NOT NULL,
`VAT No.` int(11) NOT NULL,
`Trade Reference 1` varchar(100) NOT NULL,
`Trade Reference 2` varchar(100) NOT NULL,
PRIMARY KEY (`Unique ID`)
);


drop table if exists `Payment Info`;
CREATE TABLE `Payment Info` (
`Unique ID` int(11) NOT NULL auto_increment,
`Bank Name` varchar(20) NOT NULL,
`Address` varchar(100) NOT NULL,
`Account No.` int(8) NOT NULL,
`Sort Code` int(6) NOT NULL,
id_user integer not null,
PRIMARY KEY (`Unique ID`),
constraint users foreign key (id_user) references users (`Unique ID`)
);

To make your life easier you should also encode all data in utf-8 instead of latin1 and not use field names with blanks in them.

[suley04]

Thanks for the reply smantscheff but...

...What is the difference between using utf-8 and latin1? - just that the field names can contain blank spaces in one, and not the other?

also, what is "id_user" used for?
...and how come there is no "InnoDB" used in your code?

[drjohn]

blank spaces in a field name (or any variable in your code really) are just a bad idea, that's all. You get problems when trying to use them in your programming code. Note the use of ' for example, 'around a name' with spaces, to get round this. Names like id_user don't need the ' '.

id_user in the payments table (again, drop the blank space in the table name, it complicates things) links the user who made the payment to the payment made. It enables a user to make multiple payments (your original design meant a user could only make one payment, ever)

Also, naming your id field as 'Unique id' is a silly field name, call them id_user and id_payment, so it is clearer what you are talking about.

[suley04]

Ok... Actually, you just made me realise your point about blank spaces and the field names, so thank you... I totally understand that...

However, I don't quite understand how in my original code I only allowed a user to make a payment once and how adding "id_user" allows people to make multiple payments.

Not only that, but there won't actually be any payments made because all I am trying to do is allow a "customer" to process an order, which relates to the information held in the "Users" table and the "Payment Info" table.

Later on, further down the line, a new system will be put in place to allow multiple payments, and additional information will be held about customers but for now we only need a very basic system.

Hope that is clear.

[suley04]

Oh, and also, what about the InnoDB? - I thought you HAD to use it if you wanted to use Foreign Keys?

[smantscheff]

Yes, you will need innodb so that the foreign key constrains show any effect. If innodb is your default storage engine you can omit the clause, though.
id_user allows for multiple payments by allowing more than one row in the payments table with the same value in the id_user field.
I prefer utf-8 over latin (and other 8-bit character sets) because it adapts better to multilingual input. If the whole system - database, backend and frontend - share the same utf-8 character set, you will have less character set translation issues.

[suley04]

Ok... but I still don't understand this multiple payment thing because there are no payments.

All I want to be able to do is register a "customer" and store all their account details in the "Users" table, then take their bank details and store that in the "Payment Info" table... but there won't actually be any payments being processed. That table will ONLY store their bank details.

The purpose for doing so is that the customer can create an order online and submit it, which will POST the order details to an email address, then somebody at the other end will assess the order and manually link it to an account (in the Users table) and their bank details (from the Payment Info table), and then a paper invoice will be created for the relevant customer.

It is a bit of a bespoke system I am trying to create, and will automate some of the current procedures. Then later on, gradually, the entire process will become automated, but this is a start.

Can you please re-post the code you provided with the appropriate InnoDB statements in it, as I am not yet experienced enough to know otherwise.

Thank you very much for your help.

[smantscheff]

Your design is flawed.
If one customer has exactly one set of bank details, they should go in the same table as the customer data. If you don't store any payments in the payment table, there is no need for it.
If one customer may have more than one set of bank details, you might want to have a `Bank details` table with a 1:n relationship to customers, and further down the line a Invoice and a Payments table. Maybe you should study a few examples in http://www.databaseanswers.org/data_models/
For creating innodb tables just add an "engine innodb" to the create table statement AFTER the last bracket.

[suley04]

Ok, that is fine, but wouldn't you keep the bank details in a separate table for security reasons? - that was my reason for separating them into different tables... Sorry if that is daft... :(

And, yes it is possible for customers to have more than one set of bank details, as they may have several cards (credit, debit, visa, etc) and they could even use several banks too.

...So, should I leave them as separate tables?

[smantscheff]

If one customer may have more than one bank details set, theory dictates that bank details is a separate table. So leave the overall table structure as is.
A separate table adds only to security if it has separate access rights than the other tables. If you are building a webserver based application, it would be quite awkward to manage different virtual users for different queries and views. If you are building a custom interface, it might make sense to enhance security that way, but only if you make sure that your application is the only one with access rights to the database at all.
In my opinion this is not the most efficient aspect for database protection.

[suley04]

Ok, well thanks for all your help... I'll replace the current structure with the one you posted earlier, minus the "id_user" field, as that is not required, right?

...because each ID in the "Payment Info" table will match the ID in the "Users" table... so you can have several records in the "Payment Info" table with the same ID (to match multiple bank details to any particular user account)

[suley04]

BTW - which version of UTF-8 should I use?

[suley04]

any idea of which utf-8 to use, there is all sorts of options... :(