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 such a thing 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.
|
