Postgresql – Removing duplicate rows and changing related foreign keys in PostgreSQL

Make sure that there are indexes on the constrained columns, since the dbms will do a lookup using those columns in the referencing tables.

You can try using this script, it generates an index creation script for any non-indexed column being referenced by a cascading constraints.

SELECT 'CREATE NONCLUSTERED INDEX IX_'+OBJECT_NAME(fk.parent_object_id)+'_'+c.name+' ON '+OBJECT_NAME(fk.parent_object_id)+'('+c.name+') WITH (ONLINE=ON)' 
        --, OBJECT_NAME(fk.referenced_object_id) AS referenced_tale,  cc.name
FROM 
    sys.foreign_keys fk
    INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
    INNER JOIN sys.columns cc ON fkc.referenced_object_id = cc.object_id AND fkc.referenced_column_id = cc.column_id
WHERE delete_referential_action_desc IN ('CASCADE', 'SET_NULL')
    AND NOT EXISTS
        (
        SELECT  1
        FROM
            SYS.index_columns ic 
            INNER JOIN sys.indexes i ON i.object_id = ic.object_id AND ic.index_id = i.index_id
        WHERE 
            1 = 1
            AND type_DESC IN ('CLUSTERED','NONCLUSTERED')
            AND ic.OBJECT_ID = c.object_id
            AND ic.column_id = c.column_id
            AND ic.is_included_column = 0
        )

I think this is the original idea.

First thing to notice is that the PK on the LineItem table has three attributes {CustomerID, CustomerOrderNo, OdrerItemNo}, as opposed to just two in your example.

Second thing to note is the confusion resulting from the use of the generic id name for an attribute.

The CustomerOrderNo should ideally be (1,2,3..) for each customer and OrderItemNo (1,2,3 ...) for each order.

Well, this is nice if possible, but requires a query looking for the previous max value, like

select max(CustomerOrderNo)
from Order 
where CustomerID = specific_customer ; 

which is often not preferred in high-transaction-volume environments, so it is common to see these replaced by an auto-increment, essentially serving the same purpose. It is true that this auto-incremet is now unique, hence it can be used as a KEY -- but you may choose to look at it as a necessary compromise for the OrderItemNo.

So, with some renaming CustomerOrderNo -> OrderNo and OrderItemNo -> ItemNo you may arrive to this model

So now if you look at the Order the following are unique

{OrderNo}             -- PK
{CustomerID, OrderNo} -- superkey,  AK on the diagram.

Note that {CustomerID, OrderNo} is propagated to the LineItem to serve as a FK.

If you squint a bit, this is close to your example, but with PKs {ItemNo} and {OrderNo} only -- as opposed to two column PKs from your example.

Now the question is, why not simplify to something like this?

Which is fine, but introduces PATH DEPENDENCE -- you can not join LineItem with Customer directly, must use Order in the join.

I prefer the first case when possible -- you choose your favourite. And obviously, there is no need for direct FK from LineItem to Customer in these three cases.