CODEWITHSUNDEEP
sqlpostgresql
Junaid S.
Junaid S.

Reputation: 2642

Adding Simple Constraint - Database

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.

Upvotes: 0

Views: 66

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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.

Upvotes: 4

Haleemur Ali
Haleemur Ali

Reputation: 28233

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.

Upvotes: 0

Schwern
Schwern

Reputation: 164679

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)

Upvotes: 3

Related Questions