[DarkLored]

So I created a database but one of my columns cannot be assigned 'NOT NULL' for some reason, I have searched the entire internet, removing the 'NOT NULL' seems to fix the issue but then it makes the column null even if I change it using INSERT or UPDATE.

Here's the database

pawn Код:

LoadDatabase()
{
    new Query[2000];
    Database = db_open("scrp.db");
    strcat(Query, "CREATE TABLE IF NOT EXISTS users (userid INTEGER PRIMARY KEY AUTOINCREMENT, username VARCHAR(24) COLLATE NOCASE, password VARCHAR(129), level INTEGER DEFAULT 1 NOT NULL, money INTEGER DEFAULT 5000 NOT NULL,", sizeof(Query));
    strcat(Query, "faction INTEGER DEFAULT 0 NOT NULL, factionrank INTEGER DEFAULT 0 NOT NULL, bank INTEGER DEFAULT 10000 NOT NULL, savings INTEGER DEFAULT 0 NOT NULL, savingson INTEGER DEFAULT 0 NOT NULL, skin INTEGER DEFAULT 0 NOT NULL,", sizeof(Query));
    strcat(Query, "toy1 INTEGER DEFAULT 0 NOT NULL, toy2 INTEGER DEFAULT 0 NOT NULL, toy3 INTEGER DEFAULT 0 NOT NULL, toy4 INTEGER DEFAULT 0 NOT NULL, toy5 INTEGER DEFAULT 0 NOT NULL, toy6 INTEGER DEFAULT 0 NOT NULL, toy7 INTEGER DEFAULT 0 NOT NULL,", sizeof(Query));
    strcat(Query, "toy8 INTEGER DEFAULT 0 NOT NULL, toy9 INTEGER DEFAULT 0 NOT NULL, toy10 INTEGER DEFAULT 0 NOT NULL, weapon1 INTEGER DEFAULT 0 NOT NULL, ammo1 INTEGER DEFAULT 0 NOT NULL, weapon2 INTEGER DEFAULT 0 NOT NULL, ammo2 INTEGER DEFAULT 0 NOT NULL,", sizeof(Query));
    strcat(Query, "marijuana INTEGER DEFAULT 0 NOT NULL, meth INTEGER DEFAULT 0 NOT NULL, crack INTEGER DEFAULT 0 NOT NULL, cocaine INTEGER DEFAULT 0 NOT NULL, heroin INTEGER DEFAULT 0 NOT NULL, shrooms INTEGER DEFAULT 0 NOT NULL, keys INTEGER DEFAULT 0 NOT NULL,", sizeof(Query));
    strcat(Query, "job INTEGER DEFAULT 0 NOT NULL, hours INTEGER DEFAULT 0 NOT NULL, paycheck INTEGER DEFAULT 0 NOT NULL, interior INTEGER DEFAULT 0 NOT NULL, vw INTEGER DEFAULT 0 NOT NULL, posx FLOAT DEFAULT 0.0 NOT NULL, posy FLOAT DEFAULT 0.0 NOT NULL, posz FLOAT DEFAULT 0.0 NOT NULL,", sizeof(Query));
    strcat(Query, "posa FLOAT DEFAULT 0.0 NOT NULL, admin INTEGER DEFAULT 0 NOT NULL, tester INTEGER DEFAULT 0 NOT NULL, upgradep INTEGER DEFAULT 0 NOT NULL, health FLOAT DEFAULT 100.0 NOT NULL, armor FLOAT DEFAULT 0.0 NOT NULL, duty INTEGER DEFAULT 0 NOT NULL, vip INTEGER DEFAULT 0 NOT NULL,", sizeof(Query));
    strcat(Query, "isswat INTEGER DEFAULT 0 NOT NULL, isdetective INTEGER DEFAULT 0 NOT NULL, age INTEGER DEFAULT 0 NOT NULL, origloc VARCHAR(30) NOT NULL)", sizeof(Query));
    db_query(Database, Query);
    print(Query);
}

