🔒 Locked

MySQL problem (foreign key violation)

Started by graveyard filla Nov 21, 2004 at 5:15 AM 4 replies 900+ views

Original Post

Nov 21, 2004 at 5:15 AM

hi, im working on a 2d online RPG. anyway, im setting up the login and character creation part of the game. i have 2 tables - a table which stores accounts, and a table which stores characters. here is what they look like:


CREATE TABLE T_AC_ACCOUNTS
(
   AC_ID INT NOT NULL AUTO_INCREMENT,
   USERNAME VARCHAR(20),
   PASSWORD VARCHAR(20),
   FIRST_NAME VARCHAR(50),
   LAST_NAME VARCHAR(50),
   EMAIL VARCHAR(150),
   LAST_LOGIN TIMESTAMP,
   
   UNIQUE (USERNAME),
   PRIMARY KEY (AC_ID)
);


CREATE TABLE T_CH_CHARACTERS
(
  CH_ID INT NOT NULL AUTO_INCREMENT,
  
  CH_AC_ID INT,
  NAME VARCHAR(30),
  XPOS INT,
  YPOS INT,
  MAP_OCCUPIED VARCHAR(150),
  
  FOREIGN KEY (CH_ID) REFERENCES T_AC_ACCOUNTS (AC_ID),
  PRIMARY KEY (CH_ID)

);

now, i can add accounts just fine. but im having a problem trying to add a character to an account. heres how i make an account:


INSERT INTO T_AC_ACCOUNTS (USERNAME,PASSWORD,FIRST_NAME,LAST_NAME,EMAIL) 
VALUES ('some account','some pass','blah','bleeh','spam@me.com');

now that i have an account created, i'd like to add characters to this account. i do it like this:


INSERT INTO T_CH_CHARACTERS (ACCOUNT_ID,NAME,XPOS,YPOS,MAP_OCCUPIED)
VALUES (1,'some character',802,440,'somemap.map');

now, this works fine. however, when i try to add another character to the same account (has the same CH_AC_ID), then i get a foreign key violation. i'm not sure what im doing wrong, but i know its something obvious. please go easy on me, its 6:20 in the morning and i've been up all night coding. time to sleep now [smile]. thanks for any help.

snk_kid

Nov 21, 2004 at 5:26 AM

I haven't done database programming in a while so i'm probably wrong, it seems to me having a primary key as foreign key aswell seems a bit strange, what kind of relationship are you modelling, 1-to-many?

markr

Nov 21, 2004 at 5:38 AM

The same column can't be a primary and a foreign key - that doesn't make sense.

CH_AC_ID is the FK, CH_ID is the PK.

Get it sorted.

Oh, and while you're at it, stop using obfuscated names for your tables/columns. What's wrong with "accounts" and "characters" ?

Mark

necromancer_df

Nov 21, 2004 at 5:58 AM

You need to add another field to the characters table that is the foriegn key. CH_ID can't be used as the foreign key because that is autoincremented so as when you add more than one character for an account the numbers won't match up with the account ID anymore.

graveyard filla

Nov 21, 2004 at 12:44 PM

thanks everyone. i knew it was something obvious (forgot the _AC when specyfing the foreign key for the character table).

btw, my Oracle professor had made us use this "obfuscated" naming convention. i guess for a small database like this it doesn't matter, but i think its a good habit.

thanks again.

Toolmaker

Nov 21, 2004 at 5:17 PM

Use normal table names, and prefix the fields. At least, that's what I'm doing. For my journal, I have JournalPosts, Journals and JournalComments. The prefix are JP_, J_ and JC_. If a table has a foreing key, like the JournalPosts' journal ID, I use the tables prefix. So it's JournalPosts.J_ID(Point to Journal.J_ID).

That's much clearer to use.

Toolmaker

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.