OracleBlog

You have a table with all your company's financial transactions. There is another table which references a subset of these financial transactions (ie. transactions with certain properties).

Your current solution to maintain the integrity is to set up a foreign key, referencing the transactions table, and then write a trigger to make sure that any records reference only transactions that have the correct properties.

However, you were struck with a brilliant idea to use views for a more elegant solution. You create a view on the transactions table that contains only the proper subset, and then set up a foreign key to the view instead.

You create the view, including the appropriate primary key constraint, and you're all set to go. Except when you create your table to reference that view, you see this:

ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list

Have you done something wrong?

No. You've come up with a very clever solution, but unfortunately one that isn't supported. According to Oracle's SQL Reference, "Oracle does not enforce view constraints. However, you can enforce constraints on views through constraints on base tables."

Of course, referencing the base table doesn't do you any good, because there are transactions in there that aren't valid references for your new table. Quoting Dan Morgan: "The point of the syntax is to prevent violations when people do DML (inserts, updates, deletes through the view). It does not truly serve the same purpose as a "real" primary key and can not be used in the same way."

However, reading on, both Table 17-3 (Object Privileges Available for Particular Objects) and this sentence seems to offer some hope that your solution can be achieved. "To create a foreign key constraint, in addition, the parent table or view must be in your own schema, or you must have the REFERENCES privilege on the columns of the referenced key in the parent table or view."

Alas, the documentation is misleading here. It's talking about the view itself having a foreign key, not the primary. Observe.

SQL> CREATE TABLE basetable
2 (id VARCHAR2(32) PRIMARY KEY, amount NUMBER(10), other_columns VARCHAR2(32));

Table created.

SQL> GRANT REFERENCES (id) ON basetable TO PUBLIC;

Grant succeeded.

SQL> CREATE OR REPLACE base_view
2 AS SELECT * FROM basetable WHERE amount > 100;

View created.

SQL> ALTER view base_view ADD CONSTRAINT pk_view PRIMARY KEY (id) RELY DISABLE NOVALIDATE;

View altered.

SQL> GRANT REFERENCES (id) ON base_view TO PUBLIC;

Grant succeeded.

SQL> CREATE TABLE referring (person VARCHAR2(32) PRIMARY KEY,
2 base_id REFERENCES base_view(id));
CREATE TABLE referring (person VARCHAR2(32) PRIMARY KEY,
*
ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list

That is not to say that there isn't a more elegant solution to your problem. One of the many things I've learned about Oracle is this: if something clever isn't working, re-examine your schema.

In fact that is precisely the advice that Oracle ACE Andrew "Arfur C." Clarke gave someone in your exact same position, who had wisely posted his problem to a forum (instead of asking a dummy like me).

For instance, you could instead create a table representing the subset of financial transactions you want, and another identically-structured table for all other transactions, and then your transactions table could be a view of both. That's what one expert considered. However, I'm not sure that's what you want: maintaining two identical tables. Plus, now your financial transactions table is a view and can't be referenced, putting you in the same boat for some other table.

I didn't intend to offer a specific suggestion for your schema because it would require knowing a lot more about your business, these tables, and what your requirements really are. I'm just saying that there may be an elegant solution if you take a closer look.

I preach views because they serve so many useful purposes. Unfortunately, managing this type of integrity constraint directly isn't one of them. Stick to your trigger.