In article <11**********************@p77g2000hsh.googlegroups .com>, breadon101
@yahoo.co.uk says...
If there is only one unique key on table then I would prefer that it
be defined as a primary key. In most graphical representations,
Access shows primary key field emboldened. It is a useful reminder.
That said, I am not keen on using a composite keys - be they
designated 'primary' or 'unique' - in joins. If I have a choice, I
would add an autonumber field as the primary key to a table which
would otherwise have a composite primary key. The composite key is
then defined using a unique constraint or, more usually for me, by
defining a unique index. Any foreign key referencing the table can
then use the autonumber primary key.
Think about, for example, a new table to list the songs to be
performed at each Booking. As you have it, without the addition of an
autonumber primary key, it would likely have a composite foreign key
reference to the Booking table. Consider next, what happens if a
Booking has to be postponed due to some emergency or other. It will
get a bit tricky doing all the updates, particularly if you have
enforced referential integrity. Doesn't make any difference whether
you have use 'unique' or 'primary key' to define the constraint. Its
the subsequent composite foreign key join that is nasty. With an
additional autonumber primary key on the Booking table, just one row
need be updated. Much easier.
Once set up, Access query designer takes care of the joins.
One person's nastiness is another person's beauty.
This is not anything real, but is just to show composite primary keys.
Sub createTables()
With CurrentProject.Connection
'.Execute _
"CREATE TABLE Bands" & _
" (band_nbr VARCHAR (10) NOT NULL," & _
" band_name VARCHAR (100) NOT NULL," & _
" CONSTRAINT pk_Bands" & _
" PRIMARY KEY (band_nbr));"
'.Execute _
"CREATE TABLE Customers" & _
" (customer_nbr VARCHAR (10) NOT NULL," & _
" customer_name VARCHAR (100) NOT NULL," & _
" CONSTRAINT pk_customers" & _
" PRIMARY KEY (customer_nbr));"
'.Execute _
"CREATE TABLE Locations" & _
" (location_name VARCHAR (100) NOT NULL," & _
" CONSTRAINT pk_locations" & _
" PRIMARY KEY (location_name));"
'.Execute _
"CREATE TABLE Bookings" & _
" (band_nbr VARCHAR (10) NOT NULL," & _
" customer_nbr VARCHAR (10) NOT NULL," & _
" booking_date DATETIME NOT NULL," & _
" location_name VARCHAR (100) NOT NULL," & _
" CONSTRAINT pk_bookings" & _
" PRIMARY KEY (band_nbr, customer_nbr, booking_date)," & _
" CONSTRAINT uq_no_duplicate_bookings" & _
" UNIQUE (band_nbr, booking_date)," & _
" CONSTRAINT fk_band_nbr_bookings" & _
" FOREIGN KEY (band_nbr)" & _
" REFERENCES Bands (band_nbr)" & _
" ON DELETE CASCADE ON UPDATE CASCADE," & _
" CONSTRAINT fk_customer_nbr_bookings" & _
" FOREIGN KEY (customer_nbr)" & _
" REFERENCES Customers (customer_nbr)" & _
" ON DELETE CASCADE ON UPDATE CASCADE," & _
" CONSTRAINT fk_location_name_bookings" & _
" FOREIGN KEY (location_name)" & _
" REFERENCES Locations (location_name)" & _
" ON DELETE CASCADE ON UPDATE CASCADE);"
'.Execute _
" CREATE TABLE BookingSongs" & _
" (band_nbr VARCHAR (10) NOT NULL," & _
" customer_nbr VARCHAR (10) NOT NULL," & _
" booking_date DATETIME NOT NULL," & _
" song_title VARCHAR (100) NOT NULL," & _
" play_sequence INTEGER DEFAULT 1 NOT NULL," & _
" CONSTRAINT pk_bookingsongs" & _
" PRIMARY KEY (band_nbr,customer_nbr," & _
" booking_date, song_title, play_sequence)," & _
" CONSTRAINT fk_bookings_bookingsongs" & _
" FOREIGN KEY (band_nbr, customer_nbr, booking_date)" & _
" REFERENCES Bookings (band_nbr, customer_nbr, booking_date)" & _
" ON UPDATE CASCADE);"
End With
End Sub
