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?

Felicefelicia

Answer 1

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))

Bonn

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

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. – Bonn

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

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

@HansBouwmeester or @Tananarive I was trying to find, without success, the docs for this <> operator, could you point me in the right direction? – Oxyacetylene

@benregn, <> is the "not equal" operator (as is !=), it's documented in: postgresql.org/docs/9.0/functions-comparison.html – Predicate

Answer 2

You can also use a trigger on update and insert to check that a rule is followed before allowing the data into the table. You would normally use this type of approach when the check constraint needs more complicated logic.

Neuropath

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

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. – Neuropath

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) – Eldwun

Answer 3

This is my solution for sequelize migration file in "up" function

queryInterface.addConstraint('Education', {
  fields: ['schoolName', 'studiedAt'],
  type: 'check',
  name: 'schoolName_or_studiedAt_is_null',
  where: { [Sequelize.Op.or]: [{ password: null }, { googleId: null }] },
}),

Collocation

Answer 4

Super late to the party, but this also works:

constraint validate_null check (not(schoolName is null and studiedAt is null))

Heat