constraints and primary keys
Date: Mon, 11 Jun 2007 12:16:15 GMT
Message-ID: <j2bbi.12033$wH4.10853_at_news-server.bigpond.net.au>
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
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.
Received on Mon Jun 11 2007 - 14:16:15 CEST