mysql foreign key reference from a composite key

Question

I am creating an application that will handle and record when a student gets advised by a faculty member at a university and I need an effective way to structure the tables. My problem is coming from a lack of referential integrity, caused by the inability to create a foreign key that will reference a STUDENT on only part of a composite key in ADVISE_HIST which is (STUDENT_ID,DATE_ADVISED)

here are some of the tables

create table STUDENT(
  LNAME varchar(50),
  FNAME varchar(50),
  ID char(9) primary key,
  ASSIGNED_ADVISOR_EMAIL varchar(70),
  foreign key (ASSIGNED_ADVISOR_EMAIL) references DEP_FACULTY(EMAIL) ON DELETE SET NULL,
  IS_ADVISED tinyint(1),
);

create table DEP_FACULTY(
  LNAME varchar(50),
  FNAME varchar(50),
  EMAIL varchar(70) primary key
);

create table ADVISE_HIST(
  STUDENT_ID char(9),
  /*foreign key (STUDENT_ID) references STUDENT(ID),*/
  ACTUAL_ADVISOR_EMAIL char(70) NOT NULL,
  foreign key (ACTUAL_ADVISOR_EMAIL) references DEP_FACULTY(EMAIL),
  DATE_ADVISED date,
  primary key REF_ADVISE_HIST (STUDENT_ID, DATE_ADVISED),
);

My question is, is there a way around not being able to create this key or is there a better structure that I'm not thinking of?

You can't reference the `ID` column in your `STUDENT` table because it doesn't exist! Did you mean to reference `ENUMBER`? — eggyal, Apr 25 '12 at 18:14
If you now attempt to add the foreign key constraint (`ALTER TABLE ADVISE_HIST ADD CONSTRAINT FOREIGN KEY (STUDENT_ID) REFERENCES STUDENT(ID);`), what error do you get? — eggyal, Apr 25 '12 at 18:18
it said the table was altered, but the constraint still isn't there. doesn't surprise me coming from MySQL. — Daniel Wilhoit, Apr 25 '12 at 18:31

Mike Sherrill 'Cat Recall' · Accepted Answer · 2012-04-25T20:52:02.570

MySQL has a lot of restrictions on foreign keys. Among the ones that might be getting in your way . . .

Both tables have to use the INNODB engine.
"In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. "

This code works in my version (5.1.62).

create table DEP_FACULTY(
  LNAME varchar(50),
  FNAME varchar(50),
  EMAIL varchar(70) primary key
) ENGINE = INNODB;

insert into DEP_FACULTY values ('Gregor', 'Brandich', 'gbrandich@thisdomain.com');

create table STUDENT(
  LNAME varchar(50),
  FNAME varchar(50),
  ID char(9) primary key,
  ASSIGNED_ADVISOR_EMAIL varchar(70),
  foreign key (ASSIGNED_ADVISOR_EMAIL) references DEP_FACULTY(EMAIL) ON DELETE SET NULL,
  IS_ADVISED tinyint(1)
) ENGINE = INNODB;

insert into STUDENT values ('Charmaine', 'DePeletier', 'cmd', 'gbrandich@thisdomain.com', 1);

create table ADVISE_HIST(
  STUDENT_ID char(9),
  foreign key (STUDENT_ID) references STUDENT(ID),
  ACTUAL_ADVISOR_EMAIL char(70) NOT NULL,
  foreign key (ACTUAL_ADVISOR_EMAIL) references DEP_FACULTY(EMAIL),
  DATE_ADVISED date,
  primary key REF_ADVISE_HIST (STUDENT_ID, DATE_ADVISED)
) ENGINE = INNODB;

insert into ADVISE_HIST values ('cmd', 'gbrandich@thisdomain.com', CURRENT_DATE);
insert into ADVISE_HIST values ('ctd', 'gbrandich@thisdomain.com', CURRENT_DATE);

Of those last two inserts, the first works. The second should throw a foreign key constraint error.

Aren't such indexes created automatically if they don't pre-exist? — eggyal, Apr 25 '12 at 19:26
@Catcall i tried setting ENGINE=InnoDB at the end of my tables and it won't create the table (i'm assuming because of the foreign key) and i'm not sure what you mean in your second bullet — Daniel Wilhoit, Apr 25 '12 at 19:49

mysql foreign key reference from a composite key

1 Answers1

Linked