On Jun 11, 3:40 pm, "Peter" <ptdsmitch...@[EMAIL PROTECTED]
> 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 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.
Hi Peter,
it seems that you have been using the MS Access for too long. The
problem with MS users is that they might not get the idea of why/how a
thing is happening as a rich set of wizards do most of the dirty(are
they??????) things behind the scenes.
Anyways, here are some basic concepts on the the topics you have asked
for:
CONSTRAINTS: these can be thought of as any business/organizational
rule that govern the entry and manipulation of your data in the DB.
like in your example only 1 band can play on a particular date at a
particular venue. Many other rules are also there like no 2 bands can
have the same name to avoid confusion and so on. These constraints
govern the data so that it is in tune with the real world system and
rules. Every thing you define from the data-type, size, unique, not-
null, primary keys, or foreign keys are some type of constraints.
PRIMARY KEY: a primary key is a set of (one or more) column(s) that is
used to uniquely identify any entity (or record) in the table. all the
attributes comprising the primary key CAN NOT have NULL values and
each combination (if composite key) MUST be unique. usually it is
better to assign a numeric primary key as helps in faster processing.
UNIQUE: the constraints of uniqueness implies, IF the column has an7y
value it must not match (be equal to) with any of the existing values
in the same column. The column having unique constraint CAN have NULL
values, as nulls never match to any other value or to themselves
(i.e., NULL <> NULL and NULL <> any-other-value). Therefore, in your
design for the band table the unique ness on band name does not ensure
that every record will have a band-name entry.
************************************************************************
******Primary Keys are by default UNIQUE and NOT NULL*******
************************************************************************
hope that this is of some help to you.
tc
|
