Referencing 2 foreign keys in Oracle-CodePudding

I've got 3 schemas, Course, Takes, Instructs.

Under course, I have a course_id as primary key which I want to reference the attribute course_id in both takes and instructs. How can I do that?

Currently this is my course schema where I'm only referencing 1 other table

CREATE TABLE Course (
course_id int PRIMARY KEY REFERENCES Takes (course_id),
)

CodePudding user response：

Are you sure you don't have it backwards?

Takes references Course.

Instructs references Course

Would be a more normal arrangement.

You can do ADD CONSTRAINT after table creation if that's truly your model.

CodePudding user response：

As far as I understood, you have the Course table with the primary key course_id. Then, there are tables Takes and Instructs and each of them has to reference the Course table (have course_id as a foreign key). In your query, Course references Takes but it has to be vice versa. In case none of those tables exist you need to create them as follows:

CREATE TABLE Course (course_id INT PRIMARY KEY);

CREATE TABLE Takes (take_id INT PRIMARY KEY, course_id INT, 
    FOREIGN KEY (course_id) REFERENCES Course(course_id));

CREATE TABLE Instructs (instruct_id INT PRIMARY KEY, course_id INT,
    FOREIGN KEY (course_id) REFERENCES Course(course_id));

In case the tables Takes and Instructs already exist you should modify them with ALTER TABLE:

ALTER TABLE Takes
ADD CONSTRAINT fk_course
FOREIGN KEY (course_id)
REFERENCES Course(course_id);

And the same for the Instructs table.