When I execute this in the database it says the constraint error.

pawn Код:

INSERT INTO `users` (`username`,`password`) VALUES ('%s','%s')

Error itself

pawn Код:

NOT NULL constraint failed: users.origloc: INSERT INTO `users` (`username`,`password`) VALUES ('%s','%s')

[Vince]

Please normalize. If I had to design this data structure it would look somewhat like this (see attachment). There's still too many columns in the User table to my liking but I've managed to relocate at least 22 columns to other tables.



The added benefits:

• A user can be a member of unlimited factions.
• Player can own unlimited toys.(*)
• Player can own unlimited weapons.(*)
• You can store unlimited types of drugs.
• Player can own unlimited types of drugs.

(*) yes I know there are only 10 attachment slots and 13 weapon slots, but that doesn't mean that you should create new columns. If new attachment slots get added later you don't need to change your database at all.

[Biesmen]

I do highly recommend you to normalize your database, but it won't solve the problem..

Your column "origloc" doesn't have a default value, so it's NULL at this moment.

[DarkLored]

Quote:

Originally Posted by Biesmen I do highly recommend you to normalize your database, but it won't solve the problem.. Your column "origloc" doesn't have a default value, so it's NULL at this moment.

But origloc is a string which I have specified in the database to not be null, none of my other databases experience this issue other than this one. I had this issue before but couldn't figure out what causes it.

Here's a database that works.

pawn Код:

stock LoadDatabase()
{
    new Query[4000];
    Database = db_open("scrp.db");
    strcat(Query, "CREATE TABLE IF NOT EXISTS users (userid INTEGER PRIMARY KEY AUTOINCREMENT, username VARCHAR(24) COLLATE NOCASE, password VARCHAR(129), score INTEGER DEFAULT 0 NOT NULL,", sizeof(Query));
    strcat(Query, "money INTEGER DEFAULT 500 NOT NULL, admin INTEGER DEFAULT 0 NOT NULL, vip INTEGER DEFAULT 0 NOT NULL, kills INTEGER DEFAULT 0 NOT NULL, deaths INTEGER DEFAULT 0 NOT NULL,", sizeof(Query));
    strcat(Query, "passedquiz INTEGER DEFAULT 0 NOT NULL, skin INTEGER DEFAULT 0 NOT NULL, age VARCHAR(200), birthplace VARCHAR(100) , race INTEGER DEFAULT 0 NOT NULL, culture VARCHAR(100),", sizeof(Query));
    strcat(Query, "userposx FLOAT DEFAULT 0.0 NOT NULL, userposy FLOAT DEFAULT 0.0 NOT NULL, userposz FLOAT DEFAULT 0.0 NOT NULL,", sizeof(Query));
    strcat(Query, "userrota FLOAT DEFAULT 0.0 NOT NULL, faction INTEGER DEFAULT 0 NOT NULL, factionrank INTEGER DEFAULT 0 NOT NULL,", sizeof(Query));
    strcat(Query, "interior INTEGER DEFAULT 0 NOT NULL, vw INTEGER DEFAULT 0 NOT NULL, bankcash INTEGER DEFAULT 2000 NOT NULL, playinghours INTEGER DEFAULT 0 NOT NULL,", sizeof(Query));
    strcat(Query, "paycheck INTEGER DEFAULT 0 NOT NULL, savings INTEGER DEFAULT 0 NOT NULL )", sizeof(Query));
    db_query(Database, Query);
}

@Vince by any chance is there a max amount of columns you can have per table? I am also having trouble normalizing the database, I don't really understand how it works.

I tried following your tutorial and another SQLite Foreign Key support to understand the process and made this up.

pawn Код:

