[SQL] Can't edit - No primary or unique key.

[noodleman1]

Hi,

I was trying to edit the world -> playcreateinfo_spells table but got stuck because of the following error, using HeidiSQL:



Now I'm unable to edit anything in the table, can't even modify default entries. I am able to add new rows though, but this will often result in a replacement by "65535" for the spellid column, especially when entering high numbers (= Spell IDs). Can't delete the row afterwards.


Help appreciated

Edit:
Video here -> http://v6.tinypic.com/player.swf?file=ip84fp&s=6

[stoneharry]

Right click on the table and press alter. Change it so that it has a key field or the key field can't be null, problem solved.

[noodleman1]

Right click on the table and press alter. Change it so that it has a key field or the key field can't be null, problem solved.

Thanks for your quick reply , could you explain the last step once more? I don't see any key field related options in the alter window..



Or should I switch to Navicat to perform this fix?

[stoneharry]

I use SQLyog, I don't know with navicat or hedisql. In a minute I will upload a screenshot, just give me a minute to download a SQL server. I'll edit this post.

[noodleman1]

Hmm, odd. I think mine are configured that way. Found something similar in HeidiSQL:
(changed the default value to 1 to try that, as Not Null and default = 0 seemed contradictionary, but didn't work.)

[noodleman1]

I seem to be able to edit in SQLYog just fine, and the changes work (as in, affect the game). Some spell IDs (I think anything > 65535 ) still get changed into 65535 though, preventing me from adding the spells I want. Is there like a setting for "max entry" in the table?

[stoneharry]

It's probably changed automaticaly be the core when you run the worldserver. :/ I don't know.

[Algorithm]

just run this query

ALTER IGNORE TABLE playcreateinfo_spells ADD PRIMARY KEY(indexid);

problem solved

[noodleman1]

just run this query

Ah, so this forced on a primary key? I am able to edit things now, but the key settings are probably wrong, as every entry needs to be unique (so I can't add multiple spells for the same class/race combo because that would result in a duplicate entry for indexid). For this reason, it has also deleted all other entries which weren't unique, leaving my table with only one spell per class/race (=indexid), oh well. Do you know how to change these "unique" settings using a query?

Thanks!

[noodleman1]

Anyone? :-/