Contraints, primary keys

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

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,
CREATE TABLE Bands
(BandID varchar(5) CONSTRAINT BandID PRIMARY KEY,
Band varchar(15) CONSTRAINT BandName UNIQUE,
State varchar(3) NOT NULL);
Click to expand...
Use an autoimcrement as primary key. This will avoid cascade updates.
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);
Click to expand...
You have in mind, that a datetime includes the time? So (1, "08:00 pm" )
and (1, "08:01 pm") are valid values.
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?
Click to expand...
A primary key is just one of the so called candidate key. They are all
unique. Thus some RDBMS implement them using a unique index.
A unique index and a primary key on the same fields has the identical
meaning: you can identify your data row with it.

In Access your primary key is a clustered unqiue index, therefore your
data is stored physically in the order of the 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)
Click to expand...
You can implement as many unique indices as you like til you met the
limit for indices per table. But be aware, that many unqiue indices in a
table may indicate a not proper normalized data schema.


mfG
--> stefan <--
 
Peter said:
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?
Click to expand...

No.

Constraints are DDL SQL clauses that allow you to establish rules on
the database.

A Primary Key is the column (or columns) whose value in a row uniquely
identifies that row.

A Foreign Key is a column (or columns) that contains *copies* of
values from a column (or columns) in another table, where that other
column (or columns) contain unique values (enforced in that other
table by either being Primary Key or having a unique index). Its
purpose is to prevent the entry of values into the Foreign Key column
(or columns) that are not currently in the other column (or columns)
in the other table. This prevents orphaned information from existing
in the database (such as prices that have no items, etc.). This is
called "enforcing referential integrity", and is one of the key
concepts of database design.

MS Access enforces Primary Keys and Foreign Keys using indexes that
are established when the key is established.

Unique indexes make sure that the contents of a column do not repeat.
They are used when necessary but do not need to be used on a Primary
Key (as an index was already established), as this would create a
second unnecessary index.

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.
Click to expand...

A constraint may be established by use of the Constraint clause of the
CREATE TABLE statement, or through the GUI inteferface. It is the
same either way.

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.
Click to expand...

That is the great debate about "natural keys" (VIN, SSN, UPC, etc.)
vs. "artificial keys" (usually an artificial ascending number
sequence).

In natural keys, some values, like those given above, are excellent to
use. Some tables do not have any *one* column that uniquely
identifies the row. In this case, multiple columns must be specified
to create a primary key.

In artificial keys, a single column that has nothing (or should have
nothing) to do with the rest of the row's data is created and used as
the primary key, and the database (or the user) supplies ascending
unique numbers (or some other unique values, like globally unique
indentifiers). Artificial keys are also called surrogate keys.

The MS Access world frequently prefers artificial keys because of the
way forms and controls work in referencing tables and queries. It is
"easier" to use single artificial key columns, but it is not
necessarily "better" design.

Other regions of the database universe prefer natural keys at all
times.

Other regions of the database universe are ok with using both, where
appropriate.

The arguments for both are extensive and . . . vigorous.


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.
Click to expand...

That looks like the natural primary key of the table.

If you stuck an autonumber column in front of it and made it primary
key, that would be an articial key.
 
I am interested in informed feedback on the use of Constraints, Primary Keys
and Unique.

CREATE TABLE Bands
(BandID varchar(5) CONSTRAINT BandID PRIMARY KEY,
Band varchar(15) CONSTRAINT BandName UNIQUE,
State varchar(3) NOT NULL);
Click to expand...

What is the BandID for the band 'The Clouds That Fondle Jagged Crags
And Raging Storms Conspire And You Will Know Us By The Trail Of Dead'
and how do you put its name into a VARCHAR(15) column said:
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);
Click to expand...

Periods of time are modelled in SQL using a start_date and end_date
pair in the same row. You get to choose the representation: I
recommend closed-closed because it favours BETWEEN constructs (google
for the work of Snodgrass, who prefers the closed-open
representation). You then need a sequenced primary key (lowercase)
constraint to ensure no overlapping periods for each venue. For an
example (which also discusses candidate keys), see

http://groups.google.com/group/microsoft.public.access.forms/msg/04c3f233ba3336cc

....but if you have a Calendar table, a standard trick, then
constraints are even easier to write.

Jamie.

--
 
For an example [of a sequenced primary key]
(which also discusses candidate keys), see

http://groups.google.com/group/microsoft.public.access.forms/msg/04c3...

...but if you have a Calendar table, a standard trick, then
constraints are even easier to write.
Click to expand...

What I mean by 'easier' is that it avoids self joins, the logic of
which can be hard to follow e.g. contrast this:

ALTER TABLE EarningsHistory ADD
CONSTRAINT earnings_history__no_overlapping_periods
CHECK (NOT EXISTS (
SELECT E1.employee_nbr, C1.dt
FROM Calendar AS C1, EarningsHistory AS E1
WHERE C1.dt BETWEEN E1.start_date
AND IIF(E1.end_date IS NULL, NOW(), E1.end_date)
GROUP BY E1.employee_nbr, C1.dt
HAVING COUNT(*) > 1));

with this:

ALTER TABLE EarningsHistory ADD
CONSTRAINT earnings_history__no_overlapping_periods
CHECK (NOT EXISTS (
SELECT *
FROM EarningsHistory, EarningsHistory AS E2
WHERE EarningsHistory.employee_nbr = E2.employee_nbr
AND EarningsHistory.start_date < E2.start_date
AND
(
E2.start_date
< IIF(EarningsHistory.end_date IS NULL, NOW(),
EarningsHistory.end_date)
OR IIF(E2.end_date IS NULL,
NOW(),
E2.end_date) < IIF(EarningsHistory.end_date IS NULL,
NOW(), EarningsHistory.end_date)
)));

Jamie.

--
 
Back
Top