49

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?

maxschlepzig
  • 35,645
  • 14
  • 145
  • 182
Jimmy
  • 501
  • 1
  • 4
  • 3

3 Answers3

78

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))
Aleksi Yrttiaho
  • 8,266
  • 29
  • 36
  • 3
    This check does not guard against both schoolName and studiedAt being set, which I think the OP also had in mind. – pithyless Jul 26 '11 at 18:17
  • 2
    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. – Aleksi Yrttiaho Jul 26 '11 at 19:47
  • 32
    It's a bit late, but this is an XOR constraint so you can express it as `CHECK((schoolName IS NULL) <> (studiedAt IS NULL))` – norcalli Aug 16 '17 at 22:11
  • 4
    In case the table already exists you can use: `ALTER TABLE Education ADD CONSTRAINT chk_schoolName_studiedAt CHECK ((schoolName IS NULL) <> (studiedAt IS NULL))` – Hans Bouwmeester Oct 09 '19 at 14:45
  • @HansBouwmeester or @norcalli I was trying to find, without success, the docs for this `<>` operator, could you point me in the right direction? – benregn Jan 04 '23 at 18:57
  • @benregn, `<>` is the "not equal" operator (as is `!=`), it's documented in: https://www.postgresql.org/docs/9.0/functions-comparison.html – xeonman9000 Jun 14 '23 at 22:24
0

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.

Kuberchaun
  • 29,160
  • 7
  • 51
  • 59
  • 2
    caveat: its often better not to enforce a constraint than resort to a trigger –  Mar 27 '11 at 20:07
  • 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. – Kuberchaun Mar 28 '11 at 11:36
  • I guess this has been [debated before](http://stackoverflow.com/questions/460316/are-database-triggers-evil). 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) –  Mar 28 '11 at 11:52
0

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 }] },
}),
hellvisor
  • 463
  • 5
  • 15