PostgreSQL: constraint, Insert value in column only if it exists in another table

Question

I want to add a constraint to my table student such that any value entered in its majors column must also exist in majors table. How can I do this?

Answer 1

That's a foreign key constraint.

ALTER TABLE student 
 ADD CONSTRAINT somename 
 FOREIGN KEY (major_id) REFERENCES major (id);

Note that if student.major_id is nullable, the column's value can still be null.

Note also your table doesn't accommodate double majors. To do that, we'd have a student_major table that is a many-to-many relation between student and major. This also demonstrates creating foreign keys in a create table, instead of in an alter table

create table student_major (
 id serial not null unique primary key,           -- optional, but good idea
 student_id int not null references student(id),  -- fk 
 major_id int not null references major(id),      -- fk
 UNIQUE (student_id, major_id)                    -- no redundant relations
);

Comment:

-1 for rejecting composite keys. – Bill Karwin

So let me understand this. Bill agrees that I correctly answered the OP's questions about constraints. He agrees that I correctly saw what the OP hadn't asked about, possible double majors. But Bill still marks this answer as wrong because Bill and I disagree about composite keys.

I didn't even say that a synthetic id was necessary; indeed, I specifically said it was optional but in my opinion a good idea. (Why? It "plays better" with deletes, with tables that might reference student_majors, and with ORMS and generated code in general.)

Bill, that's frankly petty. You marked down a correct answer over an elaboration (composite/synthetic) on an elaboration (students:majors being M:M instead of M:1), and over what's a "religious" war. Do you mark down correct answers because you disagree with the answerer's stand on tabs vs. spaces or vi vs. emacs? Maybe you should have taken the time to give your own answer, rather than marking down correct answers.