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?
- 1You can use trigger for same.Mahesh Meniya– Mahesh Meniya2012-04-05 05:12:44 +00:00Commented Apr 5, 2012 at 5:12
- Use Before Insert and check the dates by selecting and comparing them with each other so u can make decision on insertingAdeel Aslam– Adeel Aslam2012-04-05 07:24:44 +00:00Commented Apr 5, 2012 at 7:24
Add a comment |
2 Answers
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.
Comments
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.