LoadDatabase()
{
    new Query[2000];
    Database = db_open("scrp.db");
    strcat(Query, "CREATE TABLE IF NOT EXISTS users (userid INTEGER PRIMARY KEY AUTOINCREMENT, username VARCHAR(24) COLLATE NOCASE, password VARCHAR(129), level INTEGER DEFAULT 1 NOT NULL, money INTEGER DEFAULT 5000 NOT NULL,", sizeof(Query));
    strcat(Query, "bank INTEGER DEFAULT 10000 NOT NULL, savings INTEGER DEFAULT 0 NOT NULL, savingson INTEGER DEFAULT 0 NOT NULL, skin INTEGER DEFAULT 0 NOT NULL, keys INTEGER DEFAULT 0 NOT NULL,", sizeof(Query));
    strcat(Query, "job INTEGER DEFAULT 0 NOT NULL, hours INTEGER DEFAULT 0 NOT NULL, paycheck INTEGER DEFAULT 0 NOT NULL, interior INTEGER DEFAULT 0 NOT NULL, vw INTEGER DEFAULT 0 NOT NULL, posx FLOAT DEFAULT 0.0 NOT NULL, posy FLOAT DEFAULT 0.0 NOT NULL, posz FLOAT DEFAULT 0.0 NOT NULL,", sizeof(Query));
    strcat(Query, "posa FLOAT DEFAULT 0.0 NOT NULL, admin INTEGER DEFAULT 0 NOT NULL, tester INTEGER DEFAULT 0 NOT NULL, upgradep INTEGER DEFAULT 0 NOT NULL, health FLOAT DEFAULT 100.0 NOT NULL, armor FLOAT DEFAULT 0.0 NOT NULL, duty INTEGER DEFAULT 0 NOT NULL, vip INTEGER DEFAULT 0 NOT NULL,", sizeof(Query));
    strcat(Query, "isswat INTEGER DEFAULT 0 NOT NULL, isdetective INTEGER DEFAULT 0 NOT NULL, age INTEGER DEFAULT 0 NOT NULL, origin VARCHAR(30) NOT NULL)", sizeof(Query));
    strcat(Query, "CREATE TABLE IF NOT EXISTS FactionMember (userid INTEGER, factionid INTEGER, rank INTEGER, FOREIGN KEY(userid) REFERENCES users(userid) ON UPDATE CASCADE ON DELETE CASCADE)", sizeof(Query));
    db_query(Database, Query);
    print(Query);
}

Would this new database workout? could you explain how the factionid and rank updates if the only reference is the player id?

[Biesmen]

Quote:

Originally Posted by DarkLored But origloc is a string which I have specified in the database to not be null, none of my other databases experience this issue other than this one. I had this issue before but couldn't figure out what causes it.

You have indeed specified column "origicloc" not to be null. That's why you get this error. MySQL is basically telling you there's a column which is not allowed to be null. But you're basically entering 'null' as content/data for that column. You'll have to give that column a default value, or enter a value for that column in the query, or you'll have remove the NOT NULL constraint.

Replacing

Код:

origloc VARCHAR(30) NOT NULL

with

Код:

origloc VARCHAR(30) NOT NULL DEFAULT ''

will do the trick, or you'll have to enter a value for that column in the query:

Код:

INSERT INTO `users` (`username`,`password`,`origloc`) VALUES ('%s','%s','any content here')

[DarkLored]

Quote:

Originally Posted by Biesmen You have indeed specified column "origicloc" not to be null. That's why you get this error. MySQL is basically telling you there's a column which is not allowed to be null. But you're basically entering 'null' as content/data for that column. You'll have to give that column a default value, or enter a value for that column in the query, or you'll have remove the NOT NULL constraint. Replacing Код: origloc VARCHAR(30) NOT NULL with Код: origloc VARCHAR(30) NOT NULL DEFAULT '' will do the trick, or you'll have to enter a value for that column in the query: Код: INSERT INTO `users` (`username`,`password`,`origloc`) VALUES ('%s','%s','any content here')

Yep that fixed the issue that I had, thanks a lot man. I use SQLite btw but there is not much difference I suppose. +REP.