“Can I implement the constraint at the application layer?” is a statement that should send chills down any DBA’s spine.
The fact of the matter is, more often than not, there is a scenario where table data gets modified or added outside of the application. In a perfect world, maybe that should not be the case. But in this one, it does happen. I’m big on constraints and their use. I go out of my way to constantly express this to the developers I work with. Sometimes it gets appreciated, other times not so much. I push regardless. In this post, I will talk about constraints, some of my experiences using them, why you should use them, and share some stories and code examples.
Datatypes as a Constraint
This idea first became apparent to me when I listened to one of the Greatest oracle technologists, none other than Thomas Kyte, who was the founder of the popular question and answer site asktom, now run by other geniuses.
A simple create table statement can look like the following:
CREATE TABLE no_constraints_tab
(
person_id VARCHAR2(4000)
,person_full_name VARCHAR2(4000)
,person_birth_date VARCHAR2(4000)
,person_gender VARCHAR2(4000)
,person_SIN_nbr VARCHAR2(4000)
,person_details VARCHAR2(4000)
);
The above is a valid create table statement, but if my intention is to store people’s names in the second column and birthdate in the 3rd, gender in the 4th (M,F), then I could have used better datatypes. All of that data will fit in a VARCHAR2 as defined above without issue, but the database itself has no idea what type of data I am trying to store and thus has no way of preventing bad data from being entered.
Dates as VARCHAR2
This is one I see a lot, dates stored in VARCHAR2. I’m not quite sure the rationale behind it, but my guess is that some databases have multiple varying datatypes for storing date-time information. Some of these are nonexistent in others. In large environments where data gets moved around a lot, perhaps keeping it in a string makes it easier? I’m guessing! However, where possible store date information in a DATE defined column. There’s the possibility of wrong dates being keyed in.
VARCHAR2 with enforcing limits
A VARCHAR2 column can and should have limits, real limits, i.e., the length of the string. This is crucial as you’re ensuring that you don’t store unnecessarily long data in a column, as well as keeping your database size to a minimum. If you need a column to only store an employee_id and it can never be more than 7 alphanumeric characters. Data integrity is preserved, and there is no possibility that a longer string could be stored. When you think of data type definitions this way, then yes, they can be a type of constraint. Declaring a length for a datatype is also self-documenting as to the length of data that is required for that specific column.
Why so many kinds of numbers?
In an Oracle database, there are many columns that can be used to store a numeric value, including: NUMBER, INTEGER, BINARY_FLOAT, and BINARY_DOUBLE. Choosing the right one ensures precision and can improve computation speed. Find the documentation pertaining to all of these here. I will only talk about the number specifically, as it’s the most commonly used (and misused) datatype in Oracle.
NUMBER
This is the most commonly used numeric datatype in Oracle. It can store fixed-point and floating-point numbers with a precision and scale. The general syntax is: NUMBER(p, s)
Where:
- p (precision): The total number of significant digits.
- s (scale): The number of digits to the right of the decimal point.
For example, NUMBER(5, 2) can store numbers like 123.45, with a maximum value of 999.99.
Some of the problems
For some reason, a lot of developers believe the definition NUMBER(5, 2) can store a number like 99999.99. Inserting a value that large will throw an error in a column defined as NUMBER(5,2). Additionally, many times in data models, a number is just defined as NUMBER. This is sometimes done when the developer is unsure of how large the values/numbers are that will get inserted into the column… That in itself is a problem! When a column is defined as just a number, Oracle gives the maximum precision it supports internally (NUMBER(38)), and the scale is from -84 to +127. A column defined as NUMBER can store a value as large as 12345678901234567890123456789012345678. If you think of datatypes as a form of constraints, then this is not ideal, especially considering that for most systems, that number is unrealistic. This could also lead to precision overflow errors(ORA-01438), poor data quality, and harder validation, as well as unpredictable and inconsistent rounding of decimal places. All of these can cause data quality errors and unexpected application behaviors when doing calculations. As well, this is not very self-documenting programming.
Where possible, specify the accurate scale and precision.
Additionally, the data type may need to be an INTEGER. For example, NUMBER OF PEOPLE cannot contain decimal values (there is no such thing as 0.5 of a person). Make sure you choose an appropriate data type.
NOT NULL
NOT NULL is one of those constraints that should almost always be the default Instead of explicitly defining NOT NULL, we should be explicitly defining when a column is allowed to be NULL. Most developers dislike dealing with nulls, yet they’re scattered across almost every database. Worse, the general understanding of how nulls actually behave is often shaky.
Consider this post from LinkedIn:
There are many more posts like this out there. I see it all the time on X as well. One of the best replies I’ve ever seen came from a discussion about an age column. Someone asked something like: “If my age is null and your age is null, why can’t null = null?” And the response captured the essence of nulls perfectly:
“If you don’t know my age and I don’t know your age, that does not mean that you and I are the same age”
I couldn’t put it better!
Use the NOT NULL constraint to avoid dealing with nulls where they don’t belong. If a column should always have a value, then enforce that rule with a NOT NULL constraint. Treat NOT NULL as the standard and only allow NULL when there is a clear, justified reason.
Relationships for better performance
In the overwhelming majority of OLTP systems, foreign keys should exist. It could be one of the single biggest causes for poor data quality in OLTP and data warehouse environments. If your team is running data correction scripts after ETL jobs, for example… You may need to make use of this constraint. It’s not enough to know the relationship between your tables and draw it on an ER diagram. The problem with that is that knowledge is in your head and all of the developers’ heads, and perhaps in your ER diagram. However, the database has absolutely no idea what links to what… that metadata is invaluable. Not defining foreign keys can lead to issues such as orphaned records, invalid IDs, broken relationships, and inconsistent data across tables. This can help prevent bad data from getting into the database regardless of the application layer. Besides enforcing referential integrity, foreign keys also provide valuable metadata that Oracle’s optimizer can use during query optimization. Define foreign keys; do not leave the database in the dark. More so, define the relationship in the database! The data is here to stay; the application may not be(I touch on this later below). However, don’t take my word for it : asktom.oracle.com.
Keep names consistent
Consistent naming is an often overlooked aspect of database design, but it pays dividends over the lifetime of a system. If multiple columns represent the same business concept, they should, wherever practical, have the same name throughout the database.
For example, avoid using employee_id, employee_number, employee_nbr, emp_id, emp_no, and employee_no interchangeably when they all refer to the same identifier. While the database can function perfectly well with inconsistent names, developers, DBAs, reporting tools, and future maintainers all benefit from a consistent naming convention.
There are, of course, legitimate exceptions. A column such as manager_id may reference employee.employee_id via a foreign key, but it represents a different role within the relationship and therefore deserves a different name. Outside of such cases, if two columns represent the same attribute, give them the same name. Consistency reduces cognitive overhead, improves readability, and makes SQL easier to write, understand, and maintain.
Everything is unique: think about it!
One of the earliest lessons I learned as a DBA came from a senior colleague who told me, “Every table should have a key.” At the time, it didn’t make sense. I could imagine plenty of situations where a table didn’t seem to need one. But over the years, the truth of that statement became obvious.
Tables store data, and data is information. Information is never random. The whole point of a relational database is to efficiently organize this information. Therefore, if you have a table storing some information, then it’s specific; it’s about something… What is that thing? Therein lies the answer, and that should be your key! Think about it, it’s the fundamental data integrity constraint. With relatively few exceptions (such as transient staging or logging tables) every relational table should have a primary key.
I am not nearly as smart as the experts on asktom who also state this, take it from them.
Always use UUIDS/SEQUENCES ?
There seems to be a growing opinion (I see lots of posts on social media stating it) that every primary key column should be a sequence/incremental numeric value. I am not sure where this school of thought originated from, but here’s my take based on my experience working with databases: “That ain’t true!”
Every feature has its place.
If there is a column in your data that uniquely identifies what the data is about, for example, the ISBN of a book, then that is a natural key, and natural keys are perfectly suited to be primary key columns.
The keys that are “artificially introduced,” so to speak, are surrogate keys. They have no business meaning other than to uniquely identify a row. There are benefits to a numeric counter. However, both natural and surrogate keys have their place. As a matter of fact, there isn’t any feature of the database that doesn’t have a valid use case. Depending on the scenario both are valid and useful.
All in all, data is always about something; that something has a unique grain, make use of primary keys, and ensures that the grain stays unbroken.
Check Constraints and Assertions
Check constraints prevent invalid values:
- cardinal directions:
IN ('N','S','E','W') - gender codes
- status flags
- domain values
They ensure the data is correct before it ever reaches the application.
Oracle 26ai introduces Assertions, which extend integrity rules across tables, something previously only achievable with triggers. Assertions are cross‑table constraints, and they reinforce the idea that integrity belongs in the database, not scattered across application code.
Do it in the Database, please !
If you “have” to enforce a constraint in the application, your data model is probably wrong.
Constraints belong in the database because:
- the database is the source of truth
- applications change, rewrite, migrate, and die
- data persists
- constraints are optimized, reliable, and built‑in
- they prevent bad data regardless of where it comes from
Re‑implementing constraints in application code is reinventing the wheel and badly so.
USE COMMENTS
Oracle’s COMMENT feature is criminally underused. A simple explanation of a column’s purpose or a table’s intent can save hours of onboarding time and prevent misinterpretation.
Oracle 26ai goes further with annotations that help AI agents understand your data model. Use them. Metadata is part of integrity.
CONCLUSION
Constraints are the foundation of trustworthy data. Every time a team chooses to “handle it in the application,” they’re betting the integrity of their entire system on the hope that every piece of code, every developer, every integration, and every future rewrite will behave perfectly forever. That bet never pays out.
A relational database already gives you the tools to guarantee correctness: datatypes that mean something, keys that define the grain of your data, foreign keys that preserve relationships, checks that enforce business rules, and now assertions that extend integrity across tables. These are battle‑tested, optimized, and built directly into the engine that stores your data.
If the data matters, the constraints belong in the database. They will outlive every application, every framework, and every trend. In the end, constraints aren’t there to restrict you, they’re there to protect the only thing that truly persists: the data itself.