I am a new one in postgreSQL. I have 3 tables, one table references the other 2 table's primary keys. But I couldn't insert data into the Table3
. See the code below :
DROP TABLE Table1 CASCADE;
CREATE TABLE Table1(
"DataID" bigint NOT NULL DEFAULT '0',
"AdData" integer DEFAULT NULL,
PRIMARY KEY ("DataID")
);
DROP TABLE IF EXISTS Table2 CASCADE;
CREATE TABLE Table2 (
"Address" numeric(20) NOT NULL DEFAULT '0',
"Value" numeric(20) DEFAULT NULL,
PRIMARY KEY ("Address")
);
DROP TABLE IF EXISTS Table3 CASCADE;
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
);
ERROR: insert or update on table "Table3" violates foreign key
constraint "Table3_DataID_fkey" DETAIL: Key (DataID)=(27856) is not
present in table "Table1".
When I tried to insert data into the 3 tables, an error occured. I referred the postgreSQL documentation and changed my code as follows: (Unfortunately it showed another error)
DROP TABLE Table1 CASCADE;
CREATE TABLE Table1(
"DataID" bigint NOT NULL DEFAULT '0',
"AdData" integer DEFAULT NULL,
PRIMARY KEY ("DataID")
);
DROP TABLE IF EXISTS Table2 CASCADE;
CREATE TABLE Table2 (
"Address" numeric(20) NOT NULL DEFAULT '0',
"Value" numeric(20) DEFAULT NULL,
PRIMARY KEY ("Address")
);
DROP TABLE IF EXISTS Table3 CASCADE;
CREATE TABLE table3 (
"ID" bigint NOT NULL DEFAULT '0',
"DataID" bigint DEFAULT NULL REFERENCES Table1 ON DELETE RESTRICT,
"Address" numeric(20) DEFAULT NULL REFERENCES Table2 ON DELETE CASCADE,
"Data" bigint DEFAULT NULL,
PRIMARY KEY ("ID"),
PRIMARY KEY("DataID", "Address")
);
ERROR: multiple primary keys for table "Table3" are not allowed LINE
65: PRIMARY KEY("DataID", "Address")
Please help me… How I can create the reference?
I changed the ID
as UNIQUE
and removed the line PRIMARY KEY ("ID")
. At that time it shows another error like :
ERROR: duplicate key value violates unique constraint "Table3_pkey"
Best Answer
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: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, checkDataID
andAddress
: if they contain a non-NULL value (say 27856), then checkTable1
forDataID
Λ™andTable2
forAddress
. If there is no such value in those tables, then return an error." This last point which you've seen first:So simple: if there is no row in
Table1
whereDataID = 27856
, then you can't insert that row intoTable3
.If you need that row, you should first insert a row into
Table1
withDataID = 27856
, and only then try to insert intoTable3
. 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
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 staticDEFAULT
(like'0'
) to aUNIQUE
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
ornumeric
in your case). Use simply0
instead (or don't use it at all, as I written above).And a last point (I may be wrong here): in
Table2
, yourAddress
field is set tonumeric(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 inTable3
likeNow 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.