April 27, 2013 at 3:31 am
Hi
There is a DB with 4 tables (3 master tables and one is detail which has daily information)
Table “a” is master table contains 2 fields:
a_code (primary key)
a_name
Table “b1” is master table contains 3 fields:
a_code (primary key)
b1_code (primary key)
b1_name
Table “b2” is such as table”b1”. b2 is master table contains 3 fields:
a_code (primary key)
b2_code (primary key)
b2_name
Table “C” is detail table contains 5 fileds:
a_code (primary key)
b1_code (primary key)
b2_code (primary key)
C_code (primary key)
C_name
Field “a_code” is primary key in table “a” and also it is foreign key in tables “b1” and “b2”.
b1_code and b2_code are a part of primary key in their tables and are also foreign key in tables “C”.
Now we wants create a Relation between tables. So that table C be Related with b1 and b2 then tables b1 and b2
Related with a.
In other words, table c is sub table of b1 and b2. And tables b1 and b2 are sub tables of a.
In other hands, table a is first level . tables b1 and b2 are in second level and table c is in third level.
Now it is necessary select Cascade for update fields.
First question:
Table c has 2 Relations . one Relation with table b1 and another Relation with b2. But only one Relation can de cascade for update. The second Relation will be faced to error.
Error:
'b2' table saved successfully
'c' table
- Unable to create Relationship 'FK_c_b2'.
Introducing FOREIGN KEY constraint 'FK_c_b2' on table 'c' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.
Second question:
The code fields which are primary keys (a_code, b1_code, b2_code, c_code). They are hidden for user. User can not view them. When you add new record to table , the code field will be creased automatically. on the other hand the field “a_code” not necessary to be changed when you edit field “a_name”. therefore it is not necessary to create Relation and also to set Cascade update to child tables. Now I want to ask, is my DB a standard DB in this case?
Third question:
Field a_name can provide my aim . therefore what is necessary to field a_code? In a standard DB field a_code must be created or not?
Thanks very much
April 27, 2013 at 5:50 am
Okay, not sure what is going on here as I am not making much sense from your ramblings.
What I did get is that the primary key columns of the four tables are not visible to the users and the data they contain is generated by the system (or the database itself). If this is the case, why would any of these columns be updated after their initial creation?
These sound like they are SIDs, surrogate ids, and as such should never have to change as a result of data changes in the records they identify.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply