Domain Constraints in DBMS: Types, SQL Examples & Interview Guide

Domain constraints in DBMS define the valid set of values that an attribute can store in a database table. They matter because one invalid value, such as a negative UPI amount or an incorrectly formatted PAN, can break reports, workflows, and application logic. After reading, you can design, implement, and explain domain rules confidently.

A domain in DBMS sits at the base of data integrity: before checking relationships, keys, or transactions, the database must first decide whether each column value is legal. Domain integrity is one part of broader Integrity Constraints in DBMS, used by developers, DBAs, analysts, and interviewers.

You will be able to identify domain violations, choose the right SQL constraint, write production-ready checks, and answer GATE or interview questions on domain in database design without confusing it with key or referential constraints.

---

Who This Guide Is For

This guide is specifically designed for:

---

Core Concepts

Domain constraints in SQL are implemented through column data types, NOT NULL rules, CHECK predicates, DEFAULT values, set-membership checks, pattern validation, and user-defined domains where supported. They overlap with Constraints in SQL, but they are narrower: their main job is to protect the legal value space of an attribute, not to define relationships between rows or tables.

1.Domain and Attribute

A domain is the universe of legal atomic values for an attribute. If a table has a column named payment_amount, its domain may be “decimal numbers greater than zero and less than or equal to ₹200,000.” If a table has blood_group, its domain may be the fixed set A+, A-, B+, B-, AB+, AB-, O+, and O-. The attribute is the column; the domain is the rule set behind valid values.

Familiar example: an Aadhaar number is expected to be a 12-digit identifier, so a column storing it should not accept alphabets or random-length strings. Industry-specific example: a hospital triage system may allow only defined severity levels such as LOW, MEDIUM, HIGH, and CRITICAL, because downstream alerting and bed allocation depend on exact categories.

The mental model is a gate at the column boundary. Before the value becomes part of the database state, the DBMS checks whether it belongs to the column’s allowed set. This makes domain integrity constraints in DBMS stronger than UI validation alone, because every insert path is protected: web app, API, import script, admin console, or scheduled job.

A standard GATE-style question asks: “What is a domain in the relational model?” The expected answer is: a domain is a set of permitted atomic values for an attribute; it is not the same as a table, key, or relationship.

Code Example

2.Data Type Rules

Data type is the first and most basic domain constraint. It tells the DBMS whether the value is an integer, decimal, date, timestamp, boolean, character string, binary object, JSON document, or another supported type. Without a proper data type, a database may store values that look acceptable in the application but fail during sorting, aggregation, indexing, or comparison.

Familiar example: a PAN card value should be stored as fixed-length text, not as a number, because it contains letters and leading characters are meaningful. Industry-specific example: a SaaS billing platform should store invoice totals as NUMERIC or DECIMAL, not floating-point, because financial values require exact decimal precision.

Strong type selection reduces accidental values before CHECK constraints even run. A DATE column rejects random text that is not a valid date. A BOOLEAN column prevents unclear values such as 'yes', 'Y', 'active', and 1 from being mixed unless the DBMS explicitly maps them. This is why domain constraints in dbms start with correct type design, not with decorative validation rules.

Choose the most specific data type that matches the business meaning. Do not store dates, money, flags, or identifiers as generic VARCHAR just because it is convenient during early development.

Code Example

3.Nullability Rules

Nullability controls whether a column can store NULL, which represents missing, unknown, or inapplicable data. A NOT NULL constraint is a domain integrity rule because it excludes NULL from the set of allowed values. This is different from an empty string, zero, or false; those are actual values, while NULL means no value has been stored.

Familiar example: a UPI transaction table should not allow a missing transaction amount, because settlement, reconciliation, and audit trails require a known amount. Industry-specific example: a logistics platform may require delivery_pincode for domestic shipments, because route assignment cannot be calculated without it.

Use NOT NULL when the business process cannot proceed without a value. Avoid using fake placeholders such as 'NA', 'unknown', 0, or 1900-01-01 to bypass nullability. Such placeholders pollute analytics and force every query to remember hidden exceptions. Domain in database design should express truth, not hide missing information behind misleading values.

A common mistake is treating NULL as equal to zero or an empty string. In SQL, NULL means unknown or absent; comparisons such as column = NULL do not work as normal equality checks.

