Get the Preplance app for a seamless learning experience. Practice offline, get daily streaks, and stay ahead with real-time interview updates.
Get it on
Google Play
4.9/5 Rating on Store
SQL & Databases · Question Set
Constraints, Keys & Data Integrity interview questions for placements and exams.
Questions
15
Included in this set
Subject
SQL & Databases
Explore more sets
Difficulty
Mixed
Level of this set
Go through each question and its explanation. Use this set as a focused practice pack for SQL & Databases.
Deferrable constraints allow complex multi-step changes that temporarily violate rules within a transaction. The final state must pass checks at commit. Use this for bulk swaps or cycle updates in parent–child graphs.
ALTER TABLE t ADD CONSTRAINT fk DEFERRABLE INITIALLY DEFERRED;
For complete preparation, combine this set with full subject-wise practice for SQL & Databases. You can also explore other subjects and sets from the links below.
A surrogate key decouples identity from volatile business fields and keeps foreign keys narrow and stable. The cost is another column and an extra uniqueness rule if you still need to enforce natural key uniqueness.
CREATE TABLE products ( id BIGSERIAL PRIMARY KEY, sku TEXT UNIQUE );
CHECK validates each row against a condition. It blocks inserts and updates that violate business rules. Use it for domains like positive amounts or enumerated statuses.
amount NUMERIC(12,2) CHECK (amount >= 0)
DEFAULT supplies a value when the insert omits that column. It does not run if you pass an explicit value, including NULL. Combine NOT NULL with DEFAULT for robust domain rules.
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
When a parent row changes or is deleted, the engine must verify or locate matching child rows. Without an index, it scans the whole child table, which is slow and holds locks longer. That increases contention and deadlock risk. An index on the foreign key lets the engine seek directly to the children. It speeds joins, shortens lock durations, and keeps hot paths predictable under load.
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
A primary key uniquely identifies each row and forbids NULL by definition. It is also the default target for foreign keys. UNIQUE enforces distinct values but often allows one or more NULLs depending on the DBMS. You can define multiple UNIQUE constraints per table.
CREATE TABLE users ( id BIGINT PRIMARY KEY, email TEXT UNIQUE );
A foreign key enforces referential integrity. Values in the child column must exist in the referenced parent key. This prevents orphan records and maintains valid relationships as data changes.
CREATE TABLE orders ( id BIGINT PRIMARY KEY, customer_id BIGINT REFERENCES customers(id) );
SET NULL preserves the child record but clears the foreign key, which is valid only if the column allows NULL. CASCADE deletes the children, RESTRICT/NO ACTION prevents the parent delete when children exist.
customer_id BIGINT REFERENCES customers(id) ON DELETE SET NULL
Composite keys model identities like (order_id, line_no) or (country_code, phone). They encode business meaning and enforce pair uniqueness. They do add key width to child tables. Consider a surrogate key if many foreign keys must reference the row.
PRIMARY KEY (order_id, line_no)
A CHECK with an IN list validates values at the database boundary and works across vendors. Vendor-specific enums can be ergonomic, but CHECK is simple and portable.
status TEXT NOT NULL CHECK (status IN ('NEW','PAID','CANCELLED'))Use declarative constraints first. They are simple, automatic, and optimized by the engine. Examples include NOT NULL, UNIQUE, CHECK, and FOREIGN KEY. They are easy to reason about and are validated by the optimizer. Triggers are for rules that require procedural logic across rows or tables, audit trails, or derived writes. They add complexity and can surprise callers if not documented. Prefer constraints for static invariants; use triggers sparingly for cross-row logic you cannot express declaratively.
ALTER TABLE invoices ADD CONSTRAINT chk_amt CHECK (total >= 0); -- Trigger only if you need side-effects like audit rows
Sequences are independent objects you can reuse across tables, set start and increment, and alter without touching table DDL. They work well when you need gaps handled explicitly or cross-table ordering. Identity columns are convenient table-local generators. They reduce boilerplate but are less flexible to share or reset. Choose sequences for cross-table coordination or when your DBMS lacks uniform identity semantics; use identity for simple, per-table keys.
CREATE SEQUENCE seq_id START WITH 1;
CREATE TABLE t (id BIGINT PRIMARY KEY DEFAULT nextval('seq_id'));A plain UNIQUE often allows multiple NULLs, which is the desired behavior. If your engine treats NULLs as equal, emulate with a filtered or partial unique index on non-NULL rows. That enforces uniqueness only when a value exists. This pattern keeps flexibility for missing values while preventing duplicates when the field is provided.
-- PostgreSQL CREATE UNIQUE INDEX uniq_users_email ON users(email) WHERE email IS NOT NULL;
In 1NF you store atomic values, so lists do not cause duplication or parsing. In 2NF you remove partial dependency on part of a composite key, which stops unrelated attributes from repeating across rows. In 3NF you remove transitive dependencies so non-keys depend only on the key. For example, storing customer_city in the orders table causes update anomalies if the city changes. Moving customer attributes to the customers table and referencing it by a foreign key keeps one source of truth and avoids inconsistent edits.
CREATE TABLE customers(id PK, name, city); CREATE TABLE orders(id PK, customer_id FK REFERENCES customers(id));
CASCADE can remove deep trees. Wrap deletes in a transaction, check impact with a SELECT first, and delete in small batches if needed. Keep foreign keys explicit and ensure only intended users can perform destructive actions.
BEGIN; -- preview SELECT COUNT(*) FROM orders WHERE customer_id = :id; -- then delete parent DELETE FROM customers WHERE id = :id; COMMIT;