Re: Order of Table Creation and Foreign Keys

diamond (diamond_at_pavilion.co.uk) wrote:
: I am creating a small d/b (about 10 tables) and have written and tested the
: SQL scripts to create and populate each table. When I try to add Foreign Keys
: to some tables, I get error message 'Cannot Add Referential Constraint - Parent Keys
: Not Found'.

You have added some records to the table for which there is no parent. Therefore the foreigh key constraint has been violated. Oracle will not enable the constraint in this case. Hence the error.

Try a select like

	SELECT for_key_feild FROM child c
	WHERE not EXISTS (SELECT 1 FROM parent p
			  WHERE c.for_key_feild = p.for_keyfeild);

You can't enable or add the key if the above select returns any rows.

: I think this is something to do with the order in which I create the tables
: and Primary Keys
: thereon. Is this the case ?

If the tables are empty, or fully populated with data which does not violate the constraints the order does not matter. If you are populating tables with the constraints on the order does matter.

: Also, does anyone know a 'foolproof' method for deriving a Table Craetion Order from
: (say) a basic E-R diagram ?

Two rules one for entities and one for relationships.

• For each entity related to another entity load the table with the 1 cardinality 1 before the other entity. Note: there should be no 1-1 relations.
• Load relationship tables after loading the entities being related.

Given the foreign key constraints, never load a table with a foreign key constraint (child) before the table the constraint refers to (parent). I have seen rare cases where the parent is a decendent of the child. In such cases.you must load the data first and then enable the constraints.

: Thanks in advance
 

: Robin Farr
: diamond_at_pavilion.co.uk

Hope this clarifies your problem.

/Bill Thorsteinson Received on Wed Nov 29 1995 - 00:00:00 CET