At 04:52 26/08/2008, Sergio H. Gonzalez wrote:

>Hello, I want to create a foreign key on a table. (invoice_detail -> invoice) So
>I deleted all records from both tables (the data was just for test) and when I
>want to create the FK I receive an exception that "" is not if the referened
>table. Why?

Bad syntax? The DDL parser has read an empty string where it expected the identifier[s] of the unique key column[s] of the referenced table, e.g.

create table master (
master_id BigInt not null,
.....,
constraint pk_master PRIMARY KEY(master_id));

commit; -- <----------------- ESSENTIAL!

create table detail (
detail_id BigInt not null,
master_id BigInt not null,
...,
constraint pk_detail PRIMARY KEY(detail_id),
constraint fk_master FOREIGN KEY(master_id)
REFERENCES master(master_id));

If the FK is referring to the primary key of the master (as here) you can optionally omit the column argument since the FK will refer to the master's PK by default - so this is equivalent:
....(
constraint fk_master FOREIGN KEY(master_id)
REFERENCES master
);

However, if the FK references the key column[s] of a UNIQUE constraint, you must provide the referenced columns as an argument.

Note also that you cannot reference a foreign key to only one column of a master table constraint that consists of multiple columns.

And pay attention to committing the PK or UNIQUE constraint in the master table before requesting any foreign keys that refer to it.

>Shoul I have records any record in the master (invoices) table
>though I have the detail table empty?

No. Here the creation of the FK constraint is failing. It has nothing to do with data at this point. On the other side of the coin, if there is data in the detail table, the creation of a FK constraint will fail if the detail table contains reference data that does not match any row in the referenced master constraint column.

./heLen