Code Example

4.Range Checks

Range constraints restrict values to a valid interval. They are usually implemented with CHECK constraints using operators such as >, >=, <, <=, and BETWEEN. Ranges are common for money, age, quantity, ratings, dates, percentages, temperatures, and capacity values. A data type can say that a column is numeric, but only a range rule can say which numeric values make sense.

Familiar example: an IRCTC-style booking system should not allow passenger age as -5 or 250. Industry-specific example: a healthcare lab system may restrict oxygen saturation percentage to a medically possible range, such as 0 to 100, while marking clinically abnormal values for review elsewhere.

Range rules should be based on business and physical reality. For example, an e-commerce quantity may be between 1 and 99 for retail orders, but a wholesale procurement system may allow much larger values. The correct domain constraint depends on the process, not only on the SQL type. If a value is technically storable but logically impossible, the database should reject it.

For exams, remember the distinction: INTEGER restricts storage type, while CHECK(age BETWEEN 0 AND 120) restricts the valid domain inside that type. Both together form stronger domain integrity.

Code Example

5.Set Membership

Set membership constraints restrict a column to one value from a fixed list. They are useful when the business vocabulary is small, stable, and meaningful. Common examples include order status, KYC status, ticket priority, payment mode, subscription tier, gender values where required by policy, tax category, and account type. In SQL, this is commonly implemented with CHECK (column IN (...)).

Familiar example: a Zomato-like order system may allow order status values such as PLACED, ACCEPTED, OUT_FOR_DELIVERY, DELIVERED, and CANCELLED. Industry-specific example: a banking loan workflow may restrict risk band to LOW, MEDIUM, HIGH, and REJECT, so credit policy reports do not get fragmented by spelling variations.

Set membership rules prevent “almost same” values from entering the database, such as Delivered, DELIVERD, done, and complete. If values change frequently or need metadata, use a lookup table instead of a hard-coded CHECK list. If values are stable and small, a CHECK list is simple and fast to understand.

Use CHECK IN for small stable sets. Use a reference table and foreign key for large, changing, or metadata-rich sets such as city codes, product categories, or branch lists.

Code Example

6.Format Patterns

Format constraints validate the structure of text values. They are used for identifiers, codes, emails, phone numbers, IFSC-like values, coupon codes, vehicle numbers, and user handles. A text column alone only says “store characters”; a format rule says “store characters in this exact shape.” These constraints are especially useful when values are later matched, searched, masked, or integrated with external systems.

Familiar example: a PAN-style value follows a fixed pattern of five uppercase letters, four digits, and one uppercase letter. Industry-specific example: an ed-tech platform may require batch codes such as DSA-2026-MUM, where the prefix identifies the program, the year identifies the cohort, and the suffix identifies the city.

SQL pattern support differs across DBMS products. PostgreSQL supports POSIX regular expression operators such as ~. Standard SQL has LIKE and SIMILAR TO in some systems. MySQL 8.0.16 and later enforces CHECK constraints, but regex syntax and CHECK behavior differ by engine and version. Keep database-level format checks for stable structural rules, and keep complex country-specific validation logic in trusted services when rules change often.

Do not overclaim that every DBMS supports the same regex syntax inside CHECK. PostgreSQL, MySQL, SQL Server, and Oracle differ, so production schemas must use the syntax supported by the chosen database.

Code Example

7.Default Values

A DEFAULT constraint supplies a value when an insert statement does not provide one. It is a domain-related rule because the generated value must still belong to the permitted domain. Defaults are useful for timestamps, initial statuses, boolean flags, counters, currency codes, and audit metadata. They reduce repetitive insert code and make initial states consistent.

Familiar example: a newly created wallet account may default to ACTIVE only after policy permits immediate activation; otherwise it may default to PENDING_KYC. Industry-specific example: a SaaS support ticket may default to OPEN and MEDIUM priority so queues work even if the user submits a minimal form.

A default is not a substitute for a required business decision. If a value must be consciously chosen, do not hide the decision behind a default. For example, defaulting a medical allergy flag to false can be unsafe if the patient was never asked. Good defaults represent safe, common, and truthful initial states.

