ACID

Demystifying SQL Constraints with Oracle Live SQL
Hey Devs

I’ve been diving into Oracle Live SQL lately, and it’s been a fascinating ride through the world of data integrity and transaction control. Thought I’d share a few nuggets from my journey—especially around constraints and how they shape reliable databases.

Why Constraints Matter
When designing tables, constraints are your first line of defense against bad data. Here's a simple example I used:

sql
CREATE TABLE ACCOUNTS (
account_id NUMBER,
account_type VARCHAR2(20),
balance NUMBER,
CONSTRAINT acc_pk PRIMARY KEY (account_id),
CONSTRAINT acc_balance_ck CHECK (balance >= 0) -- No negative balances!
);
This ensures:

Every account has a unique ID.

No one can sneak in a negative balance (because chaos isn’t a valid business model).

Transactions: Merge or Mayhem?
I also played around with inserting duplicate records and wrapping them in a transaction block. It’s a great way to test how your logic handles real-world scenarios like merging balances or rejecting duplicates.

sql
BEGIN
INSERT INTO Accounts VALUES ('David', 3000);
INSERT INTO Accounts VALUES ('David', 4000);
END;
Depending on your schema design, this could either:

Merge balances intelligently

Or throw a constraint violation

Pro Tip: Test Minimal Inserts
Try inserting a record without all required fields and see how your constraints react:

sql
INSERT INTO Accounts (acc_no, name)
VALUES (101, 'Pranav');
If your table requires a balance, this should fail—or default—depending on how you've set it up.