1

MySQL:

CREATE SCHEMA IF NOT EXISTS otes_db;

USE otes_db;

CREATE TABLE IF NOT EXISTS school(
    school_code CHAR(3),
    school_desc VARCHAR(255) NOT NULL,
    school_head INT UNSIGNED NOT NULL,  
    PRIMARY KEY(school_code),
    FOREIGN KEY(school_head) REFERENCES user(user_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS user(
    user_id INT UNSIGNED,   
    user_lname VARCHAR(255) NOT NULL,
    user_fname VARCHAR(255) NOT NULL,
    user_mname VARCHAR(255),
    user_email VARCHAR(255) UNIQUE NOT NULL,
    user_password VARCHAR(255) NOT NULL,
    user_type ENUM('CEO', 'VP', 'HEAD', 'CHAIR', 'STUD') NOT NULL,
    user_isActivated ENUM('Y', 'N') DEFAULT 'N',
    PRIMARY KEY(user_id)
);

Based on this thread, I have checked for the following:

  • InnoDB Engine is set in school table
  • The referenced key is a primary key (user_id)
  • The data types are the same (INT UNSIGNED)

However, I'm still getting the error with the school_head and user_id. Anything I missed?

Community
  • 1
  • 1
k_rollo
  • 5,304
  • 16
  • 63
  • 95
  • 2
    You need to create the table `user` *before* you create the table `school`. Plus `user` is a reserved keyword, you need to quote it (better find a different name, that is not a reserved word) –  Dec 21 '14 at 10:04
  • If you could make this an answer, I will upvote and accept it. Thanks also for the tip about `user` (that's why it always highlights as blue). – k_rollo Dec 21 '14 at 10:12

1 Answers1

0

You need to create the user table first

dan b
  • 1,172
  • 8
  • 20