Can Insert [...] on Conflict Be Used for Foreign Key Violations

Can INSERT [...] ON CONFLICT be used for foreign key violations?

Yes, join your input rows to the referenced table, thereby removing rows without a match on the FK column:

INSERT INTO entries(entry_id, referenced_id, name)
SELECT val.entry_id, val.referenced_id, val.name
FROM  (
  VALUES (1, 2, 'references two')
         -- more?
  ) val (entry_id, referenced_id, name)
JOIN   referenced USING (referenced_id)  -- drop rows without matching FK
ON     CONFLICT (entry_id) DO NOTHING;   -- drop rows with duplicate id

The UPSERT itself (INSERT ... ON CONFLICT DO NOTHING) only reacts to unique violations. The manual:

ON CONFLICT can be used to specify an alternative action to raising a unique constraint or exclusion constraint violation error. (See ON CONFLICT Clause below.)

Since the VALUES expression is now not attached to an INSERT directly, column types are not derived from the target table. You may need to cast input values explicitly when operating with non-basic types. See:

• Casting NULL type when updating multiple rows

Is it possible to ignore foreign key conflicts on insert with sqlite?

There is no equivalent of INSERT OR IGNORE, which works only for the violation of UNIQUE constraints, for violation of FOREIGN KEY constraints.

As a workaround you can use EXISTS in a INSERT ... SELECT statement:

WITH cte(book_name, category) AS (
    VALUES 
    ('Harry Potter', 'Fiction'),
    ('Harry Potter', 'Fantasy'),
    ('Foundation', 'Fiction'),
    ('Foundation', 'Science Fiction'),
    ('The Catcher in the Rye', 'Coming-of-age'),
    ('Moby Dick', 'Adventure')
)
INSERT INTO category_table (book_name, category)
SELECT c.book_name, c.category
FROM cte c
WHERE EXISTS (SELECT 1 FROM master_node m WHERE m.book_name = c.book_name)

See the demo.

Results:

> book_name              | category       
> :--------------------- | :--------------
> Harry Potter           | Fiction        
> Harry Potter           | Fantasy        
> Foundation             | Fiction        
> Foundation             | Science Fiction
> The Catcher in the Rye | Coming-of-age 

Do nothing on foreign key violation in SQL insert query

I used a variation of a query from this answer. Thanks to @_horse_with_no_name

This is the query that worked for me :

with data_table(col1, col2, col3, col4,
                col5, col6) as (
    values
        ('value1','','1','num1','1900-01-01'::date, '9999-12-31'::date),
        ('value2','','1','num2','1900-01-01'::date, '9999-12-31'::date)   
)
insert into table_1(col1, col2, col3,
                    col4, col5, col6)
select d.col1, d.col2, d.col3, d.col4,
       d.col5, d.col6
from data_table d
where exists (select 1
              from table_2 t2
              where t2.col1 = d.col4)
on conflict(col1, col2, col3, col4, col5, col6) do nothing;

Conflict with foreign key when the related entity is added before SaveChanges

The reason you are getting this issue is that without the relationships between entities, EF does not guarantee that the order of insert will match your code. With the FK constraint EF is trying to insert the AuditPerson record first and tripping the FK. By specifying the navigation properties EF can work out the order to insert the records.

If you don't typically access Audits from Person then I'd suggest putting the Person reference in Audit and map as:

HasRequired(x => x.Person)
  .WithMany()
  .HasForeignKey(x => x.PersonId);

Then when setting up your entities:

context.Persons.Add(person);
var audit = new AuditPerson { AuditId = Guid.NewGuid(), Timestamp = DateTime.UtcNow, Person = person };

context.PersonAudits.Add(audit);
context.SaveChangesAsync().Wait();

Also, if your DB is SQL Server I'd recommend utilizing the database to manage the PKs using newsequentialId() and set EF to recognize them as Identity columns. Alternatively you can generate GUIDs using the same hi/lo byte order to make UUIDs that match SQL's sequential ID. These IDs are more index-friendly.

Is it possible to catch a foreign key violation in postgres

If you're only inserting a single row at a time, you can create a savepoint before the insert and rollback to it when the insert fails (or release it when the insert succeeds).

For Postgres 9.5 or later, you can use INSERT ... ON CONFLICT DO NOTHING which does what it says. You can also write ON CONFLICT DO UPDATE SET column = value..., which will automagically convert your insert into an update of the row you are conflicting with (this functionality is sometimes called "upsert").

This does not work because OP is dealing with a foreign key constraint rather than a unique constraint. In that case, you can most easily use the savepoint method I described earlier, but for multiple rows it may prove tedious. If you need to insert multiple rows at once, it should be reasonably performant to split them into multiple insert statements, provided you are not working in autocommit mode, all inserts occur in one transaction, and you are not inserting a very large number of rows.

Sometimes, you really do need multiple inserts in a single statement, because the round-trip overhead of talking to your database plus the cost of having savepoints on every insert is simply too high. In this case, there are a number of imperfect approaches. Probably the least bad is to build a nested query which selects your data and joins it against the other table, something like this:

INSERT INTO table_A (column_A, column_B, column_C)
SELECT A_rows.*
FROM VALUES (...) AS A_rows(column_A, column_B, column_C)
JOIN table_B ON A_rows.column_B = table_B.column_B;

How do UPSERT with on conflict two or more constraints?

PostgreSQL treats NULL as distinct value, therefore, you can have multiple NULL values in a column with a UNIQUE index. When you define a primary key or a unique constraint for a table, PostgreSQL automatically creates a corresponding UNIQUE index.

The solution is to create a unique index, with coalesce. In this example I have used coalesce(~, 0) which means that null and 0 are treated as the same thing. You might prefer to use another value, for example the maximum possible value of int which is 2147483648.Please not that we have to modify the ON CONFLICT list to match the index.

CREATE temp TABLE my_table (
    id_1 int,
    id_2 int,
    quantity numeric,
    mytable_date date
);

✓

CREATE UNIQUE INDEX
my_table_unique ON my_table
(coalesce(id_1,0), coalesce(id_2,0), coalesce(mytable_date,'1900-01-01'));

✓

INSERT INTO my_table (id_1, id_2, quantity, mytable_date)
    VALUES (1, NULL, 5, '2022-04-27'), (NULL, 5, 5, '2022-04-27'), (99, 85, 100, '2022-04-29')
ON CONFLICT (coalesce(id_1,0), coalesce(id_2,0), coalesce(mytable_date,'1900-01-01'))
    DO UPDATE SET
        quantity = excluded.quantity;

INSERT INTO my_table (id_1, id_2, quantity, mytable_date)
    VALUES (99, 85, 101, '2022-04-29')
ON CONFLICT (coalesce(id_1,0), coalesce(id_2,0), coalesce(mytable_date,'1900-01-01'))
    DO UPDATE SET
        quantity = excluded.quantity;

3 rows affected

1 rows affected

select * from my_table;

id_1 | id_2 | quantity | mytable_date
---: | ---: | -------: | :-----------
   1 | null |        5 | 2022-04-27  
null |    5 |        5 | 2022-04-27  
  99 |   85 |      101 | 2022-04-29  

*db<>fiddle here74bf159a4d041c31fec5f)

PostgreSQL foreign key constraint violation with inheritance

No, that won't work.

Like the documentation says:

A serious limitation of the inheritance feature is that indexes
(including unique constraints) and foreign key constraints only apply
to single tables, not to their inheritance children. This is true
on both the referencing and referenced sides of a foreign key constraint.

“Global indexes” are one of the important missing features of table inheritance.

---

Related Topics