constraints and primary keys
Date: Mon, 11 Jun 2007 12:16:15 GMT
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
CONSTRAINT VenueSameDay UNIQUE (VenueID, BookingsDate);
CONSTRAINT BandSameDay UNIQUE (BandID, BookingsDate)
Disclaimer: bands and venues would more often have than not have more than one per night. Sure.
CONSTRAINT VenueSameDayTime UNIQUE (VenueID, BookingsDate, BookingsTime) then. Received on Mon Jun 11 2007 - 14:16:15 CEST