CODEWITHSUNDEEP
oracle-databaseconstraints
pragya
pragya

Reputation: 23

Check Constraint in Oracle

I have been struggling to figure out a way to insert check such that it won't allow payment date to be earlier than the invoice date. basically, I have two table: invoice and payment. so I want a check constraint to enter into payment a date that is before the purchase date in invoice table. The invoice_id in Invoice table is a FK in payment table. Any help?

Upvotes: 0

Views: 458

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

One approach might be to duplicate the purchase date in the payment table, add a second unique constraint over the combination of invoice_id + purchase date, then modify the FK constraint to include it. Then, you can have your row-level constraint. e.g.

CREATE TABLE invoices
( invoice_id    NUMBER NOT NULL
, purchase_date DATE NOT NULL
, CONSTRAINT invoice_pk PRIMARY KEY (invoice_id)
, CONSTRAINT invoice_uk UNIQUE (invoice_id, purchase_date)
);

CREATE TABLE payments
( payment_id    NUMBER NOT NULL
, invoice_id    NUMBER NOT NULL
, purchase_date DATE NOT NULL
, payment_date  DATE NOT NULL
, CONSTRAINT payment_pk PRIMARY KEY (payment_id)
, CONSTRAINT payment_invoice_fk
    FOREIGN KEY (invoice_id, purchase_date)
    REFERENCES invoices (invoice_id, purchase_date)
, CONSTRAINT payment_date_ck
    CHECK (payment_date >= purchase_date)
);

Downside: updating invoices.purchase_date becomes a bit tricky.

Upvotes: 2

cagcowboy
cagcowboy

Reputation: 30828

A check constraint can only look at columns on the table it is defined on.

You'll need to use a TRIGGER to do this.

Upvotes: 3

Related Questions