Adding an one-out-of-two not null constraint in postgresql

Question

If I have a table in Postgresql:

create table Education ( 
    id                  integer references Profiles(id),
    finished            YearValue not null,
    started             YearValue,
    qualification       text,
    schoolName          text,
    studiedAt           integer references Organizations(id),
    primary key (id)
);

I need to make a constraint so that either schoolName or studiedAt needs to not be null (one of them has to have information in it).

How do I do this?

Solution

You can use a check constraint e.g.

constraint chk_education check (schoolName is not null or studiedAt is not null)

From the manual:

A check constraint is the most generic constraint type. It allows you to specify that the value in a certain column must satisfy a Boolean (truth-value) expression.

Edit: Alternative to comply with Pithyless' interpretation:

constraint chk_education check ((schoolName is not null and studiedAt is null) or (schoolName is null and studiedAt is not null))

Comments

[Admin]

caveat: its often better not to enforce a constraint than resort to a trigger

[Abdul Saqib]

I wouldn't go as far to say it's better to not enforce a constraint than use a trigger, I might say the oposite. When using a trigger always keep performance in mind and use them wisely. Don't use them for the heck of it. Test them for you specific needs and if it allows you to meet your needs go for it. There is not inherent evil with using a trigger. It's another tool in the box.

[Admin]

I guess this has been debated before. My gripe with them is that they do not enforce rules on existing data and there are often ways round them to get data in without them firing (eg sql*loader for Oracle). Given that, an api with well-defined transactions provides no less data integrity without any of the side-effects (and a whole lot more benefits)

[fofx]

This check does not guard against both schoolName and studiedAt being set, which I think the OP also had in mind.

[jamee]

The question asks for an invariant that forces at least on of schoolName and studiedAt contains some information. I've added a variant of the constraint that complies to your comment though I do not agree with the interpretation of the question.

[norcalli]

It's a bit late, but this is an XOR constraint so you can express it as CHECK((schoolName IS NULL) <> (studiedAt IS NULL))

[Hans Bouwmeester]

In case the table already exists you can use: ALTER TABLE Education ADD CONSTRAINT chk_schoolName_studiedAt CHECK ((schoolName IS NULL) <> (studiedAt IS NULL))