By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,410 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Constraints and primary keys

P: n/a
I am interested in informed feedback on the use of Constraints, Primary Keys
and Unique.
The following SQL statement creates a Bands tables for a database of
bookings Bands into Venues, where the rule of the business is that only band
plays on the one night.
The SQL statement prevents a Band name being repeated (as it is Unique).
Similar statement for the Venues.
CREATE TABLE Bands
(BandID varchar(5) CONSTRAINT BandID PRIMARY KEY,
Band varchar(15) CONSTRAINT BandName UNIQUE,
State varchar(3) NOT NULL);

The SQL statement for the Bookings follows - where a Venue having two bands
on the same day is prevented by the constraint in the last line.
CREATE TABLE Bookings
(VenueID varchar(5),
BandID varchar(5),
BookingsDate datetime,
StartingTime datetime,
CONSTRAINT VenueSameDay UNIQUE (VenueID, BookingsDate);

I am after any feedback on the concepts of primary key, constraints, unique
(and not null). Is a constraint a key? Or am I in the ballpark to suggest
one constraint is a primary key, but there others, i.e. unique. Or does one
call a unique constraint a key/primary key?
And in Microsoft Access, I have for years seen this implemented by having a
multiple field primary key - in this case Venue ID and BookingsDate and no
one seemed to be aware of the Constraint clause - which seems a better
implementation. One reason for so - is that one can also implement another
one, eg.
CONSTRAINT BandSameDay UNIQUE (BandID, BookingsDate)

And composite primary keys? not sure where this fits in.

Peter
Disclaimer: bands and venues would more often have than not have more than
one per night. Sure.
Make it
CONSTRAINT VenueSameDayTime UNIQUE (VenueID, BookingsDate, BookingsTime)
then.

Jun 11 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Jun 11, 7:15 am, "Peter" <ptdsmitch...@bigpond.comwrote:
I am interested in informed feedback on the use of Constraints, Primary Keys
and Unique.
The following SQL statement creates a Bands tables for a database of
bookings Bands into Venues, where the rule of the business is that only band
plays on the one night.
The SQL statement prevents a Band name being repeated (as it is Unique).
Similar statement for the Venues.
CREATE TABLE Bands
(BandID varchar(5) CONSTRAINT BandID PRIMARY KEY,
Band varchar(15) CONSTRAINT BandName UNIQUE,
State varchar(3) NOT NULL);

The SQL statement for the Bookings follows - where a Venue having two bands
on the same day is prevented by the constraint in the last line.
CREATE TABLE Bookings
(VenueID varchar(5),
BandID varchar(5),
BookingsDate datetime,
StartingTime datetime,
CONSTRAINT VenueSameDay UNIQUE (VenueID, BookingsDate);

I am after any feedback on the concepts of primary key, constraints, unique
(and not null). Is a constraint a key? Or am I in the ballpark to suggest
one constraint is a primary key, but there others, i.e. unique. Or does one
call a unique constraint a key/primary key?
And in Microsoft Access, I have for years seen this implemented by having a
multiple field primary key - in this case Venue ID and BookingsDate and no
one seemed to be aware of the Constraint clause - which seems a better
implementation. One reason for so - is that one can also implement another
one, eg.
CONSTRAINT BandSameDay UNIQUE (BandID, BookingsDate)

And composite primary keys? not sure where this fits in.

Peter
Disclaimer: bands and venues would more often have than not have more than
one per night. Sure.
Make it
CONSTRAINT VenueSameDayTime UNIQUE (VenueID, BookingsDate, BookingsTime)
then.
I agree that constraints are potentially useful and that they have
been glossed over because Microsoft didn't give them much emphasis.
This idea seems much better than enforcing uniqueness with a composite
primary key. I like it more from a performance and ease of query
creation standpoint because it allows an autonumber primary key to
proxy for a composite one, but I'll try to stay aware of the benefits
afforded by the possibility of multiple constraints. My programming
style is to check for conditions whenever possible rather than use
error handling to detect violations so a composite key or other
enforcement mechanism is required to ensure that something wrong can't
happen while the check is being made. I.e., the constraint becomes my
safety net for rare multiuser situations. I have a very large number
of concurrent users and ended up using many unbound forms. My
approach is definitely not the norm but even from my unique
perspective constraints are potentially useful for protecting data
integrity in a way that doesn't increase the complexity of the SQL I
have to maintain. My usage is a single facet of the topic you
breach. I hope others will address your questions also.

James A. Fortune
CD********@FortuneJames.com

Jun 11 '07 #2

P: n/a
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.
Jun 11 '07 #3

P: n/a
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
Jun 12 '07 #4

P: n/a
On Jun 11, 8:17 pm, Mike Gramelspacher <grame...@psci.netwrote:
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
Thanks for the detailed example. The joins will indeed be elegant.

James A. Fortune
CD********@FortuneJames.com
Jun 13 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.