Re: constraints and primary keys

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Mon, 11 Jun 2007 10:47:17 -0300
Message-ID: <466d5265$0$4304$9a566e8b_at_news.aliant.net>


Peter wrote:

> 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.

Hi Peter,

I am not sure there is a question above. You seem to want to solicit general comments about fundamental concepts in data management.

I suggest you find a good introductory text and read it. It has been a while since I perused what is on the shelves at bookstores so I am not sure what is currently available. If the book says relational databases are named for references from one table to another, put it back on the shelf and look for something even marginally correct.

Perhaps others can offer specific suggestions.

Cheers,
Bob Received on Mon Jun 11 2007 - 15:47:17 CEST

Original text of this message