SQL Errors

[mattclements]

I am working on a SQL Database, I was going to attempt to run this in Microsoft Access... but because of the errors im going to use MySQL... This SQL flags an error:

 

/* Remove all old Tables that may cause an error */ 
DROP TABLE behaviour_code;
DROP TABLE behaviour_log;
DROP TABLE teacher;
DROP TABLE student;

/* Create All Tables */
/* Create Behaviour Code Table */ 
CREATE TABLE behaviour_code (
  `code` VARCHAR(6) NOT NULL,
  `name` TEXT(50) NOT NULL,
  `description` LONGTEXT,
  PRIMARY KEY (`code`)
)
ENGINE = InnoDB;

/* Create Student Table */
CREATE TABLE student (
  `id` INTEGER(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  `first_name` TEXT(75) NOT NULL,
  `last_name` TEXT(75) NOT NULL,
  `form` VARCHAR(5) NOT NULL,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

/* Create Teacher Table */
CREATE TABLE teacher (
  `id` INTEGER(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` TEXT(150) NOT NULL,
  `form` VARCHAR(5),
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

/* Create Behaviour Log Table */
CREATE TABLE behaviour_log (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `date` DATETIME NOT NULL,
  `code` VARCHAR(45) NOT NULL,
  `student` VARCHAR(45) NOT NULL,
  `teacher` VARCHAR(45) NOT NULL,
  `description` LONGTEXT,
  `action_taken` LONGTEXT,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

/* Create Behaviour Log Relationships */
ALTER TABLE behaviour_log 
  ADD Foreign Key (student) references teacher(id),
  ADD Foreign Key (teacher) references student(id),
  ADD Foreign Key (code) references behaviour_code(code);

 

When running this in phpMyAdmin shows:

SQL query:

 

/* Create Behaviour Log Relationships */ ALTER TABLE behaviour_log ADD FOREIGN KEY ( student ) REFERENCES teacher( id ) ,

ADD FOREIGN KEY ( teacher ) REFERENCES student( id ) ,

ADD FOREIGN KEY ( code ) REFERENCES behaviour_code( code ) ;

 

 

 

MySQL said: 

 

#1005 - Can't create table './unit13/#sql-656_2fa9.frm' (errno: 150)

 

Any ideas people? Also how compatible would this SQL be with Microsoft SQL etc?

Cheers

Matt

[fenway]

This usually means the columns aren't exactly the same.

[mattclements]

Ok... that would make sense, but I have now done that, so the SQL is as follows:

 

/* Remove all old Tables that may cause an error */ 
DROP TABLE behaviour_code;
DROP TABLE behaviour_log;
DROP TABLE teacher;
DROP TABLE student;

/* Create All Tables */
/* Create Behaviour Code Table */ 
CREATE TABLE behaviour_code (
  `code` VARCHAR(6) NOT NULL,
  `name` TEXT(50) NOT NULL,
  `description` LONGTEXT,
  PRIMARY KEY (`code`)
)
ENGINE = InnoDB;

/* Create Student Table */
CREATE TABLE student (
  `id` INTEGER(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  `first_name` TEXT(75) NOT NULL,
  `last_name` TEXT(75) NOT NULL,
  `form` VARCHAR(5) NOT NULL,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

/* Create Teacher Table */
CREATE TABLE teacher (
  `id` INTEGER(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` TEXT(150) NOT NULL,
  `form` VARCHAR(5),
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

/* Create Behaviour Log Table */
CREATE TABLE behaviour_log (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `date` DATETIME NOT NULL,
  `code` VARCHAR(6) NOT NULL,
  `student` INTEGER(3) NOT NULL,
  `teacher` INTEGER(2) NOT NULL,
  `description` LONGTEXT,
  `action_taken` LONGTEXT,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

/* Create Behaviour Log Relationships */
ALTER TABLE behaviour_log
  ADD Foreign Key (student) references teacher(id),
  ADD Foreign Key (teacher) references student(id),
  ADD Foreign Key (code) references behaviour_code(code);

 

and I now get:

SQL query:

 

/* Create Behaviour Log Relationships */ ALTER TABLE behaviour_log ADD FOREIGN KEY ( student ) REFERENCES teacher( id ) ,

ADD FOREIGN KEY ( teacher ) REFERENCES student( id ) ,

ADD FOREIGN KEY ( code ) REFERENCES behaviour_code( code ) ;

 

 

 

MySQL said: 

 

#1005 - Can't create table './unit13/#sql-656_30ed.frm' (errno: 150)

 

Im really confused now  ???

[mattclements]

Don't worry - I think it's something to do with my MySQL install on Linux.

 

Cheers,

Matt 

[fenway]

As I recall, this may be due to different data type in a FK definition.

[aschk]

Look at the columns you're creating, you're trying to add a foreign key from a INT(3) to an INT(2) in another table. This is where you errors are occuring

e.g.

ADD FOREIGN KEY ( student ) REFERENCES teacher( id )

 

student is INT(3) and teacher.id is INT(2).

 

Also, regarding your SQL-92 compliance, ENGINE= is NOT standard and NEITHER is auto_increment