Ошибка violation of foreign key constraint

I have two tables: entitytype and project. Here are the create table statements:

Create table project ( 
pname varchar(20) not null, 
primary key(pname)
);

create table entitytype( 
entityname varchar(20) not null, 
toppos char(100), 
leftpos char(100), 
pname varchar(20) not null, 
primary key(entityname), 
foreign key(pname) references project(pname) on delete cascade on update cascade
);

When I try to insert any values into the entitytype table, I get the following error:

ERROR: insert or update on table "entitytype" violates foreign key constraint "entitytype_pname_fkey"
  Detail: Key (pname)=(494) is not present in table "project".

Can anyone shed some light on what I am doing wrong?

В Retail Declaration при попытке принять ТТН может возникать ошибка Violation of  FOREIGN KEY constraint

Причина: Как правило, данная ошибка возникает в случае, если данная ТТН была принята в RD и ее пытаются принять повторно. 

Решение:

Внимание! После повторного приема может потребоваться подача корректирующей декларации. Перейдите в раздел Движение продукции/Движение продукции и запросите движение продукции в целом по подразделениям, по 1 регистру и по 2 регистру. Посмотрите, нет ли там красных строк. Если красные строки есть, то отредактируйте документы таким образом, чтобы не было красных строк. 

Никак не разберусь почему пробрасывает исключение constraint violation. Таблица и запрос простые:

CREATE TABLE `taxonomy_vocabulary` (
  `name` varchar(32),
  `parent_name` varchar(32),
  PRIMARY KEY (`name`),
  KEY `fk-taxonomy_vocabulary-parent_name` (`parent_name`)
);

ALTER TABLE `taxonomy_vocabulary`
  ADD CONSTRAINT `fk-taxonomy_vocabulary-parent_name` FOREIGN KEY (`parent_name`) REFERENCES `taxonomy_vocabulary` (`name`) ON DELETE SET NULL ON UPDATE CASCADE;

INSERT INTO `taxonomy_vocabulary` (`name`, `parent_name`) VALUES
('test1', NULL),
('test2', 'test1');

UPDATE `taxonomy_vocabulary` SET `name`='test12345' WHERE `name`='test1';

Последняя строка с update бросает foreign key violation по единственному foreign ключу таблицы. Почему это происходит?

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.

Summary

Restoring a backup fails while restoring ActiveObjects and a «foreign key constraint» message.

The message may contain a reference to table AO_733371_EVENT or some other, but the cause and resolution should be the same. The examples presented here are from the AO_733371_EVENT table.

See

JRASERVER-73298
—
Getting issue details…
STATUS

Environment

Jira Core: All versions 7 and 8.

Jira Software: All versions 7 and 8.

Jira Service Desk: All versions 3 and 4.

Diagnosis

When restoring a backup into Jira, the restore executes for the most part but fails near the end with a message concerning ActiveObjects.

On the logs, we can see a message about violation of foreign key constraint. It’s usually about the EVENT tables, with the EVENT_ID varying from instance to instance and from backup file to backup file, but you may find other tables referenced as well:

There was a problem restoring ActiveObjects data for the <unknown plugin> plugin. Caught exception with following message: ERROR: insert or update on table "AO_733371_EVENT_RECIPIENT" violates foreign key constraint "fk_ao_733371_event_recipient_event_id" Detail: Key (EVENT_ID)=(2276078) is not present in table "AO_733371_EVENT". Please check the log for details.

On the example above, it’s event #2276078.

Cause

The backup generation doesn’t stop Jira, so data is continually being written and updated by Jira while the backup’s running. This leads to race conditions and the backup snapshot might have a partial data: like an event record without its corresponding event attributes.

Solution

There are two solutions to this scenario: edit the activeobjects.xml file inside the backup.zip or take a new backup during a time when Jira’s under less usage — and less likely to be writing/updating so much data.

Schedule the backup to more quiet hours

This is the preferred solution. If you don’t need the most recent backup possible, you may use one generated during a weekend. Or schedule the backup to more quiet hours, if possible.

Try the import again with this new backup and see if the «foreign key constraint» message shows no more.

Edit the ActiveObjects.XML contained in the backup.zip

This approach is only advised if the above isn’t an option. Mind you may still receive further errors after fixing this one because — as the cause is — the backup was generated during intense activity in Jira.

Example:

1. Extract the backup file, this will contain two xml files (activeobjects.xml and entitites.xml).
2. Open the ActiveObjects.xml in a text editor. In case the file is too large then split the file into smaller chunks.
3. Search for the table reference <data tableName=«AO_733371_EVENT_RECIPIENT»> in the file.

4. Below the table you can find the rows that contain the data that causes the exception, for example:

   Example

   <row>
       <string>mailEventConsumer</string>
       <timestamp>2020-06-05T08:31:20.430Z</timestamp>
       <integer>2276078</integer>
       <integer>1079085</integer>
       <timestamp>2020-06-05T08:40:39.421Z</timestamp>
       <string>NEW</string>
       <timestamp xsi:nil="true"/>
       <string>amanl</string>
   </row>

    The value in the line <integer>2276078</integer> that matches the event_id in the original exception.

5. Remove all rows that reference the event_id and save the xml file. Rows are shown in the xml as displayed in step 4, this entire section from <row> until the closing parenthesis </row> needs to be removed.
   1. You can even remove all rows from this table if needed- you will likely not need old email notifications on the restored Jira instance
6. In case you split the xml file make sure it is one single file again.
7. Zip the folder containing the two xml files.
8. Now try the import again by referring to the documentation on running an import.