SQL for Database Construction and Application Processing - Database Processing: Fundamentals, Design, and Implementation - page 290

&5($7( 7$%/( $57,67
$UWLVW,' ,QW 127 18// ,'(17,7<

/DVW1DPH &KDU 127 18//

)LUVW1DPH &KDU 127 18//

1DWLRQDOLW\ &KDU 18//

'DWH2I%LUWK 1XPHULF 18//

Figure 7-9
SQL Statements to Create
the Initial Version of the
artISt table

'DWH'HFHDVHG 1XPHULF 18//

&21675$,17 $UWLVW3. 35,0$5< .(< $UWLVW,'

&21675$,17 $UWLVW$. 81,48( /DVW1DPH )LUVW1DPH

The last two expressions in the SQL table definition statement in Figure 7-9 are constraints
that define the primary key and a candidate, or alternate, key. As stated in Chapter 6, the primary
purpose of an alternate key is to ensure uniqueness of column values. Thus, in SQL, alternate
keys are defined using the UNIQUE constraint.
The format of such constraints is the word CONSTRAINT followed by a constraint name
provided by the developer followed by a keyword indicating the type of constraint (PRIMARY
KEY or UNIQUE in this example) and then one or more columns in parentheses. For example,
the following statement defines a constraint named MyExample that ensures that the combi-
nation of first and last name is unique:

CONSTRAINT MyExample UNIQUE (FirstName, LastName),

As stated in Chapter 6, primary key columns must be NOT NULL, but candidate keys can be
NULL or NOT NULL.

By ThE WAy SQL originated in the era of punch-card data process (“What is a punch-
card?” you ask? See: http://en.wikipedia.org/wiki/Punch_card ). Punched
cards had only uppercase letters, so there was no need to think about case sensitivity.
When cards were replaced by regular keyboards, DBMS vendors chose to ignore the dif-
ference between uppercase and lowercase letters. Thus, CREATE TABLE, create table,
and CReatE taBle are all the same in SQL. NULL, null, and Null are all the same as well.

Notice that the last line of the SQL statement in Figure 7-9 is a closed parenthesis fol-
lowed by a semicolon. These characters could be placed on the line above, but dropping them
to a new line is a style convention that makes it easy to determine the boundaries of CREATE
TABLE statements. Also notice that column descriptions and constraints are separated by
commas but that there is no comma after the last one.

By ThE WAy Many organizations have developed SQL coding standards of their own.
Such standards specify not only the format of SQL statements, but also
conventions for naming constraints. For example, in the figures in this chapter, we use
the suffix PK on the names of all primary key constraints and the suffix FK for all foreign
key constraints. Most organizations have standards that are more comprehensive. You
should follow your organization's standards, even if you disagree with them. Consistent
SQL coding improves organizational efficiency and reduces errors.

Creating the WORK Table and the 1:N ARTIST-to-WORK Relationship
Figure 7-10 shows SQL statements for creating the ARTIST and WORK tables and their relation-
ship. Note that the column name Description is written as [Description] because Description is
a Microsoft SQL Server 2012 reserved keyword (see Chapter 10A on Microsoft SQL Server 2012),
and we must use the square brackets ([ and ]) to create a delimited identifier. This is the same
reason that in Chapter 6 we decided to use the table name TRANS instead of TRANSACTION.