CODEWITHSUNDEEP
mysql
CholoBoy
CholoBoy

Reputation: 159

Trouble Creating Foreign Key

I am trying to create a Foreign Key in order to link my 'Major ID' Column to my Major table.

This is my code: 

    CREATE TABLE students(
    `Student Number` INT NOT NULL PRIMARY KEY,
    `First Name` VARCHAR(255) NOT NULL,
    `Last Name` VARCHAR(255) NOT NULL,
    Address VARCHAR(255) NOT NULL,
    City VARCHAR(255) NOT NULL,
    State VARCHAR(255) NOT NULL,
    Zip INT NOT NULL,
    `Date of Birth` DATE NOT NULL,
    Major_ID INT,
    Advisor VARCHAR(255),
    `Enroll Date` DATE,
    `Total Credits Complete` INT,
    FOREIGN KEY(Major_ID) REFERENCES Major(Major_ID)
);

And the error code that I got is:

Error
SQL query:

CREATE TABLE students(
Student Number INT NOT NULL PRIMARY KEY,
First Name VARCHAR(255) NOT NULL,
Last Name VARCHAR(255) NOT NULL,
Address VARCHAR(255) NOT NULL,
City VARCHAR(255) NOT NULL,
State VARCHAR(255) NOT NULL,
Zip INT NOT NULL,
Date of Birth DATE NOT NULL,
Major_ID INT,
Advisor VARCHAR(255),
Enroll Date DATE,
Total Credits Complete INT,
FOREIGN KEY(Major_ID) REFERENCES Major(Major_ID) )
MySQL said:

*1005 - Can't create table cmp255.students (errno: 150 "Foreign key constraint is incorrectly formed") (Details…)


I used W3School as a reference on how to make a foreign key but for some reason, mine does not work.


I would really appreciate it if someone could let me know what I'm doing wrong. Thanks.


my Major Table Structure is 

create table Major(
   Major_ID int);

Upvotes: 1

Views: 304

Answers (2)

CholoBoy
CholoBoy

Reputation: 159

CREATE TABLE students(
    `Student Number` INT NOT NULL PRIMARY KEY,
    `First Name` VARCHAR(255) NOT NULL,
    `Last Name` VARCHAR(255) NOT NULL,
    Address VARCHAR(255) NOT NULL,
    City VARCHAR(255) NOT NULL,
    State VARCHAR(255) NOT NULL,
    Zip INT NOT NULL,
    `Date of Birth` DATE NOT NULL,
    `Major ID` INT REFERENCES Major(`Major ID`),
    Advisor VARCHAR(255),
    `Enroll Date` DATE,
    `Total Credits Complete` INT
);

Upvotes: 0

MontyPython
MontyPython

Reputation: 2994

When defining a foreign key, there are a few things that you need to take care of in MySQL (and almost any other relational database) of which I am listing the most frequent ones that I have come across in the past couple of years.

  1. The size and sign of integer types must be the same. The length of string types need not be the same.

  2. Character set conversion is not permitted on tables that include a character string column used in a foreign key constraint - this is when foreign key checks are enabled

  3. Parent and child table (referencing and referenced table) should have the same storage engine.

I have listed these from the MySQL official documentation. You can read more about constraints on Foreign Keys here - https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html

Upvotes: 0

Related Questions