Database Reference
In-Depth Information
&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//
'DWH'HFHDVHG
1XPHULF
18//
&21675$,17
$UWLVW3.
35,0$5< .(< $UWLVW,'
&21675$,17
$UWLVW$.
81,48( /DVW1DPH )LUVW1DPH
&5($7( 7$%/( :25.
:RUN,'
,QW
127 18// ,'(17,7<
7LWOH
&KDU
127 18//
&RS\
&KDU
127 18//
0HGLXP
&KDU
18//
>'HVFULSWLRQ@
9DUFKDU
18// '()$8/7 8QNQRZQSURYHQDQFH
$UWLVW,'
,QW
127 18//
&21675$,17
:RUN3.
35,0$5< .(< :RUN,'
&21675$,17
:RUN$.
81,48( 7LWOH &RS\
&21675$,17
$UWLVW). )25(,*1.(< $UWLVW,'
5()(5(1&(6 $57,67 $UWLVW,'
2183'$7(12 $&7,21
21 '(/(7( 12 $&7,21
Figure 7-10
SQL Statements to Create
the artISt-to-WOrK 1:N
relationship
The only new syntax in Figure 7-10 is the FOREIGN KEY constraint at the end of WORK.
Such constraints are used to define referential integrity constraints. The FOREIGN KEY con-
straint in Figure 7-10 is equivalent to the following referential integrity constraint:
ArtistID in WORK must exist in ArtistID in ARTIST
Note that the foreign key constraint contains two SQL clauses that implement the min-
imum cardinality enforcement requirements of Figure 7-8. The SQL ON UPDATE clause
specifies whether updates should cascade form ARTIST to WORK, and the SQL ON DELETE
clause specifies whether deletions in ARTIST should cascade to WORK.
The expression ON UPDATE NO ACTION indicates that updates to the primary key for a
table that has children should be prohibited (this is the standard setting for surrogate keys that
should never change). The expression ON UPDATE CASCADE would indicate that updates
should cascade. ON UPDATE NO ACTION is the default.
Similarly, the expression ON DELETE NO ACTION indicates that deletions of rows that
have children should be prohibited. The expression ON DELETE CASCADE would indicate
that deletions should cascade. ON DELETE NO ACTION is the default.
In the present case, the ON UPDATE NO ACTION is meaningless because the primary key
of ARTIST is a surrogate and will never be changed. The ON UPDATE action would need to be
specified for nonsurrogate data keys, however, and we show the option here so you will know
how to code it.
By ThE WAy Note that you must define parent tables before child tables. In this case,
you must define ARTIST before WORK. If you try to reverse the order of
definition, the DBMS will generate an error message on the FOREIGN KEY constraint
because it will not yet know about the ARTIST table.
Similarly, you must delete tables in the opposite order. You must DROP (described
later in this chapter) a child before a parent. Better SQL parsers would sort out all of
this so that statement order would not matter, but, alas, that's not the way it's done!
Just remember the following: Parents are first in and last out.
 
Search WWH ::




Custom Search