There are a few problems with your tables. I'll try to address the foreign keys first, since you question asked about them :)
But before that, we should realize that the two sets of tables (the first three you created and the second set, which you created after dropping the first set) are the same. Of course, the definition of Table3
in your second attempt has syntax and logical errors, but the basic idea is:
CREATE TABLE table3 (
"ID" bigint NOT NULL DEFAULT '0',
"DataID" bigint DEFAULT NULL,
"Address" numeric(20) DEFAULT NULL,
"Data" bigint DEFAULT NULL,
PRIMARY KEY ("ID"),
FOREIGN KEY ("DataID") REFERENCES Table1("DataID") on delete cascade on update cascade,
FOREIGN KEY ("Address") REFERENCES Table2("Address") on delete cascade on update cascade
);
This definition tell PostgreSQL roughly the following: "Create a table with four columns, one will be the primary key (PK), the others can be NULL
. If a new row is inserted, check DataID
and Address
: if they contain a non-NULL value (say 27856), then check Table1
for DataID
˙and Table2
for Address
. If there is no such value in those tables, then return an error." This last point which you've seen first:
ERROR: insert or update on table "Table3" violates foreign key constraint
"Table3_DataID_fkey" DETAIL: Key (DataID)=(27856) is not present in table "Table1".
So simple: if there is no row in Table1
where DataID = 27856
, then you can't insert that row into Table3
.
If you need that row, you should first insert a row into Table1
with DataID = 27856
, and only then try to insert into Table3
. If this seems to you not what you want, please describe in a few sentences what you want to achieve, and we can help with a good design.
And now about the other problems.
You define your PKs as
CREATE all_your_tables (
first_column NOT NULL DEFAULT '0',
[...]
PRIMARY KEY ("ID"),
A primary key means that all the items in it are different from each other, that is, the values are UNIQUE
. If you give a static DEFAULT
(like '0'
) to a UNIQUE
column, you will experience bad surprises all the time. This is what you got in your third error message.
Furthermore, '0'
means a text string, but not a number (bigint
or numeric
in your case). Use simply 0
instead (or don't use it at all, as I written above).
And a last point (I may be wrong here): in Table2
, your Address
field is set to numeric(20)
. At the same time, it is the PK of the table. The column name and the data type suggests that this address can change in the future. If this is true, than it is a very bad choice for a PK. Think about the following scenario: you have an address '1234567890454', which has a child in Table3
like
ID DataID Address Data
123 3216547 1234567890454 654897564134569
Now that address happens to change to something other. How do you make your child row in Table3
follow its parent to the new address? (There are solutions for this, but can cause much confusion.) If this is your case, add an ID column to your table, which will not contain any information from the real world, it will simply serve as an identification value (that is, ID) for an address.
Since it is a temp table, you are OK to just delete the files.
rm -f *#sql-ib32694.*
Is this harmful to InnoDB ? No. There was a data dictionary entry still inside ibdata1
. However, that entry is either missing or invalid (due to the tablespace_id
). There is actually a one in 1,099,511,627,766 chance of it causing a problem.
See my older posts on why it is not that harmful and how to deal with
Please keep in mind that temp tables (regardless of storage engine) are transient in nature. A temp table will disappear under these circumstances:
- When a query is finished using it
- When a DB connection terminates normally
- When a DB connection terminates abnormally
If a temp table still exists due to a crash, rest assured that the data dictionary no longer acknowledges its existence. Please delete them and keep a good conscience.
If you are worried about, your only recourse when this happens is to perform a fully InnoDB Cleanup. See my post Howto: Clean a mysql InnoDB storage engine? in StackOverflow. The lesson here
- Use
CREATE TEMPORARY TABLE (...) ENGINE=MyISAM;
if you have to use temp tables in your app.
- Any system-generated InnoDB temp tables can be deleted after a crash.
Best Answer
There are two sides to a foreign key constraint:
Postgres implements this by installing triggers on both tables to perform the existence checks. But the parent trigger simply won't work if the child is a temp table: the trigger needs to fire for all database users in order to guarantee referential integrity, but the temp table data is only visible within the session which created it, so the constraint cannot be enforced.
Even if it were possible, it's probably a bit too intrusive; it's rarely desirable to have a
DELETE
on the parent fail just because some other user is referencing it from a temporary dataset. But if you really need to maintain integrity in this case, you can get much the same effect by locking the parent records with aSELECT ... FOR KEY SHARE
.