I have designed a database program to keep track of Cemetery listings. I am an amateur genealogist. I have disigned one table, called Cemetery, in which I place the Cemetery general information with the ID field as the key field. Then I created a Person Information, with cemetery field as the key field. This is also the field I have linked to. The Person Information is the child and Cemetery is the parent. In the secondary index section I have designated the index as unique in Cemetery and as maintained in Personal information. When I try to enter information in the Personal Information the first record enters alright. When I try to enter another record, however, I get the error message I have committed a key violation. Can anyone tell me what I have done wrong? Thanks, Ray Holt
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Key violations
- Thread starter mrholtsr
- Start date
beanbrain
Programmer
- Dec 31, 2001
- 170
This might work better if you designate the person field a key field also. If you have more than one person with the same name then this will also be a problem. Actually I don't think you need to have the Person table keyed at all, unless of course, you are trying to establish an order in the Person table as entries are made.
But what a single primary index does is ensure that no duplicate entries can be made in that particular field. Multiple keys spread that uniqueness out over the the number of fields you make indexes.
But what a single primary index does is ensure that no duplicate entries can be made in that particular field. Multiple keys spread that uniqueness out over the the number of fields you make indexes.
Beanbrain is exactly right, the child table must have a composite key that is one step deeper than the parent. For example:
Cemetary.db
-----------
CemetaryID*
CemetaryName
CemetaryState
etc ...
Person.db
---------
CemetaryID*
PersonID*
PersonLname
PersonFname
etc ...
This would allow unlimited persons per cemetary with no key violations.
Just in case you are thinking about using an autoincrement field to generate your ID numbers, you should consider creating your own numbering system instead. If you are unfamiliar with how to do that and you are in a hurry, you should only use the AI field on the PersonID, as under this structure it serves as a unique value only. The problem with even this approach is that you may later want to enlarge your database and feed an additional child table from the Person.db.
Mac
"There are only 10 kinds of people in this world... those who understand binary and those who don't"
langley_mckelvy@cd4.co.harris.tx.us
Cemetary.db
-----------
CemetaryID*
CemetaryName
CemetaryState
etc ...
Person.db
---------
CemetaryID*
PersonID*
PersonLname
PersonFname
etc ...
This would allow unlimited persons per cemetary with no key violations.
Just in case you are thinking about using an autoincrement field to generate your ID numbers, you should consider creating your own numbering system instead. If you are unfamiliar with how to do that and you are in a hurry, you should only use the AI field on the PersonID, as under this structure it serves as a unique value only. The problem with even this approach is that you may later want to enlarge your database and feed an additional child table from the Person.db.
Mac
"There are only 10 kinds of people in this world... those who understand binary and those who don't"
langley_mckelvy@cd4.co.harris.tx.us
- Status