Why can't I add a foreign key constraint on this?

Question

    CREATE TABLE usertypes (
        userType INTEGER, 
        userName VARCHAR(12),
        PRIMARY KEY(userType)
    );   

    CREATE TABLE Users (
        loginid CHAR(9),
        name VARCHAR(15),
        netid VARCHAR(15) NOT NULL,
        password VARCHAR(15) NOT NULL,
        userType INTEGER,
        PRIMARY KEY (loginid),
        UNIQUE (netid),
        FOREIGN KEY (userType) REFERENCES usertypes(userType)
            ON DELETE NO ACTION
    );         

    CREATE TABLE Courses (
        majorid CHAR(3), 
        cid CHAR (3),
        secNum CHAR(2),
        year CHAR(4),
        semesterid CHAR(2),
        profID CHAR(9),
        PRIMARY KEY (majorid,cid,secNum,year,semesterid),
        FOREIGN KEY (majorid) REFERENCES Majors (majorid),
        FOREIGN KEY (profID) REFERENCES Users(loginid)
    );

    create table transcript(
        cid char(3),
        grade char(2),
        primary key(cid, grade),
        foreign key(cid) references courses(cid)
    );

I can't add a foreign key cid in transcript. The foreign key for majorid in courses works fine but the one in transcript doesn't work.

Here's the error Error Code: 1215. Cannot add foreign key constraint

You're not describing the `Majors` or `Users` tables here.. Please update your question with the definitions of those tables. Quick hint, stop using `CHAR` as a type to reference on. — Kermit, Aug 04 '14 at 01:20
Definitely not. Integer. There's a higher cost of performing join's, or a lot of operations, on string data. — Kermit, Aug 04 '14 at 01:31
Okay, I'll keep that in mind but for future projects. Thanks — minionhacking, Aug 04 '14 at 01:32

score 1 · Accepted Answer · answered Aug 04 '14 at 01:29

Course.cid is not a key, so transcript cannot create a foreign key. Try the following:

CREATE TABLE Courses (
        majorid CHAR(3), 
        cid CHAR (3),
        secNum CHAR(2),
        year CHAR(4),
        semesterid CHAR(2),
        profID CHAR(9),
        PRIMARY KEY (majorid,cid,secNum,year,semesterid),
        KEY (cid)
    );

score 1 · Answer 2 · edited May 23 '17 at 11:57

Short version is I don't think you can have a foreign key to something that isn't identified as a primary key or something with a unique constraint, see Foreign Key to non-primary key

I'm not sure how you are using the cid in the Courses table, if it is perhaps 110 and there is a Math 110 and a Physics 110. This has the problem of if someone has 110 on their transcript, does it reference Math or Physics?

If cid is a primary key, a unique value for each class, it should be a primary key all by itself. If you are in the situation of 110 for math and physics, you might be best served by adding a primary key that is unique for every row, such as an identity, auto incrementing key.

Why can't I add a foreign key constraint on this?

2 Answers2