Imagine you are building an order management system.
Your "customers" table contains 10,000 customers. Your "orders" table contains 500,000 orders.
Everything works perfectly.
Then one day, an application inserts an order for "customer_id = 99999".
The problem? Customer 99999 does not exist.
Your database now contains an order that cannot be linked to a valid customer.
Your reports may become unreliable. Your joins may not return the expected information. Your team may spend hours investigating a data-quality problem that could have been prevented.
The solution is not another SQL query. It is a database constraint.
Let's explore how SQL developers prevent these problems and learn some common SQL interview traps along the way.
- CREATE TABLE: Start With the Right Structure
First, create a customer table.
CREATE TABLE customers (
customer_id NUMBER PRIMARY KEY,
customer_name VARCHAR2(100) NOT NULL
);
Notice two important constraints:
- "PRIMARY KEY" ensures that every customer has a unique, non-NULL identifier.
- "NOT NULL" prevents a customer record from being inserted without a name.
Now create an orders table.
CREATE TABLE orders (
order_id NUMBER PRIMARY KEY,
customer_id NUMBER,
order_amount NUMBER(10,2)
);
At first glance, everything looks fine.
But there is a hidden problem.
Nothing in this table definition prevents an order from referencing a customer who does not exist.
How do we fix it?
- FOREIGN KEY: Protect Table Relationships
Add a foreign key.
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id);
Now the database enforces the relationship.
If someone tries to insert an order with a non-NULL customer ID that does not exist in "customers", the database rejects the operation.
Why is this important?
Because your database should not depend entirely on every application developer remembering to validate every relationship correctly.
Interview takeaway: A foreign key enforces referential integrity at the database level.
One important detail: a nullable foreign key can contain "NULL" unless the column is also defined as "NOT NULL".
- ALTER TABLE: Change Structure Without Recreating the Table
Business requirements rarely remain the same forever.
Suppose you now need to store customer email addresses.
Do you need to drop the table and create it again?
No.
ALTER TABLE customers
ADD email VARCHAR2(150);
The new column is added to the existing table, and existing rows are retained.
You can also use "ALTER TABLE" to add or remove constraints and modify columns, subject to database-specific rules and existing data.
In production, always review dependencies before changing a schema. A column modification or removal can affect reports, stored procedures, applications, and downstream data pipelines.
- DROP vs TRUNCATE vs DELETE: The SQL Interview Trap
An interviewer asks:
"You have a table containing 10 lakh records. You want to remove all records but retain the table structure. Which command would you use?"
Many candidates immediately answer "DELETE".
But let's understand the differences.
DROP — Remove the Table
DROP TABLE orders;
This removes the table itself, including its data and associated table objects such as indexes and constraints.
Use it only when the table is no longer needed.
TRUNCATE — Remove All Rows
TRUNCATE TABLE orders;
This removes all rows while retaining the table structure.
There is no "WHERE" clause, so you cannot use it to remove only selected records.
DELETE — Remove Selected Rows
DELETE FROM orders
WHERE order_amount < 1000;
This removes only rows matching the specified condition.
You can also delete all rows by omitting the "WHERE" clause.
Important Oracle interview tip: DDL statements such as "DROP" and "TRUNCATE" have implicit-commit implications. Do not assume you can roll them back like an ordinary uncommitted "DELETE".
Always verify the target table and understand the transaction behavior before running destructive commands.
- COMPOSITE PRIMARY KEY: When One Column Is Not Enough
Consider an order-items table.
A single order can contain several products, and the same product can appear in different orders.
CREATE TABLE order_items (
order_id NUMBER,
product_id NUMBER,
quantity NUMBER,
unit_price NUMBER(10,2),
PRIMARY KEY (order_id, product_id)
);
Why use two columns in the primary key?
Because the combination of "order_id" and "product_id" identifies each row.
For example:
- "(101, 10)" identifies product 10 in order 101.
- "(101, 20)" identifies product 20 in order 101.
- "(102, 10)" identifies product 10 in order 102.
These combinations are different.
But inserting "(101, 10)" a second time would violate the composite primary key.
This concept is useful when designing junction tables, order details, and many-to-many relationships.
- CHECK CONSTRAINT: Prevent Invalid Values
Suppose an order amount must never be negative.
You can enforce that rule at the database level.
ALTER TABLE orders
ADD CONSTRAINT chk_order_amount
CHECK (order_amount >= 0);
Now an insert or update that violates the constraint is rejected.
One subtle detail: a "CHECK" constraint alone does not reject "NULL" values. If the amount must also be present, define the column as "NOT NULL" or add an appropriate constraint.
This is the kind of detail that separates memorizing SQL syntax from understanding how database rules actually work.
Your SQL Interview Challenge
Before you leave, try this question without looking up the answer.
You have two tables:
- "customers", containing customer information.
- "orders", containing customer orders.
You must prevent an order from referencing a customer who does not exist.
Which solution is best?
A. Create an index on "orders.customer_id".
B. Add a foreign key referencing "customers.customer_id".
C. Use "ORDER BY customer_id".
D. Add a "CHECK" constraint requiring "customer_id > 0".
Pause and decide before reading further.
Correct answer: B — Add a foreign key.
An index can improve access performance, but it does not enforce the relationship. Sorting rows does not enforce data integrity, and checking that an ID is positive does not establish that the customer exists.
A foreign key directly enforces the required relationship.
The key principle is simple:
Good database design makes invalid data harder to create.
Want to Master SQL DDL for Interviews?
We've covered the fundamentals, but there is much more to learn.
What happens when you need a many-to-many relationship? When should you use "ON DELETE CASCADE"? How do you modify existing constraints safely? What mistakes do interviewers expect SQL developers to avoid?
I've brought these topics together in a detailed guide on Sankalan Data Tech.
The complete tutorial covers:
- "CREATE", "ALTER", "DROP", and "TRUNCATE" with practical examples
- Primary keys, foreign keys, unique constraints, and check constraints
- One-to-many and many-to-many relationships
- Composite primary keys and junction tables
- Constraints versus triggers
- Common interview mistakes and follow-up questions
- 21 SQL interview questions with answers
👉 Continue learning with the complete SQL DDL guide:
If you are preparing for a SQL developer, Oracle PL/SQL, or data engineering interview, work through the examples and understand the reasoning behind each constraint instead of simply memorizing syntax.
Save this post for revision, and test yourself on the interview questions in the full guide.
Top comments (0)