Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> constraints and primary keys

constraints and primary keys

From: Peter <ptdsmitchell_at_bigpond.com>
Date: Mon, 11 Jun 2007 12:16:15 GMT
Message-ID: <j2bbi.12033$wH4.10853@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
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. Received on Mon Jun 11 2007 - 07:16:15 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US