Cloud Platform

you cannot have a foreign key constraint without a primary key, and you cannot have a nullable primary key. So I wonder how you tested that in 9i.

Your query is ugly, it does not support values like '*' and it supports only characters.

The proper way to write this would be

where ( a.col1 = b.col1 or (a.col1 is null and b.col1 is null)) and
 ( a.col2 = b.col2 or (a.col2 is null and b.col2 is null));

but this is just the join, not the constraint! Maybe you could enforce the constraint with a trigger.

A workaround I could eventually suggest is replacing NULLs with -1 or with a special string like "*", which is imho less messy than implementing referential integrity with triggers.

Regards
Laurent

No, for example, I haven't. On the other hand the only problem what I had with such databases was to migrate them into something more data base like instead of data waste like :)
But I think that even such a constraint is better than nothing.

Hmm but I assume that even without any FK constraints developers had to make joins even before, so they'v used nvl's before? Or you are trying to add some more functionality? Or trying to rewrite some functionality?

Gints Plivna
http://www.gplivna.eu

Thanks for the reply, but yes you can FK to a UK.


http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14220/glossary.htm
excerpt-

foreign key

Integrity constraint that requires each value in a column or set of columns to match a value in a related table's UNIQUE or PRIMARY KEY.

FOREIGN KEY integrity constraints also define referential integrity actions that dictate what Oracle should do with dependent data if the data it references is altered.

Actually that fancy thing is possible, although honestly I've always created FK's only pointing to PK's :)

SQL> create table tab (a number not null, b number);

Table created.

SQL> alter table tab add constraint tab_uk unique (a, b);

Table altered.

SQL> create table tab1 (a number not null, b number);

Table created.

SQL> alter table tab1 add constraint tab1_tab_fk foreign key (a, b) references tab (a, b);

Table altered.

SQL> insert into tab values (1, null);

1 row created.

SQL> insert into tab1 values (1, 1);
insert into tab1 values (1, 1)
*
ERROR at line 1:
ORA-02291: integrity constraint (GINTS.TAB1_TAB_FK) violated - parent key not
found


SQL> insert into tab1 values (1, null);

1 row created.

Gints Plivna
http://www.gplivna.eu

you cannot have a foreign key constraint without a primary key, and you cannot
have a nullable primary key. So I wonder how you tested that in 9i.

We can do this with a UNIQUE constarint...

SQL> create table abc1 (id number, some_col varchar2(3))
  2  /

Table created.


SQL> alter table abc1 add constraint abc_uk unique (id, some_col)
  2  /

Table altered.

SQL> insert into abc1 values (1, null)
  2  /

1 row created.

SQL> insert into abc1 values (1, 'APC')
  2  /

1 row created.

SQL> create table xyz1 (id number, some_col varchar2(3))
  2  /

Table created.

SQL> alter table xyz1 add constraint xyz_fk foreign key (id, some_col)
  2  references abc1(id, some_col)
  3  /

Table altered.

SQL> insert into xyz1 values (1, null)
  2  /

1 row created.

SQL> insert into xyz1 values (1, 'APC')
  2  /

1 row created.

SQL> insert into xyz1 values (2, 'APC')
  2  /
insert into xyz1 values (2, 'APC')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.XYZ_FK) violated - parent key not found


SQL>

Laurent obviously ate too much Toblerone whilst celebrating Switzerland's National Day yesterday :)

Cheers, APC

thanks for this info, I did not know that, and you are very right

SCOTT@LSC01> create table t1(a number unique);

Table created.

SCOTT@LSC01> create table t2(a number references t1(a));

Table created.

In this case, having NULL sound perfectly legal. Where did you get this hint of using NVL(c,'*') ?

I would then add a constraint CHECK (c!='*') in the table to avoid having a wrong join...

First off this database was designed pre oracle 7 and is now running on 9.2. Im converting unique not null indexes to PK constraints and adding FK constraints where I can. The FKs are there, just not enforced with a constraint. Just so ya know, FK constraints didnt exist before 7. Anyway I have a table and a bunch of other tables related to my table in question. I also have a lot of packages and procedures referring to my table in question. It would be nice not to have re-write a bunch of code eh? So, heres my dilemma.... My table has columns a,b,c. Currently, It does not have a primary key defined however columns a,b are unique but b is sometimes null. Columns a,b are used throughout the database as unenforced FK in multiple tables. So, I was going to make a Id column for the primary key in my table and use that on all new tables as the FK. Make a unique key on a,b and just foreign key that where it currently exists throughout the database. I wouldnt have to rewrite any code or redesign any database objects. ??

I think the reason why you and I find this so perplexing is that even though it is allowed, the unique index with null values part, it happens so infrequently. And typically from a ground up design you and I probably wouldnt even allow this to happen. But, Im doing the old school retrofit thing here.

The check(c!=*) is a good idea..

I didnt read the nvl trick anywhere just thought it up and posted it here for comments.

See my other post to understand why I'm even considering this... :(

Thanks..

OK, I probably learned at school then :)
Yea, if they are returning results as expected and you haven't at least a pair of testers.
It seems so, because from procedural code perspective you wouldn't change anything at all. Except of course if your application at first inserts child record and only then parent which could be quite possible if you hadn't foreign keys to catch such situation. Even then you can create constraints as deferrable assuming changes are done in one transaction.

Gints Plivna
http://www.gplivna.eu

or simply as I said above

where ( a.col1 = b.col1 or (a.col1 is null and b.col1 is null)) and
 ( a.col2 = b.col2 or (a.col2 is null and b.col2 is null));

so you do not have to worry if col1 is a number and col2 a date ;-) nor do the check.

Not quite true. Oracle 6 had constraints, it just didn't enforce them. Although, looking back at it now, I bet I there was an underscore parameter than would have enabled enforcement, at least in the later version of 6.

Cheers, APC