[SQL]Foreign Key Error - Printable Version

[SQL]Foreign Key Error - 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: [SQL]Foreign Key Error (/showthread.php?tid=593691)

[SQL]Foreign Key Error - Sasino97 - 08.11.2015

Hello people, when executing these queries I'm having an issue:

pawn Code:

stock DB_UserInit()
{
    new query[2048];
    
    strcat(query, "CREATE TABLE IF NOT EXISTS "TABLE_USERS" (\n");
    strcat(query, "Username VARCHAR(24) NOT NULL DEFAULT ' ',\n");
    strcat(query, "Password CHAR(65) NOT NULL DEFAULT ' ',\n");
    strcat(query, "IP CHAR(16) NOT NULL DEFAULT '0.0.0.0',\n");
    strcat(query, "Exp INT(5) NOT NULL DEFAULT '0',\n");
    strcat(query, "Level INT(5) NOT NULL DEFAULT '0',\n");
    strcat(query, "VIP BOOLEAN NOT NULL DEFAULT '0',\n");
    strcat(query, "Admin INT(4) NOT NULL DEFAULT '0',\n");
    strcat(query, "Disabled BOOLEAN NOT NULL DEFAULT '0',\n");
    strcat(query, "AutoLogin BOOLEAN NOT NULL DEFAULT '0',\n");
    strcat(query, "RegisterDate DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00'\n,");
    strcat(query, "LoginDate DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00',\n");
    strcat(query, "CONSTRAINT PRIMARY KEY(Username)) ENGINE=InnoDB ;");
    
    mysql_tquery(MySQL, query, "OnQueryFinished", "dd", 0, 0);
    format(query, 2048, "");
    
    strcat(query, "CREATE TABLE IF NOT EXISTS "TABLE_CHARACTERS" (\n");
    strcat(query, "ID INT(12) NOT NULL AUTO_INCREMENT,\n");
    strcat(query, "Username VARCHAR(24) UNIQUE NOT NULL,\n");
    strcat(query, "FirstName VARCHAR(24) NOT NULL DEFAULT ' ',\n");
    strcat(query, "LastName VARCHAR(24) NOT NULL DEFAULT ' ',\n");
    strcat(query, "Cash INT(12) NOT NULL DEFAULT '0',\n");
    strcat(query, "Bank INT(12) NOT NULL DEFAULT '0',\n");
    strcat(query, "Skin INT(4) NOT NULL DEFAULT '0',\n");
    strcat(query, "Sex BOOLEAN NOT NULL DEFAULT '0',\n");
    strcat(query, "Age INT(4) NOT NULL DEFAULT '0',\n");
    strcat(query, "Health DECIMAL(6,2) NOT NULL DEFAULT '100.0',\n");
    strcat(query, "Armor DECIMAL(6,2) NOT NULL DEFAULT '0.0',\n");
    strcat(query, "Energy DECIMAL(6,2) NOT NULL DEFAULT '100.0',\n");
    strcat(query, "PhoneNumber INT(6) NOT NULL DEFAULT '0',\n");
    strcat(query, "DrivingLicense BOOLEAN NOT NULL DEFAULT '0',\n");
    strcat(query, "FlyingLicense BOOLEAN NOT NULL DEFAULT '0',\n");
    strcat(query, "GunPermit BOOLEAN NOT NULL DEFAULT '0',\n");
    strcat(query, "HuntPermit BOOLEAN NOT NULL DEFAULT '0',\n");
    strcat(query, "FishPermit BOOLEAN NOT NULL DEFAULT '0',\n");
    strcat(query, "Wanted INT(2) NOT NULL DEFAULT '0',\n");
    strcat(query, "Fine INT(12) NOT NULL DEFAULT '0',\n");
    strcat(query, "Jail BOOLEAN NOT NULL DEFAULT '0',\n");
    strcat(query, "Cell INT(4) NOT NULL DEFAULT '0',\n");
    strcat(query, "Bail INT(12) NOT NULL DEFAULT '0',\n");
    strcat(query, "Phone INT(4) NOT NULL DEFAULT '0',\n");
    strcat(query, "CONSTRAINT PRIMARY KEY(ID),\n");
    strcat(query, "CONSTRAINT FOREIGN KEY(Username) REFERENCES "TABLE_USERS"(Username)\nON DELETE SET NULL\nON UPDATE CASCADE\n) ENGINE=InnoDB ;");

    mysql_tquery(MySQL, query, "OnQueryFinished", "dd", 0, 0);
    return 1;
}

The users table is succesfully created, while the characters table throws out an error.

pawn Code:

