Database Diagram - Cascade relationship

DavidT_macktool

Well-known member
Joined
Oct 21, 2004
Messages
502
Location
Indiana
Programming Experience
3-5
I cant get the second tier of a relationship to work. Here is the structure:
Setup Operations -> one to many -> Setup Fixtures -> one to many -> Setup Photos.
Keys:
Setup Operations: PartId, OperationNumber
Setup Fixtures: PartId, OperationNumber, FixtureNumber
Setup Photos: PartId, OperationNumber, FixtureNumber, PhotoNumber

I am setting up a relationship that will cascade updates and deletes between the tables. The Setup Operations -> Setup Fixtures relationship works fine, the next level does not.

What I have tried:
At first I had all 3 tables in 1 diagram with relationships set just like I described in the structure. Setup Photos do not cascade when Setup Fixture/FixtureNumber is changed.
Next I split the diagram into 2 separate diagrams.
Setup Operations -> one to many -> Setup Fixtures
Setup Fixtures
-> one to many -> Setup Photos
This configuration led to a FK relation error with the Setup Fixtures table when manipulating the Setup Photos Table.

What I need help with:
Can someone inform me with the correct way to structure this relationship so that if a Setup Operation table OperationNumber is changed all related records are updated. Also if a Setup Fixture table FixtureNumber is changed or deleted all the Setup Photo table records are updated.

I tried to keep this short, I will provide more/better information if requested.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,502
Location
Sydney, Australia
Programming Experience
10+
While it's legal, it's a pretty bad idea to have primary key values changing in a database. If you have a column whose value will change then you should generally not use that column as the primary key. Your SetupOperations table should have a SetupOperationID as the PK which is an FK in SetupFixtures. You can then change the PartID and the OperationNumber as much as you want with no need to cascade any changes to any related tables. Likewise, the SetupFixtures table should have a SetupFixtureID column that is the PK and a FK in SetupPhotos.
 

DavidT_macktool

Well-known member
Joined
Oct 21, 2004
Messages
502
Location
Indiana
Programming Experience
3-5
Makes complete sense. I was hoping to not have to create new tables to replace the inherited ones, but I think that is the best fix. Might as well correct it now and avoid issues later.

Thanks for the information/reply.
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
Why does your photos table have every PK value from the other tables?

If an operation has many fixtures and fixtures have many photos, how can you have a photo that has an operation ID? It breaks the relational aspect because to use the same photo of a fixture in 2 operations you must repeat it in the photos table with a different operationn id

OperationID, OperationData
FixtureID, OperationID, FixtureData
PhotoID, FixtureID, PhotoData

All photos from an operation:

VB.NET:
SELECT * FROM
  operations
  INNER JOIN
  fixtures
  USING(operationID)
 
  INNER JOIN
  photos
  USING(fixtureID)
WHERE 
  operationID = 123
 
Top Bottom