DEFAULT is applied when the column is omitted from INSERT or when DEFAULT is explicitly used. It does not automatically replace an explicit NULL unless the DBMS has special behavior or additional constraints.

Code Example

8.User Defined Domains

A user-defined domain is a named reusable domain that combines a base type with constraints. SQL standard supports domains, and PostgreSQL implements CREATE DOMAIN with options such as base type, DEFAULT, NOT NULL, CHECK, and collation support. Other systems vary; for example, MySQL does not provide the same CREATE DOMAIN feature.

Familiar example: if multiple tables store Indian mobile numbers, creating a reusable indian_mobile_number domain avoids copying the same pattern check across customers, delivery partners, and support agents. Industry-specific example: a fintech system may define a reusable positive_money domain for wallet loads, refunds, cashback, and fee records.

User-defined domains improve consistency and maintainability. If the rule changes, it can be managed centrally in DBMSs that support domain alteration safely. They also make schema intent readable: amount positive_money communicates more than amount NUMERIC(12,2). For interview answers, mention both the conceptual benefit and the practical limitation that support differs by DBMS.

If asked “How can domain constraints be reused?” answer with user-defined domains where supported, or reusable CHECK patterns and reference tables where CREATE DOMAIN is unavailable.

Code Example

9.Tuple Level Checks

A tuple-level CHECK validates a rule that depends on multiple columns in the same row. Strictly speaking, this goes beyond a single-column domain because the valid value of one attribute depends on another attribute. Still, DBMS courses often discuss it near domain constraints because it is implemented with the same CHECK mechanism and protects row-level correctness.

Familiar example: in a hotel booking table, checkout_date must be after checkin_date. Industry-specific example: in a healthcare insurance claim, approved_amount should not exceed claimed_amount, and both should remain non-negative. A single data type cannot express this relationship; a table-level predicate is required.

Use tuple-level checks for rules that can be evaluated using values in the current row. Do not use CHECK for rules that need to search other rows or other tables; those belong to keys, foreign keys, triggers, assertions where supported, or transaction logic. This distinction helps separate domain integrity from entity integrity and referential integrity, covered in more detail in Integrity Constraints in DBMS: Types, Examples & SQL Code.

A CHECK constraint should be deterministic for the row being inserted or updated. Cross-table validations are not ordinary domain constraints; use foreign keys, triggers, or controlled transactions.

Code Example

10.Not Domain Constraints

Some SQL constraints are column-level in syntax but not domain constraints in meaning. Primary key and unique constraints enforce entity identity. Foreign keys enforce referential integrity between tables. These constraints are essential, but they do not define the allowed value set of a single attribute in the same way that data type, NOT NULL, CHECK, DEFAULT, or CREATE DOMAIN rules do.

Familiar example: a vehicle registration number may have a format domain rule, while uniqueness of that registration number is an entity rule that prevents duplicate vehicles. Industry-specific example: a banking account table may restrict account type to SAVINGS or CURRENT as a domain rule, while a foreign key from transactions to accounts ensures every transaction references an existing account.

Do not call every constraint a domain constraint. UNIQUE, PRIMARY KEY, and FOREIGN KEY may be written near columns, but their main purpose is identity or relationship integrity, not value-domain validation.

Code Example

Domain constraints answer one question: “Does this attribute value belong to its allowed domain?” If the rule is about uniqueness, row identity, or matching another table, it belongs to another integrity category.

---

Learning Path

Use this path to move from textbook definitions to production-ready schema design and interview-level reasoning.

---

Frequently Asked Questions

What is Domain Constraints in DBMS?

Domain constraints in DBMS are rules that restrict the values allowed in a table column. They protect domain integrity by ensuring values have the correct type, range, format, nullability, default behavior, or allowed category before they are stored.

What is domain in DBMS?

A domain in DBMS is the set of valid atomic values for an attribute. For example, the domain of a rating column may be integers from 1 to 5, while the domain of a status column may be only OPEN, RESOLVED, or CLOSED.

What is the difference between domain constraint and integrity constraint?

Integrity constraint is the broader category that includes domain integrity, entity integrity, referential integrity, and other rules. Domain constraint is one specific type of integrity constraint that focuses on whether an attribute value is valid for its column.

How are domain constraints in SQL implemented?

