Adding Simple Constraint - Database

Question

I am trying to add simple constraint in the database. I follow this

My goal: In Tenants table, if LeaseExpirationDate isn't NULL then it must be later than LeaseStartDate.

I tried:

alter table Tenants
   add constraint leasedates_check check (leaseexpirationdate is not null or leaseexpirationdate > leasestartdate);

Then I try:

UPDATE 
  Tenants 
SET 
  leaseexpirationdate = leasestartdate
WHERE
  apartmentnumber = 1 and houseid = 100;

The row is updated even if leaseexpirationdate is not null. Where am I wrong? Am I not comparing dates right?

Create Statement:

CREATE TABLE Tenants(
HouseID INT,
ApartmentNumber INT,
LeaseTenantSSN INT NOT NULL,
LeaseStartDate DATE NOT NULL,
LeaseExpirationDate DATE,
Rent DECIMAL(7,2),
LastRentPaidDate DATE,
RentOverdue BOOLEAN,
PRIMARY KEY(HouseID, ApartmentNumber));

I am using Postgresql.

Nimantha · Accepted Answer · 2022-01-04 04:23:58Z

4

This constraint:

alter table Tenants
   add constraint leasedates_check check (leaseexpirationdate is not null or leaseexpirationdate > leasestartdate);

is equivalent to:

alter table Tenants
   add constraint leasedates_check check (leaseexpirationdate is not null);

Presumably, you seem to intend:

  add constraint leasedates_check check (leaseexpirationdate is null or leaseexpirationdate > leasestartdate);

Note that the check is for is null and or is the connector.

edited Jan 4, 2022 at 4:23

Nimantha

6,5276 gold badges32 silver badges78 bronze badges

answered Feb 26, 2017 at 18:44

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

My goal: In Tenants table, if LeaseExpirationDate isn't NULL then it must be later than LeaseStartDate.

check(leaseexpirationdate is not null or leaseexpirationdate > leasestartdate)

This says that leaseexpirationdate must not be null, or if it is null it must be greater than leasestartdate. Comparing null isn't very useful. It will return unknown which is the third boolean in SQL's ternary logic.

Instead you want to check that leaseexpirationdate is null or if it isn't null it must be greater than leasestartdate.

check(leaseexpirationdate is null or leaseexpirationdate > leasestartdate)

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Feb 26, 2017 at 18:45

Schwern

167k28 gold badges225 silver badges370 bronze badges

Comments

Haleemur Ali · Accepted Answer · 2017-02-26 19:17:49Z

Postgresql has a special infinity value for datetime types that makes expressing these kinds of rules saner. Here, if I'm not mistaken, the null value is used to indicate a lease that does not have an expiry date.

My goal: In Tenants table, if LeaseExpirationDate isn't NULL then it must be later than LeaseStartDate.

So, the table definition can be:

CREATE TABLE "Tenants"(
  "HouseID" INT NOT NULL,
  "ApartmentNumber" INT NOT NULL,
  "LeaseTenantSSN" INT NOT NULL,
  "LeaseStartDate" DATE NOT NULL,
  "LeaseExpirationDate" DATE NOT NULL DEFAULT 'INFINITY',
  "Rent" DECIMAL(7,2),
  "LastRentPaidDate" DATE,
  "RentOverdue" BOOLEAN,

  PRIMARY KEY("HouseID", "ApartmentNumber")
);

And, with that the check constraint can be:

ALTER TABLE "Tenants"
   ADD CONSTRAINT leasedates_check check ("LeaseExpirationDate" > "LeaseStartDate");

@Gordon Linoff's answer above is correct as well, and is the default way most developers approach start / end times & associated constraints in databases.

Also note that Postgresql identifiers are case-insensitive, unless quoted, as in this answer.

Collectives™ on Stack Overflow

Adding Simple Constraint - Database

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related