Hello and welcome to the Database programmer blog. This is
a blog for anybody who wants to learn about databases on
their own terms and find out how database decisions affect
their applications.
Constraints are probably the least talked about topic in
application and database design. I suspect that there is
something deep in the souls of programmers that just does
not like to talk about limitations. We like to talk about
flexibility, extensions, abstraction and so forth. Constraints
rub us wrong. But of course we also love to write absolute
rules for ourselves and others (thou shalt always use integer
primary keys, thou shalt not use HTML TABLE elements, and
so on). So go figure.
If you want to make a deadline, you have to limit what
you do. You cannot make a list a kilometer long and finish it
in a day. By the same principle, if a database is going
to accurately store data, there must be rules
about what makes the data correct. These rules we call
constraints.
This essay is part of the Keys, Normalization and Denormalization series.
There is also a Complete Table of Contents and a >Skills-oriented Table Of Contents.
Table Structure is The First Constraint
Table structure is not usually listed as a constraint,
but your table structures represent the first and most
basic constraints on your application.
The table structure can be pictured as the mission
statement of the application. If you have a table of
students, we can safely assume you must record certain
facts about students. Likewise, if you do not
have a table of Nobel Prize Winners, we can safely
assume that Novel Laureates are outside the scope of
your application.
This basic fact, that table structure represents
a codification of design decisions, is worth getting
into a little deeper.
Where Do You Want The Zebra?
The importance of table structures can be seen with
an analogy. Imagine you purchase a building
and some printing equipment, and then you hire some
people and create a printing shop. Somebody comes
in and says, "Where do you want this zebra?" The answer
is that you have no need or use for a zebra in a
printing shop, and it will actually get in the way and
cause problems. So the answer is "Not here!" There
is a constraint on the use of your building, which
is that wild game is not welcome.
Database tables represent that decision to commit
to a certain task or collection of tasks. They constrain
the end users to perform the mission of the company,
and not some other mission.
Sometimes the more naive programmers will not give up
on this point, and they will respond with something like
"Well, yes, but computers do not have the limitations
of physical systems, they are so much more flexible."
This is true but wrong. A computer language like
C++ is incredibly flexible in its potential, but
that potential is worth nothing until somebody sits
down and writes code to do a specific task, at which
point that code is good only for that task. In other
words, the general purpose value of the computer
is only realized when dedicated to a specific purpose.
Likewise, a database server is in principle capable
of storing data about anything, but is only useful
once decisions have been made to create specific
tables to store specific things.
To say it one more time, table designs are constraints
because they say what will be tracked and what will not
be tracked.
Primary Keys, Unique Constraints and Foreign Keys
The next category of constraints have been well
discussed on this blog, and I do not want to spend too
much time on them here, but we will do a quick review.
Your table designs are the foundation of your application.
In simple terms, a well normalized database will have
a table for every different kind of thing that must
be tracked (students, courses, classrooms, etc).
In addition, there are always plenty of
cross references that list relationships between these
primary entries (teacher to courses they are qualified
to teach), and transactions that represent interactions
between these (which courses a student has completed).
The primary key makes sure that there are no duplicates,
and the foreign key ensures that data stored in separate
tables makes sense. For instance, imagine a table that
lists which courses each teacher is qualifed to teach.
It would not make much sense to list teachers in this
table that are not on the faculty, and the foreign key
prevents these kinds of mistakes.
As a veteran database designer I am still often astonished
at how many seemingly complicated and unique business
requirements reduce to a simple collection of tables,
with their primary and foreign keys. I have seen this
happen so many times that I am now automatically skeptical
when somebody tells me they have 'special needs' that
probably will not fit into simple tables. They always
end up fitting into tables.
Check Constraints
Once you have your tables and their keys, there is
one more kind of constraint you can use, which is
called the CHECK constraint. A check constraint is
nothing more than some SQL expression that must always
be true, and is attached either to a column or a table.
MySQL does not support CHECK constraints as of
version 5.0. This is one of the reasons why users of
other database systems often say nasty things about
MySQL.
A typical example for a check constraint is that
a discount price must be lower than a regular
price. Another use is to make sure that a number
is between one and 100, so that it can be used
as a percentage. This bit of SQL illustrates
these ideas in the PostgreSQL dialect:
CREATE SEQUENCE orderlines_line
CREATE TABLE ORDERLINES (
order int references orders (order)
,line int DEFAULT nextval('orderlines_line')
,sku char(10) references items (sku)
,price numeric(10,2)
,discount_price numeric(10,2) CHECK (discount_price < price)
,tax_pct numeric(3) CHECK (tax_pct >= 0 AND tax_pct <= 100)
)
Like all server-side technologies, the CHECK constraint
can greatly reduce the amount of client-side code you
have to write. Going further, the CHECK constraint
makes your application easier to port to other languages,
if for no other reason than there is less code to port.
More Complex Constraints
Sometimes constraints are more complex than can be
expressed with a CHECK constraint. In these
cases you can attach a "trigger" to a table that
fires when a row is written. The trigger can modify
the data being written or prevent the write from
happening. Triggers are a large topic so they will
be treated separately in other essays. Like all other
computer technologies, triggers have plenty of fans who
quietly use them to great affect, while there are plenty
of noisy types telling you your nose will fall off if
you use them.
Conclusion
This week we have seen that constraints are used to
help to ensure that all data going into a database
is correct. Constraints begin with the basic decisions
about tables themselves, and then go through the
selection of keys, and can be more complex CHECK
constraints or even be completely general purpose
code-based rules enforced in Triggers.
Other Posts
This essay is part of the Keys, Normalization and Denormalization series.
There is also a Complete Table of Contents and a >Skills-oriented Table Of Contents.
Update Contact :
No Wa/Telepon (puat) : 085267792168
No Wa/Telepon (fajar) : 085369237896
Email : Fajarudinsidik@gmail.com
No Wa/Telepon (puat) : 085267792168
No Wa/Telepon (fajar) : 085369237896
Email: Fajarudinsidik@gmail.com
atau Kirimkan Private messanger melalui email dengan klik tombol order dibawah ini :
