Search code examples
mysqlphpmyadmincreate-table

Why won't MySQL statement work?

I am using PHPMyAdmin right now and I am creating a new table with these values below but it's not working and I can't see why at all.

SQL query:

CREATE TABLE  `database`.`hub_attendance_lessons` (

`id` BIGINT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`lesson_id` BIGINT( 10 ) UNSIGNED NOT NULL ,
`course_id` BIGINT( 10 ) UNSIGNED NOT NULL ,
`student_id` BIGINT( 10 ) UNSIGNED NOT NULL ,
`date` BIGINT( 10 ) UNSIGNED NOT NULL ,
`attended` BOOL( 2 ) UNSIGNED NULL ,
`absent` BOOL( 2 ) UNSIGNED NULL ,
`excused_absent` BOOL( 2 ) UNSIGNED NULL ,
`late` BOOL( 2 ) UNSIGNED NULL ,
`excused_late` BOOL( 2 ) UNSIGNED NULL
)
 ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci 
 COMMENT =  'stores the attendance of all lessons for all students';

MySQL said:

#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 '(2) UNSIGNED NULL, absent BOOL(2) UNSIGNED NULL, `excused_absent` BOOL(2) UNSI' at line 1

Solution

  • BOOL and BOOLEAN are just shorthand for TINYINT(1). It makes no sense to have a BOOL(2). Remove all lengths of 2 for your booleans.