0

I have two tables in PostgreSQL:

create Table student (
 studentID integer primary key,
 studentname text
);

create Table courses (
 courseID text primary key,
 schoolname text
);

I want to create a third table schoolstudent that has a foreign key (studentID, schoolname) where studentID references the primary key of the student table and schoolname references the schoolname key in the courses table.

How can I create a foreign key from two different tables in PostgreSQL 9.4 or 9.5?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
R.Y.H
  • 73
  • 3
  • 10

2 Answers2

2

A FK constraint requires a UNIQUE or PK constraint on the target column(s), which schoolname obviously cannot provide. You need another table with unique rows per school:

CREATE TABLE school(
  school_id serial PRIMARY KEY,
  schoolname text NOT NULL
);

CREATE TABLE student(
 student_id serial PRIMARY KEY,
 studentname text
 );

CREATE TABLE schoolstudent(
 school_id  int REFERENCES school,
 student_id int REFERENCES student,
 PRIMARY KEY (school_id, student_id)
);

CREATE TABLE course(
 course_id text PRIMARY KEY,
 school_id int REFERENCES school
);

Using short syntax for foreign key constraints. Details in the manual.

If you really need schoolname in the schoolstudent table (I seriously doubt that, looks like a design error), you can just add it. To enforce referential integrity you can include it in the foreign key, but you need a (redundant) matching UNIQUE constraint on school(school_id, schoolname), too.

CREATE TABLE schoolstudent(
 school_id  int,
 student_id int REFERENCES student,
 schoolname text,
 PRIMARY KEY (school_id, student_id),
 CONSTRAINT schoolstudent_combo_fk FOREIGN KEY (school_id, schoolname)
    REFERENCES school (school_id, schoolname) ON UPDATE CASCADE
);

Using explicit syntax in this case. And I suggest to cascade updates.

Or if schoolname is actually guaranteed to be UNIQUE (again, my doubts) you can replace school_id completely and just use schoolname as PK and FK column. Long text columns are not very efficient for the purpose, though - if performance matters. And schoolnames change, which is not ideal for PK columns.

You still need a separate school table in any case.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Is their a way that I can use the schoolname without having to create new table or it is not possible? because i need the schoolname in the 'schoolstudent' table – R.Y.H Dec 02 '15 at 06:43
  • @R.Y.H: Consider the addendum. – Erwin Brandstetter Dec 02 '15 at 07:08
  • `is there a way that I can use the schoolname withou ...` This depends on whether `schoolname` is fully dependent on course_id (we don't know, you mabe do know). If the same course can be tought at *more than one* school, *at least* one extra table will be needed. (and studentcourse will have to refer to student+{course,school} – joop Dec 02 '15 at 11:57
0

you can set many to many relation only if both the fields are Unique(probably Primary Keys).if above condition is Fulfilled you can use

CREATE TABLE Schoolstudent(
   ID INT references     student(studentID),
   Schoolname CHAR(50)  references courses(Schoolname),

);

But schoolname in table courses should be unique or PK.

akhil kumar
  • 1,598
  • 1
  • 13
  • 26
  • Is there any way that I can combine the studentID with the schoolname as a foreign key so it becomes unique. Since studentID is unique so the foreign key(studentID, schoolname) will become unique?? For example, if we have coursename attribute, we can create foreign key(courseID, coursename) references courses(courseID,coursename). Can we do the same thing if the two attributes are in different tables?? – R.Y.H Dec 02 '15 at 07:14