Search code examples
sqlpostgresqlconstraintscheck-constraints

How can I ensure that Column_A can only have a value if Column_B is NULL? And vice versa

I'm attempting to create a table that has three columns:

id

paid_at

failed_at

How can I make sure that paid_at can only have a value if failed_at is NULL?

Here is my current code:

 CREATE TABLE charges(
      id        TEXT     NOT NULL     PRIMARY KEY,
      paid_at   TEXT,
      failed_at TEXT
    );

    ALTER TABLE charges
      ADD CONSTRAINT paid_at CHECK (failed_at IS NULL);

    ALTER TABLE charges
      ADD CONSTRAINT failed_at CHECK (paid_at IS NULL);

I also want to make sure that BOTH cannot be null.

How can I do this?

Thanks!

Solution

  • You can use the following predicate:

    alter table charges add constraint exclusive_rule check (
  paid_at is null and failed_at is not null or
  paid_at is not null and failed_at is null
);