Data Types
Depending on their histories, different SQL implementations support a vari-ety of data types. The SQL:2003 specification recognizes only five predefined general types:
Numerics Strings
Booleans Datetimes Intervals
Within each of these general types may be several subtypes (exact numerics, approximate numerics, character strings, bit strings, large object strings). In addition to the built-in, predefined types, SQL:2003 supports collection types, constructed types, and user-defined types.
If you use an SQL implementation that supports one or more data types that the SQL:2003 specification doesn’t describe, you can keep your database more portable by avoiding these undescribed data types. Before you decide to create and use a user-defined data type, make sure that any DBMS you may want to port to in the future also supports user-defined types.
Exact numerics
As you can probably guess from the name, the exact numeric data types enable you to express the value of a number exactly. Five data types fall into this category:
INTEGER SMALLINT BIGINT NUMERIC DECIMAL
INTEGER data type
Data of the INTEGER type has no fractional part, and its precision depends on the specific SQL implementation. The database developer can’t specify the precision.
The precision of a number is the maximum number of digits the number can have.
SMALLINT data type
The SMALLINT type is also for integers, but the precision of a SMALLINT in a specific implementation can’t be any larger than the precision of an INTEGER on the same implementation. Implementations on IBM System/370 computers commonly represent SMALLINT and INTEGER with 16-bit and 32-bit binary numbers respectively. In many implementations, SMALLINT and INTEGER are the same.
If you’re defining a database table column to hold integer data and you know that the range of values in the column won’t exceed the precision of SMALLINT data on your implementation, assign the column the SMALLINT type rather than the INTEGER type. This assignment may enable your DBMS to conserve storage space.
BIGINT data type
The BIGINT data type is new with SQL:2003. It is also an integer type, and it is defined as a type whose precision is at least as great as that of the INTEGER type and could be greater. The exact precision of a BIGINT data type is imple-mentation dependent.
NUMERIC data type
NUMERIC data can have a fractional component in addition to its integer com-ponent. You can specify both the precision and the scale of NUMERIC data. (Precision, remember, is the maximum number of digits possible.)
The scale of a number is the number of digits in its fractional part. The scale of a number can’t be negative or larger than that number’s precision.
If you specify the NUMERIC data type, your SQL implementation gives you exactly the precision and scale that you request. You may specify NUMERIC and get a default precision and scale, or NUMERIC (p) and get your specified precision and the default scale, or NUMERIC (p,s) and get both your speci-fied precision and your specified scale. The parameters p and s are place-holders that would be replaced by actual values in a data declaration.
Say, for example, that the NUMERIC data type’s default precision for your SQL implementation is 12 and the default scale is 6. If you specify a database column as having a NUMERIC data type, the column can hold numbers up to 999,999.999999. If, on the other hand, you specify a data type of NUMERIC
(10) for a column, that column can hold only numbers with a maximum value of 9,999.999999. The parameter (10) specifies the maximum number of digits possible in the number. If you specify a data type of NUMERIC (10,2) for a column, that column can hold numbers with a maximum value of 99,999,999.99. In this case, you may still have ten total digits, but only two of the digits can fall to the right of the decimal point.
NUMERIC data is for values such as 595.72. That value has a precision of 5 (the total number of digits) and a scale of 2 (the number of digits to the right of the decimal point). A data type of NUMERIC (5,2) is appropriate for such numbers.
DECIMAL data type
The DECIMAL data type is similar to NUMERIC. This data type can have a frac-tional component, and you can specify its precision and scale. The difference
is that the precision your implementation supplies may be greater than what you specify, and if so, the implementation uses the greater precision. If you do not specify precision or scale, the implementation uses default values, as it does with the NUMERIC type.
An item that you specify as NUMERIC (5,2) can never contain a number with an absolute value greater than 999.99. An item that you specify as DECIMAL (5,2) can always hold values up to 999.99, but if the implementation permits larger values, the DBMS doesn’t reject values larger than 999.99.
Use the NUMERIC or DECIMAL type if your data has fractional positions, and use the INTEGER, SMALLINT, or BIGINT type if your data always consists of whole numbers. Use the NUMERIC type if you want to maximize portability, because a value that you define as NUMERIC (5,2), for example, holds the same range of values on all systems.
Approximate numerics
Some quantities have such a large range of possible values (many orders of magnitude) that a computer with a given register size can’t represent all the values exactly. (Examples of register sizes are 32 bits, 64 bits, and 128 bits.)
Usually in such cases, exactness isn’t necessary, and a close approximation is acceptable. SQL:2003 defines three approximate numeric data types to handle this kind of data.
REAL data type
The REAL data type gives you a single-precision floating-point number, the precision of which depends on the implementation. In general, the hardware you’re using determines precision. A 64-bit machine, for example, gives you more precision than does a 32-bit machine.
A floating-point number is a number that contains a decimal point. The decimal point “floats” or appears in different locations in the number, depending on the number’s value. 3.1, 3.14, and 3.14159 are examples of floating-point numbers.
DOUBLE PRECISION data type
The DOUBLE PRECISION data type gives you a double-precision floating-point number, the precision of which again depends on the implementation. Surprisingly, the meaning of the word DOUBLE also depends on the implemen-tation. Double-precision arithmetic is primarily employed by scientific users. Different scientific disciplines have different needs in the area of precision. Some SQL implementations cater to one category of users, and other imple-mentations cater to other categories of users.
In some systems, the DOUBLE PRECISION type has exactly twice the capacity of the REAL data type for both mantissa and exponent. (In case you’ve forgotten what you learned in high school, you can represent any number as a mantissa multiplied by ten raised to the power given by an exponent. You can write 6,626, for example, as 6.626E3. The number 6.626 is the mantissa, which you multiply by ten raised to the third power; in that case, 3 is the exponent.) You gain no benefit by representing numbers that are fairly close to one (such as 6,626 or even 6,626,000) with an approximate numeric data type. Exact numeric types work just as well, and after all, they’re exact. For numbers that are either very near zero or much larger than one, however, such as 6.626E-34 (a very small number), you must use an approximate numeric type. The exact numeric types can’t hold such numbers. On other systems, the DOUBLE PRECISION type gives you somewhat more than twice the mantissa capacity and somewhat less than twice the exponent capacity as the REAL type. On yet another type of system, the DOUBLE PRECISION type gives double the mantissa capacity but the same exponent capacity as the REAL type. In this case, accuracy doubles, but range does not.
The SQL:2003 specification does not try to arbitrate or establish by fiat what DOUBLE PRECISION means. The specification requires only that the precision of a DOUBLE PRECISION number be greater than the precision of a REAL number. This constraint, though rather weak, is perhaps the best possible in light of the great differences you encounter in hardware.
FLOAT data type
The FLOAT data type is most useful if you think that your database may some-day migrate to a hardware platform with different register sizes than the one on which you originally design it. By using the FLOAT data type, you can spec-ify a precision — for example, FLOAT (5). If your hardware supports the specified precision with its single-precision circuitry, single-precision arith-metic is what your system uses. If the specified precision requires double-precision arithmetic, the system uses double-precision arithmetic.
Using FLOAT rather than REAL or DOUBLE PRECISION makes porting your databases to other hardware easier, because the FLOAT data type enables you to specify precision. The precision of REAL and DOUBLE PRECISION numbers is hardware-dependent.
If you aren’t sure whether to use the exact numeric data types (NUMERIC/ DECIMAL) or the approximate numeric data types (FLOAT/REAL), use the exact numeric types. The exact data types are less demanding of system resources and, of course, give exact rather than approximate results. If the range of pos-sible values of your data is large enough to require the use of the approximate data types, you can probably determine this fact in advance.
Character strings
Databases store many types of data, including graphic images, sounds, and animations. I expect odors to come next. Can you imagine a three-dimensional 1600 x 1200 24-bit color image of a large slice of pepperoni pizza on your screen, while an odor sample taken at DiFilippi’s Pizza Grotto replays through your super-multimedia card? Such a setup may get frustrating — at least until you can afford to add taste-type data to your system as well. Alas, you can expect to wait a long time before odor and taste become standard SQL data types. These days, the data types that you use most commonly — after the numeric types, of course — are the character-string types.
You have three main types of character data: fixed character data (CHARACTER or CHAR), varying character data (CHARACTER VARYING or VARCHAR), and character large object data (CHARACTER LARGE OBJECT or CLOB). You also have three variants of these types of character data: NATIONAL CHARACTER, NATIONAL CHARACTER VARYING, and NATIONAL CHARACTER LARGE OBJECT.
CHARACTER data type
If you define the data type of a column as CHARACTER or CHAR, you can specify the number of characters the column holds by using the syntax CHARACTER (x), where x is the number of characters. If you specify a column’s data type as CHARACTER (16), for example, the maximum length of any data you can enter in the column is 16 characters. If you don’t specify an argument (that is, you don’t provide a value in place of the x), SQL assumes a field length of one character. If you enter data into a CHARACTER field of a specified length and you enter fewer characters than the specified number, SQL fills the remaining character spaces with blanks.
CHARACTER VARYING data type
The CHARACTER VARYING data type is useful if entries in a column can vary in length, but you don’t want SQL to pad the field with blanks. This data type enables you to store exactly the number of characters that the user enters. No default value exists for this data type. To specify this data type, use the form CHARACTER VARYING (x) or VARCHAR (x), where x is the maximum number of characters permitted.
CHARACTER LARGE OBJECT data type
The CHARACTER LARGE OBJECT (CLOB) data type was introduced with SQL:1999. As its name implies, it is used with huge character strings that are too large for the CHARACTER type. CLOBs behave much like ordinary character strings, but there are a number of restrictions on what you can do with them. A CLOB may not be used in a PRIMARY KEY, FOREIGN KEY, or UNIQUE predicate. Furthermore, it may not be used in a comparison other than one for either equality or inequality. Because of their large size, applications generally do
not transfer CLOBs to or from a database. Instead, a special client-side type called a CLOB locator is used to manipulate the CLOB data. It is a parameter whose value identifies a character large object.
NATIONAL CHARACTER, NATIONAL CHARACTER VARYING, and NATIONAL CHARACTER LARGE OBJECT data types
Different languages have some characters that differ from any characters in another language. For example, German has some special characters not present in the English language character set. Some languages, such as Russian, have a very different character set from the English one. If you specify, for example, the English character set as the default for your system, you can use alternate character sets because the NATIONAL CHARACTER, NATIONAL CHARACTER VARYING, and NATIONAL CHARACTER LARGE OBJECT data types function the same as the CHARACTER, CHARACTER VARYING, and CHARACTER LARGE OBJECT data types, except that the character set you’re specifying is different from the default character set. You can specify the character set as you define a table column. If you want, each column can use a different character set. The following example of a table-creation statement uses multiple character sets:
CREATE TABLE XLATE (
LANGUAGE_1 CHARACTER (40),
LANGUAGE_2 CHARACTER VARYING (40) CHARACTER SET GREEK, LANGUAGE_3 NATIONAL CHARACTER (40),
LANGUAGE_4 CHARACTER (40) CHARACTER SET KANJI ) ;
The LANGUAGE_1 column contains characters in the implementation’s default character set. The LANGUAGE_3 column contains characters in the implemen-tation’s national character set. The LANGUAGE_2 column contains Greek char-acters. And the LANGUAGE_4 column contains kanji characters.
Booleans
The BOOLEAN data type comprises the distinct truth values true and false, as well as unknown. If either a Boolean true or false value is compared to a NULL or unknown truth value, the result will have the unknown value.
Datetimes
The SQL:2003 standard defines five data types that deal with dates and times. These data types are called datetime data types, or simply datetimes. Considerable overlap exists among these data types, so some implementa-tions you encounter may not support all five.
Implementations that do not fully support all five data types for dates and times may experience problems with databases that you try to migrate from another implementation. If you have trouble with a migration, check how both the source and the destination implementations represent dates and times.
DATE data type
The DATE type stores year, month, and day values of a date, in that order. The year value is four digits long, and the month and day values are both two digits long. A DATE value can represent any date from the year 0001 to the year 9999. The length of a DATE is ten positions, as in 1957-08-14.
Because SQL explicitly represents all four digits of a year in the DATE type, SQL data was never subject to the much-hyped Year 2000 (Y2K) problem.
TIME WITHOUT TIME ZONE data type
The TIME WITHOUT TIME ZONE data type stores hour, minute, and second values of time. The hours and minutes occupy two digits. The seconds value may be only two digits but may also expand to include an optional fractional part. This data type, therefore, represents a time of 32 minutes and 58.436 seconds past 9 a.m., for example, as 09:32:58.436.
The precision of the fractional part is implementation-dependent but is at least six digits long. A TIME WITHOUT TIME ZONE value takes up eight posi-tions (including colons) when the value has no fractional part, or nine posi-tions (including the decimal point) plus the number of fractional digits when the value does include a fractional part. You specify TIME WITHOUT TIME ZONE type data either as TIME, which gives you the default of no fractional digits, or as TIME WITHOUT TIME ZONE (p), where p is the number of digit positions to the right of the decimal. The example in the preceding paragraph represents a data type of TIME WITHOUT TIME ZONE (3).
TIMESTAMP WITHOUT TIME ZONE data type
TIMESTAMP WITHOUT TIME ZONE data includes both date and time informa-tion. The lengths and the restrictions on the values of the components of TIMESTAMP WITHOUT TIME ZONE data are the same as they are for DATE and TIME WITHOUT TIME ZONE data, except for one difference: The default length of the fractional part of the time component of a TIMESTAMP WITHOUT TIME ZONE is six digits rather than zero. If the value has no fractional digits, the length of a TIMESTAMP WITHOUT TIME ZONE is 19 positions — ten date posi-tions, one space as a separator, and eight time positions, in that order. If frac-tional digits are present (six digits is the default), the length is 20 positions plus the number of fractional digits. The twentieth position is for the decimal point. You specify a field as TIMESTAMP WITHOUT TIME ZONE type by using either TIMESTAMP WITHOUT TIME ZONE or TIMESTAMP WITHOUT TIME ZONE (p), where p is the number of fractional digit positions. The value of p can’t be negative, and the implementation determines its maximum value.
TIME WITH TIME ZONE data type
The TIME WITH TIME ZONE data type is the same as the TIME WITHOUT TIME ZONE data type except this type adds information about the offset from universal time (UTC, also known as Greenwich Mean Time or GMT). The value of the offset may range anywhere from –12:59 to +13:00. This additional infor-mation takes up six more digit positions following the time — a hyphen as a separator, a plus or minus sign, and then the offset in hours (two digits) and minutes (two digits) with a colon in between the hours and minutes. A TIME WITH TIME ZONE value with no fractional part (the default) is 14 positions long. If you specify a fractional part, the field length is 15 positions plus the number of fractional digits.
TIMESTAMP WITH TIME ZONE data type
The TIMESTAMP WITH TIME ZONE data type functions the same as the TIMESTAMP WITHOUT TIME ZONE data type except that this data type also adds information about the offset from universal time. The additional infor-mation takes up six more digit positions following the timestamp. Including time zone data sets up 25 positions for a field with no fractional part and 26 posi-tions plus the number of fractional digits for fields that do include a frac-tional part (six digits is the default number of fractional digits).
Intervals
The interval data types relate closely to the datetime data types. An interval is the difference between two datetime values. In many applications that deal with dates, times, or both, you sometimes need to determine the interval between two dates or two times. SQL recognizes two distinct types of inter-vals: the year-month interval and the day-time interval. A year-month interval is the number of years and months between two dates. A day-time interval is the number of days, hours, minutes, and seconds between two instants within a month. You can’t mix calculations involving a year-month interval with calculations involving a day-time interval, because months come in varying lengths (28, 29, 30, or 31 days long).
ROW types
The ROW data type was introduced with SQL:1999. It’s not that easy to under-stand, and as a beginning to intermediate SQL programmer, you may never use it. After all, people got by without it just fine between 1986 and 1999.
One notable thing about the ROW data type is that it violates the rules of normal-ization that E.F. Codd declared in the early days of relational database theory. One of the defining characteristics
of first normal form is that a field in a table row may not be multivalued. A field may contain one and only one value. However, the ROW data type allows you to declare an entire row of data to be contained within a single field in a single row of a table — in other words, a row nested within a row.
Consider the following SQL statement, which defines a ROW type for a person’s address information:
CREATE ROW TYPE
|
addr_typ (
|
Street
|
CHARACTER
VARYING (25)
|
City
|
CHARACTER
VARYING(20)
|
State
|
CHARACTER
(2)
|
PostalCode
|
CHARACTER
VARYING (9)
|
) ;
|
After it’s defined, the new ROW type can be used in a table definition:
CREATE TABLE CUSTOMER (
|
||
CustID
|
INTEGER
|
PRIMARY
KEY,
|
LastName
|
CHARACTER
VARYING (25),
|
|
FirstName
|
CHARACTER
VARYING (20),
|
|
Address
|
addr_typ
|
|
Phone
|
CHARACTER
VARYING (15)
|
|
) ;
|
||
The advantage here is that if you are maintaining address information for multiple entities — such as customers, vendors, employees, and stockhold-ers — you only have to define the details of the address specification once, in the ROW type definition.
Collection types
After SQL broke out of the relational straightjacket with SQL:1999, types that violate first normal form became possible. It became possible for a field to contain a whole collection of objects rather than just one. The ARRAY type was introduced in SQL:1999, and the MULTISET type was introduced in SQL:2003.
Two collections may be compared to each other only if they are both the same type, either ARRAY or MULTISET, and if their element types are compa-rable. Because arrays have a defined element order, corresponding elements from the arrays can be compared. Multisets do not have a defined element order, but can be compared if an enumeration exists for each multiset being compared and the enumerations can be paired.
ARRAY type
The ARRAY data type violates first normal form (1NF) but in a different way than the way the ROW type violates 1NF. The ARRAY type, a collection type, is
not a distinct type in the same sense that CHARACTER or NUMERIC are distinct data types. An ARRAY type merely allows one of the other types to have multi-ple values within a single field of a table. For example, say it is important to your organization to be able to contact your customers whether they are at work, at home, or on the road. You want to maintain multiple telephone num-bers for them. You can do this by declaring the Phone attribute as an array, as shown in the following code:
CREATE TABLE CUSTOMER (
|
||
CustID
|
INTEGER
|
PRIMARY
KEY,
|
LastName
|
CHARACTER
VARYING (25),
|
|
FirstName
|
CHARACTER
VARYING (20),
|
|
Address
|
addr_typ
|
|
Phone
|
CHARACTER
VARYING (15) ARRAY [3]
|
|
) ;
|
||
The ARRAY [3] notation allows you to store up to three telephone numbers in the CUSTOMER table. The three telephone numbers represent an example of a repeating group. Repeating groups are a no-no according to classical rela-tional database theory, but this is one of several examples of cases where SQL:1999 broke the rules. When Dr. Codd first enunciated the rules of normal-ization, he traded off functional flexibility for data integrity. SQL:1999 took back some of that functional flexibility, at the cost of some added structural complexity. The increased structural complexity could translate into compro-mised data integrity if you are not fully aware of all the effects of actions you perform on your database. Arrays are ordered in that each element in an array is associated with exactly one ordinal position in the array.
Multiset type
A multiset is an unordered collection. Specific elements of the multiset may not be referenced, because they are not assigned a specific ordinal position in the multiset.
REF types
REF types are not part of core SQL. This means that a DBMS may claim com-pliance with SQL:2003 without implementing REF types at all. The REF type is not a distinct data type in the sense that CHARACTER and NUMERIC are. Instead, it is a pointer to a data item, row type, or abstract data type that resides in a row of a table (a site). Dereferencing the pointer can retrieve the value stored at the target site. If you’re confused, don’t worry, because you’re not alone. Using the REF types requires a working knowledge of object-oriented pro-gramming (OOP) principles. In fact — because the REF types are not a part of core SQL — you may be better off if you don’t use them. If you want maxi-mum portability across DBMS platforms, stick to core SQL.
User-defined types
User-defined types (UDTs) represent another example of features that arrived in SQL:1999 that come from the object-oriented programming world. As an SQL programmer, you are no longer restricted to the data types defined in the SQL:2003 specification. You can define your own data types, using the princi-ples of abstract data types (ADTs) found in such object-oriented program-ming languages as C++.
One of the most important benefits of UDTs is the fact that they can be used to eliminate the “impedance mismatch” between SQL and the host language that is “wrapped around” the SQL. A long-standing problem with SQL has been the fact the SQL’s predefined data types do not match the data types of the host languages within which SQL statements are embedded. Now, with UDTs, a database programmer can create data types within SQL that match the data types of the host language. A UDT has attributes and methods, which are encapsulated within the UDT. The outside world can see the attribute definitions and the results of the methods, but the specific imple-mentations of the methods are hidden from view. Access to the attributes and methods of a UDT can be further restricted by specifying that they are public, private, or protected. Public attributes or methods are available to all users of a UDT. Private attributes or methods are available only to the UDT itself. Protected attributes or methods are available only to the UDT itself or its subtypes. You see from this that a UDT in SQL behaves much like a class in an object-oriented programming language. Two forms of user-defined types exist: distinct types and structured types.
Distinct types
Distinct types are the simpler of the two forms of user-defined types. A dis-tinct type’s defining feature is that it is expressed as a single data type. It is constructed from one of the predefined data types, called the source type.
Multiple distinct types that are all based on a single source type are distinct from each other and are thus not directly comparable. For example, you can use distinct types to distinguish between different currencies. Consider the following type definition:
CREATE DISTINCT TYPE USdollar AS DECIMAL (9,2) ;
This creates a new data type for U.S. dollars, based on the predefined
DECIMAL data type. You can create another distinct type in a similar manner:
CREATE DISTINCT TYPE Euro AS DECIMAL (9,2) ;
You can now create tables that use these new types:
CREATE TABLE USInvoice (
InvID INTEGER PRIMARY KEY,
CustID INTEGER,
EmpID INTEGER,
TotalSale USdollar,
Tax USdollar,
Shipping USdollar,
GrandTotal USdollar
)
;
InvID INTEGER PRIMARY KEY,
CustID INTEGER,
EmpID INTEGER,
TotalSale Euro,
Tax Euro,
Shipping Euro,
GrandTotal Euro
)
;
The USdollar type and the Euro type are both based on the DECIMAL type,
but instances of one cannot be directly compared with instances of the other
or with instances of the DECIMAL type. In SQL as in the real world, it is possi-
ble to convert U.S. dollars into Euros, but this requires a special operation
(CAST). After the conversion has been made, comparisons become possible.
Structured types
The second form of user-defined type, the structured type, is expressed as a list of attribute definitions and methods instead of being based on a single predefined source type.
Constructors
When you create a structured UDT, the DBMS automatically creates a con-structor function for it, giving it the same name as the UDT. The constructor’s job is to initialize the attributes of the UDT to their default values.
Mutators and observers
When you create a structured UDT, the DBMS automatically creates a muta-tor function and an observer function. A mutator, when invoked, changes the value of an attribute of a structured type. An observer function is the opposite of a mutator function. Its job is to retrieve the value of an attribute of a struc-tured type. You can include observer functions in SELECT statements to retrieve values from a database.
Subtypes and supertypes
A hierarchical relationship can exist between two structured types. For exam-ple, a type named MusicCDudt has a subtype named RockCDudt and another subtype named ClassicalCDudt. MusicCDudt is the supertype of those two subtypes. RockCDudt is a proper subtype of MusicCDudt if there is no subtype of MusicCDudt that is a supertype of RockCDudt. If RockCDudt has a subtype named HeavyMetalCDudt, HeavyMetalCDudt is also a subtype of MusicCDudt, but it is not a proper subtype of MusicCDudt.
A structured type that has no supertype is called a maximal supertype, and a structured type that has no subtypes is called a leaf subtype.
Example of a structured type
You can create structured UDTs in the following way:
/* Create a UDT named MusicCDudt */
CREATE TYPE MusicCDudt AS
/* Specify attributes */
Title CHAR(40),
Cost DECIMAL(9,2),
SuggestedPrice DECIMAL(9,2)
/* Allow for subtypes */
NOT FINAL ;
CREATE TYPE RockCDudt UNDER MusicCDudt NOT FINAL ;
The subtype RockCDudt inherits the attributes of its supertype MusicCDudt.
CREATE TYPE HeavyMetalCDudt UNDER RockCDudt FINAL ;
Now that you have the types, you can create tables that use them. For example:
CREATE TABLE METALSKU (
Album HeavyMetalCDudt,
SKU INTEGER) ;
Now you can add rows to the new table:
BEGIN
/* Declare a temporary variable a */
DECLARE a = HeavyMetalCDudt ;
/* Execute the constructor function */
SET a = HeavyMetalCDudt() ;
/* Execute first mutator function */
SET a = a.title(‘Edward the Great’) ;
/* Execute second mutator function */
SET a = a.cost(7.50) ;
/* Execute third mutator function */
SET a = a.suggestedprice(15.99) ;
INSERT INTO METALSKU VALUES (a, 31415926) ;
END
Data type summary
Data
Type
|
Example
Value
|
|||
CHARACTER (20)
|
‘Amateur Radio
|
‘
|
||
VARCHAR (20)
|
‘Amateur Radio’
|
|||
CLOB (1000000)
|
‘This character string is a
million
|
|||
characters long . . .’
|
||||
SMALLINT, BIGINT
|
7500
|
|||
or INTEGER
|
||||
NUMERIC or
DECIMAL
|
3425.432
|
|||
REAL, FLOAT, or DOUBLE
|
6.626E-34
|
|||
PRECISION
|
||||
BLOB (1000000)
|
‘1001001110101011010101010101. .
.’
|
|||
BOOLEAN
|
‘true’
|
|||
DATE
|
DATE ‘1957-08-14’
|
|||
TIME (2) WITHOUT
|
TIME ‘12:46:02.43’ WITHOUT TIME
ZONE
|
|||
TIME ZONE1
|
||||
TIME (3) WITH
|
TIME ‘12:46:02.432-08:00’ WITH
|
|||
TIME ZONE
|
TIME ZONE
|
|||
TIMESTAMP WITHOUT
|
TIMESTAMP ‘1957-08-14 12:46:02’
|
|||
TIME ZONE (0)
|
WITHOUT TIME ZONE
|
|||
TIMESTAMP WITH
|
TIMESTAMP ‘1957-08-14
12:46:02-08:00’
|
|||
TIME ZONE (0)
|
WITH TIME ZONE
|
|||
INTERVAL DAY
|
INTERVAL ‘4’ DAY
|
|||
ROW
|
ROW (Street VARCHAR (25), City
|
|||
VARCHAR (20), State CHAR (2),
|
||||
PostalCode VARCHAR (9))
|
||||
ARRAY
|
INTEGER ARRAY [15]
|
|||
MULTISET
|
No literal
applies to the MULTISET type.
|
|||
REF
|
Not a type,
but a pointer
|
|||
USER DEFINED TYPE
|
Currency type
based on DECIMAL
|
|||
Your SQL implementation may not support all the data types that I describe in this section. Furthermore, your implementation may support nonstandard data types that I don’t describe here. (Your mileage may vary, and so on. You know the drill.)
Null Values
If a database field contains a data item, that field has a specific value. A field that does not contain a data item is said to have a null value. In a numeric field, a null value is not the same as a value of zero. In a character field, a null value is not the same as a blank. Both a numeric zero and a blank character are definite values. A null value indicates that a field’s value is undefined — its value is not known.
A number of situations exist in which a field may have a null value. The fol-lowing list describes a few of these situations and gives an example of each:
The value exists, but you don’t know what the value is yet. You set MASS to null in the Top row of the QUARK table before the mass of the top quark is accurately determined.
The field isn’t applicable for this particular row. You set SEX to null in the C-3PO row of the EMPLOYEE table because C-3PO is a droid who has no gender.
The value is out of range. You set SALARY to null in the Oprah Winfrey row of the EMPLOYEE table because you designed the SALARY column as type NUMERIC (8,2) and Oprah’s contract calls for pay in excess of $999,999.99.
A field can have a null value for many different reasons. Don’t jump to any hasty conclusions about what any particular null value means.
Constraints
Constraints are restrictions that you apply to the data that someone can enter into a database table. You may know, for example, that entries in a particular numeric column must fall within a certain range. If anyone makes an entry that falls outside that range, then that entry must be an error. Applying a range constraint to the column prevents this type of error from happening.
Traditionally, the application program that uses the database applies any constraints to a database. The most recent DBMS products, however, enable you to apply constraints directly to the database. This approach has several advantages. If multiple applications use the same database, you need to apply the constraints only once rather than multiple times. Additionally, adding constraints at the database level is usually simpler than adding them to an application. In many cases, you need only to tack a clause onto your CREATE statement.
No comments:
Post a Comment