help you fix your problem Foreign keys enforce referential integrity. These constraints guarantee that a row in a table order_details with a field order_id referencing an orders table will never have an order_id value that doesn't exist in the orders table. Foreign keys aren't required to have a working relational database (in fact MySQL's default storage engine doesn't support FKs), but they are definitely essential to avoid broken relationships and orphan rows (ie. referential integrity). The ability to enforce referential integrity at the database level is required for the C in ACID to stand.
hope this fix your issue An SQL FK constraint says its referencing column list subrow values, if all non-NULL, have to appear as subrow values for its referenced column list, which must be declared UNIQUE/PK in a base table. Your constraint is not an SQL FK constraint.
(SELECT name FROM Person p JOIN Employee e ON p.name = e.name)(name)
How can a relational database with foreign key constraints ingest data that may be in the wrong order?
fixed the issue. Will look into that further You have a business workflow problem, because line items of individual orders are coming in before the orders themselves have come in. One workaround, perhaps not ideal, would be to create a before insert trigger which checks, for every incoming insert to the line_item table, whether that order already exists in the order table. If not, then it will first insert the order record before trying the insert on line_item.
CREATE OR REPLACE FUNCTION "public"."fn_insert_order" () RETURNS trigger AS $$
INSERT INTO "order" (order_number)
WHERE NOT EXISTS (SELECT 1 FROM "order" WHERE order_number = NEW.order_number);
CREATE TRIGGER "trigger_insert_order"
BEFORE INSERT ON line_item FOR EACH ROW
EXECUTE PROCEDURE fn_insert_order()