I’ve done reverse engineering of one of my Postgresql databases, 210 tables, 180 views, 900 functions. RE worked ok, but (it’s not a fault of TDM, is Postgresql that works this way) I wanted to clear the model from some particularities of PG that are not really SQL-standard conform. Default values, for example, with their cast (‘XX’::bpchar), or field constraints (use of ANY ARRAY instead of more classic IN, too many nested parenthesis in other circumstances).
Huge job (by the way, I’ve seen a video about customizing RE operations, that could be a better answer, but frankly actually is too much “advanced” for my knowledge of the tool).
During the process, I planned to prepare some “standard” domains, and associate fields with those domains, so that I can standardize my model, preparing for future enhancements.
But I had to stop, when I discovered that TDM, probably with the idea of protecting the model from my hands does not allow me to change fields referenced in foreign keys. I cannot associate a domain (even if data types correspond). I cannot change default value. I cannot edit constraint. I can do pratically nothing… …
Is there a way to tell TDM “let me touch these fields, at my own risk, please??”
I need to see an example. Please send me small model to modeling@quest.com.
In general, attributes migrate from parent to child. The place where to modify attribute is in parent entity - in other words, if you edit PK attribute in parent table, properties of FK attribute in child table get synchronized automatically.
Click Settings | Options and in section Model | Physical model there is option Attribute Properties Propagation. You can change it to Only Obligatory PK Properties to make other properties changeable.
I have prepared a sample for you, but as is a “dynamic” situation is hard to explain with a “static” model… I try to explain, hoping to be clear:
let’s suppose that the model attached has been imported with RE of a database.
You see, there’s master table table1 and child table table2. You can see that the two are linked by t1field1 --> t2field1. Each one has an existing constraint on these fields, ckc_t1field1 in master table, and ckc_t2field1 in child table.
These two constraints are different. Now, you think is by mistake, and the wrong one is ckc_t2field1… the check constraint in child table … Try to change it please and no, tamper with reference is not allowed
(I’ve hoped attribute properties propagation could do the trick, but didn’t worked… honestly I’ve not seen any difference switching from one to the other)
(test.txp has been prepared with latest beta of TDM) Test.txp (37.7 KB)
you now want to refine a little bit your model, and you create a new domain to associate to all fields like t1field1… You prepare one, with check constraint incorporated, and you associate the domain to t1field1. At this point, the RE check constraint is not useful anymore, because it comes from domain. So you remove it.
But in t2field1 you CANNOT remove the RE check constraint, but use of domain (with check constraint) is propagated, and you find that t2field1 has TWO identical check constraints!!!
(attached you can find new revision of test; please look at SQL preview of table2 and see the two constraints on t2field1; try to remove one!) Test.txp (38.6 KB)
If you assign a Domain with check constraint to PK attribute in parent table and then create new relationship to child table, FK attribute will migrate automatically. Neither in parent nor in child entity (in attribute properties) you will see the check constraint defined in Domain.
I guess you used Foreign Keys Mapping feature in your sample model. This is not recommended in case there is some check constraint on mapped attribute/column.
I can only recommend you to:
a) Keep the newly added (propagated from parent table) attribute and delete existing attribute or
b) Delete check contraint first and then use Foreign Keys Mapping.
(BTW: Domains are logical items and not Domain objects in postgreSQL. If you wish to generate Domain statement, you need to create new Dictionary Item.)
Additional info on domains: It’s OK if you cannot modify values defined in domain. That’s why domains exist. Imagine that you changed default value from N to Y. What should happen to items that you later modified? Would such items be overwritten or not? If you want to use different default values, create different domains.
problem is that this model is obtained back from RE an existing database!!! So i cannot do nor a) neither b) and I’m stuck
About propagation of attributes, I cannot use it: my internal rules states that each field has an unique prefix for each table: so in table customers customer code is called “ac000_cd_customer” while in order table is called “oc000_cd_customer”. Propagation try to name both “ac000_cd_customer” and is unacceptable for me…
I “only” ask to have te option to freely edit/delete the check constraint inside child table’s attribute… is too hard to achieve???
I agree about domains, maybe I didn’t explain myself very well… the problem is NOT the domain…
I explain once again:
a) i reverse engineering a database.
b) the reverse engineered database has table MASTER and table DETAIL. both have a field CUSTOMER. both CUSTOMER attributes have a check constraint (with different naming and potentially different SQL)
c) I realize that the CUSTOMER field in DETAIL table is wrong (beware: it a RE database, so could be…) but I CANNOT TOUCH IT !!!
d) alternative to c) I want to cleanup and standardize a little bit my model, and I create a domain CODE with its own check constraint and default value. I want to assign this domain to CUSTOMER attribute. In master table I associate the attribute AND delete the RE check constraint of the attribute (if I don’t do that check constraint becomes double: one from domain, the other embedded in attribute)… all ok BUT … domain is propagated to CUSTOMER field in DETAIL table (and this is OK) but I CANNOT DELETE THE RE CHECK CONSTRAINT EMBEDDED IN CUSTOMER ATTRIBUTE OF DETAIL TABLE… and I’m stuck
is more clear now???
All can be recap: as soon as you create from scratch your database, I have well understood how to manage, problem is when you deal with a RE database, where you inherit a lot of things… and you can go stuck in a deadlock as I’ve described above…
Regarding check constraints: I looked at the sample model and found out that is was not reverse engineered, but crated in TDM that’s why I thought you had used FK mapping. I was wrong. I made a similar sample database and reverse engineered the structure and finally got the result you had described.
In general, TDM doesn’t allow (or should not allow) you to work with check constraints on FK attributes. That’s how it was designed, but we know it should be changed. I added new change request to our system (CR 95442) and we will provide solution in future versions of TDM.
If you wish to delete check constraints you have two possibilities. Use scripting (such objects can be deleted via scripting) or download and import the following package:
I really appreciate your effort… I’m really impressed…
Unfortunately, I’ve tried your package inside current BETA release of TDM (I use beta because of PG 9.1 support) but … first time I open an attribute window all ok… I press enable button and I do see buttons active… but then if I close the window and try to edit another attribute (any other attribute) TDM goes in infinite loop
Then I close TDM, reopen, and… bang! I obtain the same infinite loop at first try of opening any attribute… (removing package all goes ok then…)…
does not allow me to change fields referenced in foreign keys. I cannot associate a domain (even if data types correspond). I cannot change default value. I cannot edit constraint. I can do pratically nothing… …
and no, tamper with reference is not allowed 