I restart a project of mine which use mysql, I used xampp in the past to test the code on the mysql server so I update to lasted version but a piece of code that is identical and was working, I now get an error in the class check in line

CREATE TABLE user(
	idu INTEGER PRIMARY KEY AUTO_INCREMENT,
	name CHAR(40) NOT NULL,
	email CHAR(50) NOT NULL,
	passwordd CHAR(60) NOT NULL,
	nickname CHAR(20) NOT NULL UNIQUE,
	country CHAR(100) NOT NULL
)
CREATE TABLE test(
	id INTEGER AUTO_INCREMENT,
	iduser INTEGER REFERENCES player(id) ON UPDATE CASCADE ON DELETE CASCADE,
	...
	[U]class CHAR(10) NOT NULL CHECK class IN('x1','x2','x3'),[/U]
	...
        ...
	PRIMARY KEY(id,iduser),
	FOREIGN KEY (iduser) REFERENCES user(idu)
);

Error On xampp says:
"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'class IN('x1','x2','x3')"

I don't know what was the previous collation I used , I'm now using the utf8_bin could this be associated with this?

I wasnt able to identify why this is happening , can any1 help with this ?


Best regards
ArturM

Ok I have looked around for something that might help but the only think I can see is I believe there should be a space after the IN

class CHAR(10) NOT NULL CHECK class IN ('x1','x2','x3'),

I tried with and without space still same error , can you execute that piece of code?

I have added constraint at the end.

CREATE TABLE test(
	id INTEGER AUTO_INCREMENT,
	iduser INTEGER REFERENCES player(id) ON UPDATE CASCADE ON DELETE CASCADE,
.
.
class CHAR(10) NOT NULL ,

.
.
PRIMARY KEY(id,iduser),
FOREIGN KEY (iduser) REFERENCES user(idu),
CONSTRAINT chk_class CHECK (class IN ('x1','x2','x3'))
);

I never used this kind of constraint. For this purpose I'd rather use an enum field type for the class column.

I have added constraint at the end.

CREATE TABLE test(
	id INTEGER AUTO_INCREMENT,
	iduser INTEGER REFERENCES player(id) ON UPDATE CASCADE ON DELETE CASCADE,
.
.
class CHAR(10) NOT NULL ,

.
.
PRIMARY KEY(id,iduser),
FOREIGN KEY (iduser) REFERENCES user(idu),
CONSTRAINT chk_class CHECK (class IN ('x1','x2','x3'))
);

I didnt manage to get it working with constraint but I did this and its now working:

class CHAR(10) NOT NULL CHECK (class IN('x1','x2','x3')),

Thank you anyways

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.