OracleBlog

18 months.
107 posts.
110,000 reads.

Not too long ago I reacted with humble incredulity that 10,000 people had read what I had to say about Oracle.

Here I am, exactly one year later, and there have been 100,000 more visits. I'm almost afraid to continue, will I be talking about 1,000,000 in November 2007?

I wonder why people are reading my blog because, despite how numerous we are, it seems like every other Oracle blogger knows this material far better than I. So I've taken the time to ask some of you why.

Although it would be nice if someone did me the compliment of disputing my claim, instead it appears that my lack of expertise is part of the appeal. It seems the average Oracle user out there is closer to my level rather than some expert like, say, Laurent Schneider or Jeff Hunter.

Perhaps that makes my work more accessible. The complexities of Oracle often make you feel dumb, so you can come to me to feel smart again. :)

In that vein, I'd like to get some of you started on your own blogs. You may feel intimidated because of all the experts out there, but if anything I'm proof that you don't have to be an Oracle guru to make a contribution.

I think you'd be surprised at just how warm a reception you'll receive no matter what level you're at. If you'd like to give it a try, I'm offering you an audience on my blog. I'd be happy to answer questions, review your first few posts, and invite others here to do the same, until you have decided whether or not blogging is for you.

My contact information is in my profile, I look forward to hearing from you! And thanks for reading!

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.