public OnQueryError(errorid, error[], callback[], query[], connectionHandle)
{
    printf("***MySQL ERROR!\n\tError no.%d - %s\n\tCallback: %s\n\tQuery: %s", errorid, error, callback, query);
    return 1;
}

Code:

[14:01:06] ***MySQL ERROR!
	Error no.1215 - Impossible d'ajouter des contraintes d'index externe
	Callback: OnQueryFinished
	Query: CREATE TABLE IF NOT EXISTS characters (
ID INT(12) NOT NULL AUTO_INCREMENT,
Username VARCHAR(24) UNIQUE NOT NULL,
FirstName VARCHAR(24) NOT NULL DEFAULT ' ',
LastName VARCHAR(24) NOT NULL DEFAULT ' ',
Cash INT(12) NOT NULL DEFAULT '0',
Bank INT(12) NOT NULL DEFAULT '0',
Skin INT(4) NOT NULL DEFAULT '0',
Sex BOOLEAN NOT NULL DEFAULT '0',
Age INT(4) NOT NULL DEFAULT '0',
Health DECIMAL(6,2) NOT NULL DEFAULT '100.0',
Armor DECIMAL(6,2) NOT NULL DEFAULT '0.0',
Energy DECIMAL(6,2) NOT NULL DEFAULT '100.0',
PhoneNumber INT(6) NOT NULL DEFAULT '0',
DrivingLicense BOOLEAN NOT NULL DEFAULT '0',
FlyingLicense BOOLEAN NOT NULL DEFAULT '0',
GunPermit BOOLEAN NOT NULL DEFAULT '0',
HuntPermit BOOLEAN NOT NULL DEFAULT '0',
FishPermit BOOLEAN NOT NULL DEFAULT '0',
Wanted INT(2) NOT NULL DEFAULT '0',
Fine INT(12) NOT NULL DEFAULT '0',
Jail BOOLEAN NOT NULL DEFAULT '0',
Cell INT(4) NOT NULL DEFAULT '0',
Bail INT(12) NOT NULL DEFAULT '0',
Phone INT(4) NOT NULL DEFAULT '0',
CONSTRAINT PRIMARY KEY(ID),
CONSTRAINT FOREIGN KEY(Username) REFERENCES users(Username)
ON DELETE SET NULL
ON UPDATE CASCADE
) ENGINE=InnoDB ;

(Note: I don't know why the error message is french, as I'm not french), but I guess it means that it couldn't add the foreign key

Re: [SQL]Foreign Key Error - ikey07 - 08.11.2015

Table creation do in the phpmyadmin, go to your database, click SQL at the top and paste in this query

CREATE TABLE IF NOT EXISTS `TABLE_USERS` ( Username VARCHAR(24) NOT NULL DEFAULT 'None', Password CHAR(65) NOT NULL DEFAULT 'None',IP CHAR(16) NOT NULL DEFAULT '0.0.0.0',Exp INT(5) NOT NULL DEFAULT '0',Level INT(5) NOT NULL DEFAULT '0',VIP BOOLEAN NOT NULL DEFAULT '0',Admin INT(4) NOT NULL DEFAULT '0',Disabled BOOLEAN NOT NULL DEFAULT '0',AutoLogin BOOLEAN NOT NULL DEFAULT '0',RegisterDate DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00', LoginDate DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00', CONSTRAINT PRIMARY KEY(Username)) ENGINE=InnoDB ;

In the same manner do the rest of the tables.

Re: [SQL]Foreign Key Error - Sasino97 - 08.11.2015

Hello, thanks for the reply.
I'm sorry but I don't want to create the tables manually, I want the sa-mp server to do it (so for example, if in the future I want to release this script, it will auto-create tables).
Also, the problem is not that, because the user table was succesfully created by the pawn script.

Re: [SQL]Foreign Key Error - PrO.GameR - 08.11.2015

Username VARCHAR(24) UNIQUE NOT NULL,

^^ then you are asking it to change Username to NULL if the reference gets deleted, I've played with these referencing options for a decent amount of time while I was trying to learn mysql, I found out they usually have to be exactly the same for them to be able to reference them together.

Re: [SQL]Foreign Key Error - Vince - 08.11.2015

Quote:

Originally Posted by Sasino97

I'm sorry but I don't want to create the tables manually, I want the sa-mp server to do it

Doesn't matter. Supply an .sql file with the needed statements. If you want to get really fancy you can even code a batch/shell script to execute it.

But anyway: your constraint says: ON DELETE SET NULL. Yet you have declared the field to be "NOT NULL". Hence, error. Also the primary key should never have any intrinsic value. Use a unique id instead and avoid copying data.