Domain constraints in SQL are implemented using data types, NOT NULL, CHECK, DEFAULT, and user-defined domains where supported. Some DBMSs also provide enum-like types or regex functions, but syntax varies across PostgreSQL, MySQL, SQL Server, and Oracle.

Is CHECK constraint a domain constraint?

Yes, a CHECK constraint can implement a domain constraint when it restricts allowed values of a column, such as salary >= 0 or status IN ('OPEN','CLOSED'). A table-level CHECK involving multiple columns is closer to a row-level integrity rule, although it is implemented using the same SQL mechanism.

Is foreign key a domain constraint?

No, a foreign key is a referential integrity constraint. It ensures that a value in one table matches an existing key value in another table, while a domain constraint checks whether the value itself is valid for its attribute.

When should I use CHECK instead of a lookup table?

Use CHECK when the allowed values are small, stable, and do not need extra metadata. Use a lookup table when values change often, need descriptions, require ordering, support localization, or are managed by business users.

What is a common mistake in domain constraints?

A common mistake is relying only on frontend or API validation and leaving the database unprotected. Another mistake is storing everything as VARCHAR, which weakens type safety and makes reporting, indexing, and comparison more error-prone.

---

Interview Preparation

Interviewers ask domain constraint questions to check whether you understand database correctness at schema level, not just query writing. Answer with the definition first, then give one SQL mechanism and one practical example.

Conceptual Questions

• Define domain constraint in one sentence: A domain constraint restricts an attribute to values from its valid domain. Examples include an age column accepting only realistic integers or a payment status column accepting only approved lifecycle values.
• How is domain integrity different from entity integrity? Domain integrity validates column values, while entity integrity ensures each row can be uniquely identified, usually through a primary key. A salary being non-negative is domain integrity; an employee ID being unique is entity integrity.
• Why should validation exist in the database if the application already validates input? Applications are not the only write path. Imports, admin scripts, integrations, migrations, and direct SQL access can bypass application validation, so database constraints provide a final protection layer.
• Can DEFAULT guarantee correct data? No. DEFAULT only supplies a value when one is omitted; it does not prove that the default is correct for every business situation. The chosen default must be safe, meaningful, and inside the allowed domain.

Applied / Problem-Solving Questions

• Design a constraint for product discount percentage: Use a numeric type and CHECK constraint such as discount_percent BETWEEN 0 AND 100. Add NOT NULL only if every product must always have a discount value, otherwise allow NULL to mean “not specified.”
• A table stores order statuses with spelling variations. How would you fix it? Clean existing values, choose a canonical set, and add CHECK (status IN (...)) or a lookup table with a foreign key. Use a lookup table if statuses are configured by operations teams or need descriptions.
• How would you validate a date range in a booking table? Use a table-level CHECK such as checkout_date > checkin_date. Also choose DATE or TIMESTAMP types instead of storing dates as text.
• What should you do when the same phone-number rule appears in many tables? In PostgreSQL, create a reusable domain with CREATE DOMAIN. In DBMSs without domain support, standardize the CHECK expression through migrations, shared schema templates, or a reference validation layer plus database checks.

The most tested question is: “Classify domain, entity, and referential integrity.” Standard answer: domain restricts valid attribute values, entity ensures unique row identity, and referential ensures valid references between tables.

---

Key Takeaways

Domain constraints in DBMS protect the legal value space of each attribute. The concrete tools are data types, NOT NULL, CHECK, DEFAULT, set membership rules, format checks, and user-defined domains where supported. Good domain design prevents invalid values such as negative amounts, impossible dates, misspelled statuses, and malformed identifiers from entering the database.

For GATE and interviews, the most tested points are the definition of domain, the difference between domain integrity and other integrity constraints, and the SQL mechanisms used to enforce domain constraints in sql. Be ready to explain why primary keys and foreign keys are not domain constraints, even though they are vital database constraints.

The natural next step is Constraints in SQL, because it expands from domain-level validation to the full set of SQL constraints used in practical schema design.

---

Further Reading

• Constraints in SQL, Learn how NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT constraints work together.
• Integrity Constraints in DBMS, Review the broader integrity categories used in relational database theory.
• Integrity Constraints in DBMS: Types, Examples & SQL Code, Practise integrity constraint classification with examples and SQL snippets.