SQL Constraint: Date a Is Before Date B -- How

SQL Constraint: date A is before date B -- How?

create table foo
(
   from_date date,
   to_date date,
   constraint check_dates check (from_date < to_date)
);

Or if you need to apply this to an existing table, use:

alter table foo
   add constraint check_dates check (from_date < to_date);

The PostgreSQL manual contains a good chapter about check constraints: http://www.postgresql.org/docs/current/static/ddl-constraints.html#AEN2410

CHECK constraint on date of birth?

Check constraints must be deterministic. That is, a particular row must always satisfy the constraint or it must always fail to satisfy the constraint. But SYSDATE is inherently non-deterministic since the returned value is constantly changing. Thus, you cannot define a CHECK constraint that calls SYSDATE or any other user-defined function.

If you try to reference SYSDATE in the constraint definition, you'll get an error

SQL> ed
Wrote file afiedt.buf

  1  create table t(
  2      birth_date date check( birth_date between date '1900-01-01' and
  3                                                sysdate )
  4* )
SQL> /
                                              sysdate )
                                              *
ERROR at line 3:
ORA-02436: date or system variable wrongly specified in CHECK constraint

You could create a CHECK constraint where both the minimum and maximum date were hard-coded but that would not be particularly practical since you'd have to constantly drop and recreate the constraint.

SQL> ed
Wrote file afiedt.buf

  1   create table t(
  2       birth_date date check( birth_date between date '1900-01-01' and
  3                                                 date '2011-12-08' )
  4*  )
SQL> /

Table created.

The practical way to enforce this sort of requirement would be to create a trigger on the table

CREATE OR REPLACE TRIGGER check_birth_date
  BEFORE INSERT OR UPDATE ON employee
  FOR EACH ROW
BEGIN
  IF( :new.emp_dob < date '1900-01-01' or 
      :new.emp_dob > sysdate )
  THEN
    RAISE_APPLICATION_ERROR( 
      -20001, 
      'EMployee date of birth must be later than Jan 1, 1900 and earlier than today' );
  END IF;
END;

How to make a unique constraint on date that there is no row with date within x months

You can achieve this with an "exclusion constraint", which is like a generalised unique constraint which can check any operator. See this question for some background on the syntax.

In particular, we can say that no two rows A and B should exist where the following conditions hold:

• A.name equals B.name
• A.type equals B.type
• A.date is between B.date and B.date + 6 months

Note that you don't also need to check the 6 months before B.date, because that will checked by looking at the rows the other way around: B.date will be between A.date and A.date + 6 months.

To make the last condition implementable with a single operator, we can express it in terms of ranges:

• The range A.date to A.date + 6 months overlaps the range B.date to B.date + 6 months

We can then write an exclusion constraint which analyses using the && (range overlap) operator, which looks like this:

Alter Table entries
   Add Constraint name_and_type_within_6_months
   Exclude Using Gist (
       name with =,
       type with =,
       tsrange(date, date + interval '6 months') with &&
   );

(Hat tip to Philipe Fatio for this gist showing a date range exclusion.)

Here is an interactive demo showing that constraint in action: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=83181388416d1e5905e088532839ad79

Date range overlapping check constraint

The CHECK is being executed after the row has been inserted, so the range overlaps with itself.

You'll need to amend your WHERE to include something like: @MyTableId <> MyTableId.

---

BTW, your WHERE expression can be simplified.

Ranges don't overlap if:

• end of the one range is before the start of the other
• or start of the one range is after the end of the other.

Which could be written in SQL like:

WHERE @DateEnd < DateStart OR DateEnd < @DateStart

Negate that to get the ranges that do overlap...

WHERE NOT (@DateEnd < DateStart OR DateEnd < @DateStart)

...which according to De Morgan's laws is the same as...

WHERE NOT (@DateEnd < DateStart) AND NOT (DateEnd < @DateStart)

...which is the same as:

WHERE @DateEnd >= DateStart AND DateEnd >= @DateStart

So your final WHERE should be:

WHERE
    @MyTableId <> MyTableId
    AND @DateEnd >= DateStart
    AND DateEnd >= @DateStart

[SQL Fiddle]

NOTE: to allow ranges to "touch", use <= in the starting expression, which would produce > in the final expression.

How to get the soonest date in relation to another date field

One method is apply:

select a.*, b.*. -- or whatever columns you want
from a outer apply
     (select top (1) b.*
      from b
      where b.item_id = a.item_id and
            b.date2 >= '2020-10-12'
      order by b.date2 asc
     ) b;

Restrict Insert based on previous insertion date

You only want to insert when there not exists a record with the same col1 and a too recent date:

insert into mytable (col1, col2, date_create)
select 'B' as col1, 4 as col2, trunc(sysdate) as date_create from dual ins
where not exists
(
  select *
  from mytable other
  where other.col1 = ins.col1
  and other.date_create > ins.date_create - 4
);

An undesired record would not be inserted thus. However, no exception would be raised. If you want that, I'd suggest a PL/SQL block or a before insert trigger.

---

Related Topics