Re: Problem creating foreign keys

applet_at_mailhost.sasknet.sk.ca (Lois Appleton) wrote:

>I'm trying to create a foreign key on a table. I have successfully created
>foreign keys on 11 tables using the data field client_number. When I try to
>create it on this table it fails telling me there is no matching values but I
>can do a join on the two tables using client_number.
 

>The data field client_number has been defined the same on all tables.
 

>Any ideas or suggestions?

I don't know about the bug in 7.1 the others are talking about, but I have a suggestion. Doing a join using the client_number doesn't guarantee that there are matching values in both tables (unless you are doing an outer join obviously). It just shows you the MATCHES, not the rows that don't match. Try a subquery instead.

SELECT count(*)
FROM table_a a

WHERE NOT EXISTS (	SELECT null
			FROM table_b b
			WHERE b.client_number = a.client_number )

When the count is zero you know for sure that everything check out. Just my two cents worth. Maybe it is the bug after all...

+------------------------------------------------------------+
|   Mike Philippens - Gorinchem - The Netherlands, Europe    |
|                 Vijfhart Automatisering bv                 |
|      Oracle Specialists in Training and Consultancy        |
|                 Utrecht    The Netherlands                 |
+------------------------------------------------------------+