Why should you care?

If two Swiggy payments share the same gateway reference ID, reconciliation breaks — UNIQUE on that column catches duplicates in the database.

See it live — copy this example

Run in MySQL Workbench or the mysql CLI.

CREATE TABLE IF NOT EXISTS orders (
  order_id     INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id  INT UNSIGNED NOT NULL,
  order_ref    VARCHAR(32) NOT NULL UNIQUE,
  total_inr    DECIMAL(12,2) NOT NULL CHECK (total_inr >= 0),
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

What happened?

• order_ref must be unique across rows.
• total_inr cannot be negative.
• customer_id must exist in customers — foreign key enforces parent row first.

Practice next

1. Ensure customers exists, then CREATE orders.
2. Insert valid row with a real customer_id.
3. Try duplicate order_ref — note duplicate key error.
4. Add CHECK (CHAR_LENGTH(order_ref) >= 8) and test short refs.
5. Show constraints: SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA='DataFlow' AND TABLE_NAME='orders';

Remember

Constraints enforce data rules inside MySQL. UNIQUE stops duplicates; FK keeps references valid. CHECK limits values (MySQL 8.0.16+).

DataFlow order reference

Payment webhook retries must not create two orders with the same order_ref.

Outcome: UNIQUE constraint makes the second insert fail safely.