If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Contraints, primary keys
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. |
#2
|
|||
|
|||
Contraints, primary keys
hi Peter,
Peter wrote: CREATE TABLE Bands (BandID varchar(5) CONSTRAINT BandID PRIMARY KEY, Band varchar(15) CONSTRAINT BandName UNIQUE, State varchar(3) NOT NULL); 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); 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? 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) 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 -- |
#3
|
|||
|
|||
Contraints, primary keys
"Peter" wrote in message ... 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? 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. 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. 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. 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. |
#4
|
|||
|
|||
Contraints, primary keys
On Jun 11, 1:03 pm, "Peter" wrote:
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); 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 g? 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); 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/micro...c3f233ba3336cc ....but if you have a Calendar table, a standard trick, then constraints are even easier to write. Jamie. -- |
#5
|
|||
|
|||
Contraints, primary keys
On Jun 11, 3:07 pm, Jamie Collins wrote:
For an example [of a sequenced primary key] (which also discusses candidate keys), see http://groups.google.com/group/micro...forms/msg/04c3... ...but if you have a Calendar table, a standard trick, then constraints are even easier to write. 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. -- |
Thread Tools | |
Display Modes | |
|
|