SQL & PL/SQL

I've encountered a difference between an UPDATE clause and a MERGE INTO clause, both which accomplish the same thing, but it would appear that the MERGE INTO clause is triggering extra constraint validations. I'm unable to find an explanation for this.

I'm using 10g.

Here is my test case.

1. Create the tables.

CREATE TABLE MK_NAME (
    name_id NUMBER,
    name VARCHAR2(20),
    PRIMARY KEY (name_id));


CREATE TABLE MK_INVOICE (
    invoice_id NUMBER,
    status VARCHAR2(20),
    supplier_name_id NUMBER);

2. Populate the tables. Note the third invoice (5002) references a name that doesn't exist in MK_NAME.

INSERT INTO MK_NAME (name_id, name) values (100, 'TipperCo');
INSERT INTO MK_NAME (name_id, name) values (200, 'MuffyCo');

INSERT INTO MK_INVOICE (invoice_id, supplier_name_id) values (5000, 100);
INSERT INTO MK_INVOICE (invoice_id, supplier_name_id) values (5001, 200);
INSERT INTO MK_INVOICE (invoice_id, supplier_name_id) values (5002, 333);

3. Add a constraint on supplier_name_id but do not validate it.

ALTER TABLE MK_INVOICE MODIFY (supplier_name_id CONSTRAINT RMK_NAME REFERENCES MK_NAME(name_id) NOVALIDATE);

4. If I attempt to set the status column for invoice 5002 using an UPDATE statement, this will succeed.

UPDATE MK_INVOICE
set status = 'Approved'
where invoice_id = 5002;

 16:02:51  [UPDATE - 1 row(s), 0.015 secs]  Command processed
... 1 statement(s) executed, 1 row(s) affected, exec/fetch time: 0.015/0.000 sec  [1 successful, 0 warnings, 0 errors]

However, if I use a MERGE INTO statement which will achieve the same result, this will fail because the constraint that was added in step 3 will be validated against supplier_name_id, even though supplier_name_id is not part of this statement.

MERGE INTO MK_Invoice i
USING (SELECT invoice_id
       FROM MK_Invoice
       WHERE invoice_id = 5002) s
ON (i.invoice_id = s.invoice_id)
WHEN MATCHED THEN UPDATE
SET i.status = 'Approved';


 16:04:59  [MERGE - 0 row(s), 0.000 secs]  [Error Code: 2291, SQL State: 23000]  ORA-02291: integrity constraint (DOCPADMIN.RMK_NAME) violated - parent key not found
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec  [0 successful, 0 warnings, 1 errors]

Can anyone please explain this difference? Why has Oracle chosen to validate invoice 5002's supplier_name_id value in the second statement but not the first?

Thanks,

Mark