Derby: Referencing Multiple Foreign Keys From Different Tables

Question

Good day!

I have been searching the Internet for an answer to my problem but I have not been able to get one. How do I reference foreign keys from different tables in a Derby database?

This is my current SQL code:

CREATE TABLE class_t
(course_id VARCHAR(6) NOT NULL,
semester VARCHAR(6) NOT NULL CONSTRAINT sem_constraint CHECK (semester IN ('1st','2nd','module')),
school_year DATE NOT NULL,
course_name VARCHAR(70) NOT NULL,
CONSTRAINT class_pk PRIMARY KEY (course_id, semester, school_year)
);

CREATE TABLE student_t
(id_number INT NOT NULL,
fullname VARCHAR(35) NOT NULL,
contact_num VARCHAR(35),
email VARCHAR(25),
CONSTRAINT student_pk PRIMARY KEY (id_number)
);

CREATE TABLE student_list
(course_id VARCHAR(6) NOT NULL,
semester VARCHAR(6) NOT NULL CONSTRAINT sem_constraint2 CHECK (semester IN ('1st','2nd','module')),
school_year DATE NOT NULL,
id_number INT NOT NULL,
CONSTRAINT student_list_pk PRIMARY KEY (course_id, semester, school_year, id_number),
CONSTRAINT student_list_FK FOREIGN KEY (course_id, semester, school_year, id_number)
REFERENCES class_t (course_id, semester, school_year), student_t (id_number) #this is my problem
);

Your help is very much appreciated! Thanks in advance.

What would you _like_ to write in place of the `(course_id, ... id_number)` in your foreign key declaration? — sarnold, Apr 12 '12 at 01:40
@sarnold Do you mean like this: `REFERENCES class_t, student_t (course_id, semester, school_year, id_number)`? I tried it but it still has an error. — Ace Eusebio, Apr 12 '12 at 01:48

score 4 · Accepted Answer · answered Apr 13 '12 at 01:11

Nevermind, I've figured it out myself. Had a clue at the solution when I looked at http://db.apache.org/derby/docs/10.2/ref/rrefsqlj13590.html in the examples part. There should be different constraints for each foreign keys from different tables. It should be like this:

CREATE TABLE student_list
(course_id VARCHAR(6) NOT NULL,
semester VARCHAR(6) NOT NULL CONSTRAINT sem_constraint2 CHECK (semester IN ('1st','2nd','module')),
school_year DATE NOT NULL,
id_number INT NOT NULL,
CONSTRAINT student_list_pk PRIMARY KEY (course_id, semester, school_year, id_number),
CONSTRAINT student_list_fk1 FOREIGN KEY (course_id, semester, school_year)
REFERENCES class_t (course_id, semester, school_year),
CONSTRAINT student_list_fk2 FOREIGN KEY (id_number)
REFERENCES student_t (id_number)
);

Thanks for all your help stackoverflow.com community! :)

Derby: Referencing Multiple Foreign Keys From Different Tables

1 Answers1