Primary and Foreign Keys in Oracle - back to the basics

Sometimes we get so far along in life, we "forget" about the basics. I've been designing databases for quite some time now (most in Oracle) and yesterday someone asked me something so fundamental I was completely taken by surprise because I didn't know the answer off the top of my head.

Q: Can you create a Foreign Key in a table which references a column with a unique index in another table (but is NOT the Primary Key)?

A: My initial reflex response was "No, a Foreign Key is always a Primary Key in a parent table".

...But...
When trying to do this, Oracle gives you a pretty vague Error Description.
"ORA-02270: no matching unique or primary key for this column-list"

Here's an example

SQL>CREATE TABLE a (
2 a_id NUMBER PRIMARY KEY,
3 a_string VARCHAR2(10));

Table created.


SQL>CREATE unique INDEX a_idx ON a(a_string);

Index created.


SQL>CREATE TABLE b (
2 b_id NUMBER PRIMARY KEY,
3 a_string VARCHAR2(10));

Table created.


SQL>ALTER TABLE b
2 ADD CONSTRAINT b_fk FOREIGN KEY (a_string)
3 REFERENCES a(a_string);
REFERENCES a(a_string)
*
ERROR at line 3:
ORA-02270: no matching unique or primary key for this column-list


So this got me thinking, if table "a" did not have a Primary Key, but a unique constraint on the string field, would Oracle allow me to do this?

SQL>CREATE TABLE a (
2 a_id NUMBER,
3 a_string VARCHAR2(10));

Table created.

SQL>CREATE unique INDEX a_idx ON a(a_string);

Index created.

SQL>CREATE TABLE b (
2 b_id NUMBER PRIMARY KEY,
3 a_string VARCHAR2(10));

Table created.

SQL>ALTER TABLE b
2 ADD CONSTRAINT b_fk FOREIGN KEY (a_string)
3 REFERENCES a(a_string);
REFERENCES a(a_string)
*
ERROR at line 3:
ORA-02270: no matching unique or primary key for this column-list


Nope, it didn't let me do it. All is well in the world of Oracle physical data modeling. But "why oh why" does the error message start with "no matching unique" when it should just say "no matching primary key"?

Silly little things which could in fact saved me a little bit of grief, but being humbled every once in a while is good for the ego.

Until next time...Rich

Comments

Shabs said…
Rich

I was reading up on the same error when your blogpost came up.

I think the error description does make sense. If i created a UNIQUE constraint on the parent table's field in question and not a UNIQUE index, the fkey does go through.

What do you think?