CODEWITHSUNDEEP
mysqlsqlsql-server
terrible programming
terrible programming

Reputation: 49

SQL - many to many table wont work

#1005 - Can't create table 'ass1 lol.management' (errno: 150) (Details...)

i'm having the above error returned when i try create this many to many table

trying to create a many to many table between employees and supervisors, not error is returned when i make the supervisor and employee tables. but when i go to create my management table i get the above error.

these are my create table statements

employee table

CREATE TABLE IF NOT EXISTS employee (

    emp_id INT(4) NOT NULL,
    yr_ser INT(4),
    status VARCHAR(30),
    emp_name VARCHAR(30),
    emp_gen VARCHAR(1),
    PRIMARY KEY (emp_id)
);

supervisor Table

CREATE TABLE IF NOT EXISTS supervisor (
    sup_id INT(4) NOT NULL,
    sup_name VARCHAR(30) NOT NULL,
    gen INT(4),
    dep_id INT(4),

    PRIMARY KEY (sup_id),
    INDEX (dep_id),

    FOREIGN KEY (dep_id)
        REFERENCES department(dep_id)
        ON UPDATE CASCADE ON DELETE RESTRICT    
);

but then when i go to create this table i get the error

management Table

CREATE TABLE IF NOT EXISTS management (
    emp_id INT(4) NOT NULL,
    sup_id INT(4) NOT NULL,

    PRIMARY KEY (emp_id, sup_id),
    INDEX (emp_id),
    INDEX (sup_id),

    FOREIGN KEY (emp_id)
        REFERENCES employee(emp_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,

    FOREIGN KEY (sup_id)
        REFERENCES supervisor(act_id)
        ON UPDATE CASCADE ON DELETE RESTRICT
);

help would be appreciated, thank you.

Upvotes: 2

Views: 55

Answers (1)

Robby Cornelissen
Robby Cornelissen

Reputation: 97140

Your may-to-many table references a field act_id in the supervisor table, but no such field is defined. Should be sup_id instead:

CREATE TABLE IF NOT EXISTS management (
emp_id INT(4) NOT NULL,
sup_id INT(4) NOT NULL,

PRIMARY KEY (emp_id, sup_id),
INDEX (emp_id),
INDEX (sup_id),

FOREIGN KEY (emp_id)
    REFERENCES employee(emp_id)
    ON UPDATE CASCADE ON DELETE RESTRICT,

FOREIGN KEY (sup_id)
    REFERENCES supervisor(sup_id) -- Here
    ON UPDATE CASCADE ON DELETE RESTRICT
);

Upvotes: 1

Related Questions