Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – how to ensure certain columns have the same data in a table that has a composite primary key

I have a table called order and it stores what a customer needed for their order.

The table consists of (It is simplified):

orderID, stockID, quantity, custID, handledBy and status.

The composite primary key of this table is orderID and stockID.

How do I ensure that certain fields have the same data when inserting new records?

For example:

I want to add these records:

(order001, stock001, 15, cust001, staff001, Packing)

(order001, stock002, 50, cust001, staff001, Ready to Deliver)

How do I ensure that the custID and the staffID fields have the same data so that the below possible scenario will not happen?

(order001, stock001, 15, cust001, staff001, Packing)

(order001, stock002, 50, cust005, staff007, Ready to Deliver)

The idea is that each order will have the exact same customer (custID) and the same order will always be handled by the same staff (staffID), so if order001 belongs to the customer cust001 and handled by staff001, all the other records with the orderID order001 will always have cust001 as the data in custID and staff001 as the data in staffID

Tags:

2

Answers


  1. 0

    You may user trigger to resolve this problem
    Please refer below code, It will allow to insert only that data which has same order, custID and staffID combination

    Drop table MasterOrderCustStaffMapping
create table MasterOrderCustStaffMapping(orderID varchar(500), custID varchar(500), staffID varchar(500))

insert into MasterOrderCustStaffMapping
select distinct orderID,custID,staffID FROM Order_Details

CREATE TRIGGER UPD_Order_Details ON Order_Details
FOR INSERT 
AS
BEGIN

  DELETE O
  FROM Order_Details O
  INNER JOIN    INSERTED D ON O.orderID=D.orderID AND O.stockID=D.stockID 
AND O.quantity=D.quantity AND O.custID=D.custID AND O.staffID=D.staffID AND O.status=D.status
  LEFT JOIN MasterOrderCustStaffMapping M ON D.orderID=M.orderID 
and D.staffID=M.staffID AND M.custID=O.custID
  WHERE     M.orderID IS NULL   

END
    Login or Signup to reply.
  2. 0

    You might want to revisit your data design to improve normalization by splitting this table into separate Order and OrderDetails tables, so that the custID and handledBy values are defines once per order row and not repeated for each detail row.

    However, if you are stuck with the current design, you can use a trigger to check the consistency of affected orders following an insert or an update of the columns of interest, and throw an error if the check fails.

    CREATE TRIGGER TR_Orders_Check_Consistency
    ON Orders
    AFTER INSERT, UPDATE
AS BEGIN
    IF (NOT EXISTS (SELECT TOP 1 * FROM DELETED) -- Any insert
        OR UPDATE(orderId) OR UPDATE(custID) OR UPDATE(handledBy)) -- Update of key columns
    BEGIN
        IF EXISTS(
            SELECT *
            FROM (SELECT DISTINCT I.orderId FROM INSERTED I) IDS
            JOIN Orders O ON O.orderId = IDS.orderId
            GROUP BY O.OrderId
            HAVING MIN(O.custID) <> MAX(O.custID)
            OR MIN(O.handledBy) <> MAX(O.handledBy)
            OR COUNT(O.custID) NOT IN (0, COUNT(*)) -- Mixed null/non-null values
            OR COUNT(O.handledBy) NOT IN (0, COUNT(*)) -- Mixed null/non-null values
        )
        THROW 51000, 'Conflicting custID or handledBy values in the Orders table.', 1
    END
END

    The above trigger will fire after any insert or update operation on the orders table. (It will not fire for a delete, since that cannot cause a failure for this use case.) The cnsistency check will execute only if either (1) the action is an insert (indicated by an empty DELETED special table) or (2) or of the action was an update that changed one of the columns of interest.

    The logic identifies all updated orderIDs and checks that, for each distinct orderId, all custID and handledBy values are consistent by comparing the minimum and maximum values, which should be the same. It also has checks to verify that either all values are null or all are non-null. This latter check is only needed if the columns being tested are nullable.

    If inconsistent values are found, a THROW statement is executed to generate an error. This error will also cause the current action to be rolled back.

    If you with to suppress the "null value is eliminated by an aggregate or other SET operation" warnings, you can modify the trigger with the following alternate logic.

                HAVING MIN(ISNULL(O.custID, '')) <> MAX(ISNULL(O.custID, ''))
            OR MIN(ISNULL(O.handledBy, '')) <> MAX(ISNULL(O.handledBy, ''))
            OR SUM(IIF(O.custID IS NULL, 0, 1)) NOT IN (0, COUNT(*)) -- Mixed null/non-null values
            OR SUM(IIF(O.handledBy IS NULL, 0, 1)) NOT IN (0, COUNT(*)) -- Mixed null/non-null values

    See this db<>fiddle for a demo.

    A few more notes:

    • A CHECK constraint was also considered, but check constrains execute on a row-by-row basis and would not permit a multi-row updates that, for example, changes the handledBy value across all rows for a given order.
    • The RAISERROR command cannot be used, since it will not force a roll back of the current operation.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search