- Local time
- Today, 03:43
- Joined
- Feb 19, 2002
- Messages
- 43,424
tblParts is the part master. It must include EVERY detail part. Part # 141 is missing from tblParts and that is what is causing the issue. Once you add that part to tblParts, you will be able to enforce RI. Not every part in tblParts will exist in tblAssemblyItems BUT every part in tblAssemblyItems WILL exist in tblParts - this is backwards from what you said. tblParts is the "parent" and tblAssemblyItems is the "child" in this relationship. The "parent" is the table where the related field is the primary key. The "child" is the table where the related field is a non-key field or one of the fields in a composite key PK. When tbl1.PK points to tbl2.PK, the relatioship is 1-1. This is an extremely unusual relationship. Normally, you would simply use a single table. There are a couple of reasons to use two tables in a 1-1 relationship.
1. Security. Some if the data is extra sensitive and you don't want to give permissions to that table to everyone. Depending on the RDBMS, you might be able to accomplish this by field level security bur not all RDBMS (such as Jet/ACE) support this feature.
2. Sparseness. Sometimes some of the data is rarely present and so purists will put the sparse fields in a second table.
3. Rare use. Sometimes there are large data items that are rarely referenced. It is more efficient to simply separate them so that the bulk of the table processing takes place against a much smaller physical data set.
4. Grouping of entities. When you have disparate entities such as students, faculty, vendors, guardians that have many common uses, it makes sense to define them in an entity table but keep their separate data types in individual tables. That allows you to use one address table and one contact info table for example and relate it to the entity "parent" rather than to each different "child".
1. Security. Some if the data is extra sensitive and you don't want to give permissions to that table to everyone. Depending on the RDBMS, you might be able to accomplish this by field level security bur not all RDBMS (such as Jet/ACE) support this feature.
2. Sparseness. Sometimes some of the data is rarely present and so purists will put the sparse fields in a second table.
3. Rare use. Sometimes there are large data items that are rarely referenced. It is more efficient to simply separate them so that the bulk of the table processing takes place against a much smaller physical data set.
4. Grouping of entities. When you have disparate entities such as students, faculty, vendors, guardians that have many common uses, it makes sense to define them in an entity table but keep their separate data types in individual tables. That allows you to use one address table and one contact info table for example and relate it to the entity "parent" rather than to each different "child".