[MySQL] Foreign keys problem - Printable Version

[MySQL] Foreign keys problem - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+--- Thread: [MySQL] Foreign keys problem (/showthread.php?tid=424681)

[MySQL] Foreign keys problem - dusk - 23.03.2013

Hey everyone,i know this isn't the right place for it but here i go:

My goal is to make numbers go 1,2,3,4,5 automatic for every player in mysql. (for a car system). So people told me:create a foreign key. So i did. I have 2 tables:
1st. users with column userid(auto increment and primary)
2nd. vehicles with column vehicleid(auto increment and primary)

So I created a foreign key from the vehicles relation view,and now I can't insert any data into the vehicles table. Need help.Any ideas?

Re: [MySQL] Foreign keys problem - Stylock - 23.03.2013

No ideas... Can you at least provide some code on how are you inserting data into the table? And how did you create foreign keys?

Re: [MySQL] Foreign keys problem - dusk - 23.03.2013

I don't think code is needed here. I tried using inserting by SQL in phpmyadmin.

I created it by relation view in pma.

Re: [MySQL] Foreign keys problem - Stylock - 23.03.2013

You're probably trying to insert an ID that doesn't exist in the parent table.

Re: [MySQL] Foreign keys problem - dusk - 23.03.2013

What do you mean?

Re: [MySQL] Foreign keys problem - Stylock - 23.03.2013

A record must exist in the "users" table before you can insert it in the "vehicles" table. Make sense?

Re: [MySQL] Foreign keys problem - dusk - 23.03.2013

It does, but it does not help, I get this error while inserting into vehicles:

pawn Код:

1452 - Cannot add or update a child row: a foreign key constraint fails (`bebras`.`vehicles`, CONSTRAINT `vehicles_ibfk_1` FOREIGN KEY (`vehicleid`) REFERENCES `users` (`userid`))

Re: [MySQL] Foreign keys problem - Stylock - 23.03.2013

Your database is screwed. You've made a foreign key out of the primary key. This is how your database looks like:

Код:

users
--------
userid PK
username
other
--------

vehicles
--------
vehicleid PK FK
other
--------

This is how it should look like:

Код:

users
--------
userid PK
username
other
--------

vehicles
--------
vehicleid PK
userid FK
other
--------

I can't help you any further, since your goal doesn't make any sense to me whatsoever.

Re: [MySQL] Foreign keys problem - dusk - 23.03.2013

What should be the index for userid in the vehicles table?

My goal is auto numeration per player

Re: [MySQL] Foreign keys problem - Stylock - 24.03.2013

Well, I still have no idea what you're trying to achieve, but you should learn how to properly use foreign keys.

Some info about foreign keys:
https://sampforum.blast.hk/showthread.php?tid=420363

I also suggest using MySQL Workbench instead of phpmyadmin.