This page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & Trudy Pelzer. The authors have graciously allowed us to reproduce the contents of the book here. Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB. Use the navigation bar to navigate the book.

Any business needs to store "money" usually a signed decimal with two fixed digits after the decimal point; and "interest" usually an unsigned decimal with 3 digits after the decimal point. Some SQL DBMSs have special data types for business needs, but Standard SQL doesn't, so this is a good place to use a Domain. For example, these four SQL statements define and utilize two numeric Domains:

CREATE DOMAIN MONEY_ AS DECIMAL(8,2));

CREATE DOMAIN INTEREST_ AS DECIMAL(5,3));

ALTER DOMAIN INTEREST_ ADD CONSTRAINT constraint_1
  CHECK (VALUE >= 00.000);

CREATE TABLE Money_Examples (
  money_column_1 MONEY_,
  interest_column_1 INTEREST_,
  money_column_2 MONEY_,
  interest_column_2 INTEREST_);

In this example, the first two SQL statements create two Domains named MONEY_ and INTEREST_. The third SQL statement adds a Constraint to INTEREST_ Domain: it must always have a value greater than or equal to zero. Lastly, the Domains are used in a CREATE TABLE statement this saves a bit of typing, but more importantly, using the Domains makes it clear that money and interest fields are being defined rather than merely vague, generic decimal fields.

SQL provides a predefined unsigned-integer Domain, called CARDINAL_NUMBER, that you could use on the theory that anything predefined is better than a roll-your-own. Since all predefined Objects are belong to INFORMATION_SCHEMA, use a <Schema name> qualifier when making Columns with CARDINAL_NUMBER for example:

ALTER TABLE Exact_Examples ADD COLUMN
  occurrence_cardinal INFORMATION_SCHEMA.CARDINAL_NUMBER;

This definition will cause this SQL statement to fail because CARDINAL_NUMBER allows only unsigned numbers (that is, only numbers that are greater than or equal to zero):

UPDATE Exact_Examples SET
  occurrence_cardinal = -1;

But this SQL statement will work:

UPDATE Exact_Examples SET
  occurrence_cardinal = +1;

Note: Numbers in a CARDINAL_NUMBER Domain don't have the same range as C/Delphi "unsigned".

Comments

Comments loading...