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.

On 11.06.2007 14:13, Peter wrote:
Constraint is a /logical/ concept while an index is a /physical/
concept. Some constraints use indexes for their enforcement (e.g.
uniqueness and PK) while others don't (e.g. "not null").

Kind regards

robert

Peter wrote:
Robert Klemme wrote:
To broaden that:

UNIQUE and PRIMARY KEY constraints, respectively, are physical embodiments of
the logical concepts of candidate keys and primary keys. The logical concepts
are part of relational database theory; the physical implementations enforce
the rules.

A key, candidate or otherwise, is a combination of columns (this is where
composite keys fit in) that uniquely identify the row. Ideally, the key
should comprise fewer columns than the entire row; columns not part of the key
are "dependent data". Sometimes there are more than one combination of
columns that can identify a row, such as a unique username and an ID code.
Either combination can be called "the key", but only one, really, should be
called the PRIMARY KEY. In SQL, that one is the PRIMARY KEY; all the other
keys are declared via a UNIQUE constraint.

Composite, or multi-column keys, may have one or more columns.

One more note - in temporal databases, particularly with fine-resolution types
like TIMESTAMP, do not compare to equality to a single time column. Compare
for an overlap of ranges - have a "start_time" and "finish_time" column in the
row, or equivalent, e.g., "valid" and "expires", where NULL for "expires"
means it is still valid.

--
Lew

"Lew" <lew (AT) lewscanon (DOT) nospam> wrote

Huh?

There are plenty of propositions that consist entirely of key data.


This can be generalized to any domain which uses a (non integer) number as a
measure.
Testing real values for equality is always subject to random error.

"Lew" wrote
David Cressey wrote:
Point taken.

I should have said, "In some cases, the key will comprise fewer columns than
the entire row."

--
Lew