SQL TQuery Issue - Change PRIMARY KEY to UNIQUE

[Sacricx]

Hey Totenfluch, I got an errorlog cause of the SQL TQuery in the code in Line 224.

With Mysql:

[EventItemsSpawner.smx] All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead

With Sqlite:

[EventItemsSpawner.smx] near "AUTO_INCREMENT": syntax error

I did already change PRIMARY KEY to UNIQUE in the code for myself. The errors are gone and the tables got created.

best regards

Sacricx

[Totenfluch]

I did already change PRIMARY KEY to UNIQUE in the code for myself.

This was a terrible Idea and will probably cause you lots of errors later on

CREATE TABLE IF NOT EXISTS eventItems_stats ( Id BIGINT NOT NULL AUTO_INCREMENT , playername VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL , playerid VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL , amount INT NOT NULL , PRIMARY KEY (Id), UNIQUE KEY playerid (playerid)) ENGINE = InnoDB CHARSET=utf8 COLLATE utf8_bin

Basically you just change Id from NULL DEFAULT NULL to NOT NULL and AUTO_INCREMENT will take care of it. This query should work on most mysql servers... I assume you have it installed on windows because mariadb can work with it.

[Sacricx]

Yes, you are right, I have it installed on a Windows Server OS.

Alright, I will drop my current database, create a new one and use the provided query from you. I will replace it in the code aswell. What I did by replacing the PRIMARY KEY with UNIQUE then, shows that Im not quite much experienced with mysql yet and still learning.

For example: I didn't know that a primary key is necessary to ensure that a table record can always be uniquely identified etc. In this case "Id" is the column with the primary key in the "eventItems_stats" table and "playerid" is the column with the uniqe key that needs to related to "Id" in order to uniquely identify a player, right ?

If that's the case, I have another qestion: Once a new player is recorded into the "eventItems_stats" table, is the column "Id" from the "store_players" table used to give new players their id in the "eventItems_stats" table ?

If so, it would explain to me why it was a terrible idea to replace PRIMARY KEY with UNIQUE. My guess is that a new player would be given a new unique id in the "Id" column instead of using the already existing one from the store database. So when picking up an item, there could be a problem recieving the credits because of a conflict since the same SteamID (in the Store + EventItems DB) would relate to 2 diffrent id numbers.

If thats not the case, I have no idea, why it could cause me alot of errors later on.

Thank you very much for your responses and help :) !

best regards

Sacricx