Database Reference
In-Depth Information
The foreign key constraint must name the table referenced by the for-
eign key. Optionally, it can name the primary key or unique key column
referenced as well. A foreign key constraint created inline is not allowed to
have a name you define. Out-of-line foreign key constraints can be named.
Notice how this foreign key column is declared as NOT NULL. It is
possible to declare a foreign key column as being nullable, but this require-
ment is rare in reality unless your data model uses one-to-many or zero-
entity relationships. This type of relationship is common in multidimen-
sional, multiple-inheritance object structures and sometimes in data ware-
house fact tables (e.g., MUSIC schema SALES table). As far as objects are
concerned, Oracle Database is a relational database, not an object database.
The ARTIST_ID foreign key column will automate validation between the
ARTIST_ID columns in both of the new ARTISTS and SONGS tables.
20.2.1.2.1
Out-of-Line Primary and Foreign Keys
We have used inline, column-level constraints in the previous two table cre-
ation scripts for the ARTISTS and SONGS tables. We could have used out-
of-line or table-level constraints for all but the NOT NULL constraints in
these tables. Table-level constraints are applied to the table as a whole after
all columns have been created. For the ARTISTS and SONGS tables, it is
not necessary to use table-level constraints.
Situations where table-level constraints are required can involve con-
straints created on multiple columns. The CDTRACK table in the MUSIC
schema is a perfect example. Let's create a copy of the CDTRACK table
called CDTRACKS.
CREATE TABLE CDTRACKS(
MUSICCD_ID NUMBER NOT NULL
, SONG_ID NUMBER NOT NULL
, TRACK_SEQ_NO NUMBER NOT NULL
,
CONSTRAINT
PKCDTRACKS
PRIMARY KEY
(MUSICCD_ID,SONG_ID)
,
CONSTRAINT
FKCDTRACKS_SONG
FOREIGN KEY
(SONG_ID) REFERENCES SONGS
,
CONSTRAINT
FKCDTRACK_CD
FOREIGN KEY
(MUSICCD_ID) REFERENCES MUSICCD);
Out-of-line constraints are defined immediately after the column defini-
tions. You can place them before the column definitions as well, although
