CODEWITHSUNDEEP
sqlpostgresqlknex.js
M K
M K

Reputation: 306

How to check if one of the values is present

I'm making changes to old schema and the new requirement is to have 3 different columns in the table. At least one has to be filled with ID.

I'm thinking about implementing service to check if any of field is present before insert. But still wondering if there is a solution to implement this on the database side.

Let's say table looks something like this:

businessId  int     businessId or userId or followerId   
userId      int     businessId or userId or followerId   
followerId  int     businessId or userId or followerId

I can not mark any of these fields required, because upon insert data may be inserted into a different column. So if any of the fields are present it should allow the database insertion.

Upvotes: 0

Views: 679

Answers (1)

GMB
GMB

Reputation: 222472

I understand that you are trying to set up a composite NOT NULL constraint on 3 columns. In Postgres you could set up a check constraint as follows:

ALTER TABLE mytable
  ADD CONSTRAINT at_least_one_non_null
  CHECK (
      businessId IS NOT NULL
      OR userId IS NOT NULL
      OR followerId IS NOT NULL
  );

This will ensure that at least one the 3 columns is not null on each and every record.

Upvotes: 2

Related Questions