Error fk name length exceeds maximum allowed length 30

Я пытаюсь превратить логическую модель моей базы данных в сценарий DDL, но я не знаю, как исправить эту ошибку в сценарии DDL или Data Modeller: — ОШИБКА: длина имени FK превышает максимально допустимую длину (30)
Кажется, он основан на первичном ключе моей соединительной таблицы, который состоит из двух внешних ключей из двух соседних таблиц.

Я попытался изменить имена первичных ключей в соседних таблицах, но когда я пытаюсь сгенерировать НОВЫЙ сценарий DDL с помощью Data Modeler, он по-прежнему генерирует старый сценарий.

Вот разделы кода, которые создают 3 таблицы и связывают их вместе:

    CREATE TABLE items (
        item_no   NUMBER (8) NOT NULL,
        "year"      DATE,
        price     NUMBER (20,2)
    );

    ALTER TABLE items ADD CONSTRAINT items_pk PRIMARY KEY ( item_no );

    CREATE TABLE purchase_order (
        order_no            NUMBER(8) NOT NULL,
        quantity            INTEGER,
        item_description    VARCHAR2(200),
        unit_price          NUMBER(20,2),
        total               NUMBER(20,2),
        order_date          DATE,
        sales_person_code   VARCHAR2(5) NOT NULL,
        supplier_id         NUMBER(3) NOT NULL
    );

    ALTER TABLE purchase_order ADD CONSTRAINT purchase_order_pk PRIMARY KEY ( order_no );

    CREATE TABLE purchase_order_items (
        purchase_order_order_no   NUMBER(8) NOT NULL,
        items_item_no             NUMBER(8) NOT NULL
    );

    ALTER TABLE purchase_order_items ADD CONSTRAINT purchase_order_items_pk PRIMARY KEY ( items_item_no,
                                                                                          purchase_order_order_no );

    ALTER TABLE purchase_order_items
        ADD CONSTRAINT purchase_order_items_items_fk FOREIGN KEY ( items_item_no )
            REFERENCES items ( item_no );

    --  ERROR: FK name length exceeds maximum allowed length(30) 
    ALTER TABLE purchase_order_items
        ADD CONSTRAINT purchase_order_items_purchase_order_fk FOREIGN KEY ( purchase_order_order_no )
            REFERENCES purchase_order ( order_no );

    ALTER TABLE purchase_order
        ADD CONSTRAINT purchase_order_sales_person_fk FOREIGN KEY ( sales_person_code )
            REFERENCES sales_person ( code );

    ALTER TABLE purchase_order
        ADD CONSTRAINT purchase_order_supplier_fk FOREIGN KEY ( supplier_id )
            REFERENCES supplier ( id );

Поэтому я точно не знаю, какая длина имени FK слишком длинная и что мне нужно изменить в сценарии, чтобы исправить эту ошибку.

Column-name length exceeds maximum length of 30 characters

We were trying to create a table in the mysql database for a complex topic using RTIDDS.
PFA the IDL that we are using — KungfuPandaMovie.idl

When we add the entry corresponding to this IDL in the RTIDDS_SUBSCRIPTIONS file, which is:
«insert into RTIDDS_SUBSCRIPTIONS (table_owner, table_name, domain_Id, topic_name, type_name) values (‘TestDB’, ‘KungfuPandaExample’, 0, ‘Example KungfuPandaMovie’, ‘KungfuPandaMovie’);«
And start the rtirtc_mysql daemon, we get the error:
«RTI Database Integration Service to MySQL, Release 5.2.0.2: startup succeeded
[DDSQLDaemonDynamic_getPrimitiveFieldDcl,line 424:ERROR:50] The column name ‘cast$HeroesInThisMovie$Oougway’ exceeds the maximum length of 30 characters.
Use the XML tags and to control the length of the column name.
[DDSQLDaemonDynamic_createDatabaseTable,line 1787:ERROR:20] Error generating ‘CREATE TABLE’ SQL statement string
[DDSQLDaemonCore_onUpdateMetaTableEntry,line 3476:ERROR:8] Error creating table ‘rts.KungfuPandaExample’
[DDSQLDaemonCore_ConnectionThreadProc,line 4463:ERROR:4] Error creating subscription associated with the table ‘rts.KungfuPandaExample’
«

We tried adding the following entries in the «RTI_REAL_TIME_CONNECT.xml» file which is at the location » /resource/xml»:

$
[
]
512
512

We tried with different values here — 64/256/512/1024, and also restarted the rtirtc_mysql daemon and mysql process before testing.
But still we get this error.
We understand that MySQL database imposes a limit of 64 characters on the column name, but the 30 character limit on column name imposed by RTIDDS can be overcome with the addition of the above two tags in the configuration file.
But here we are not even able to go past the 30 characters limit of RTIDDS, and create a column name of length between 30 to 64 characters.

As per the document: http://community.rti.com/rti-doc/510/RTI_Real-Time_Connect_5.1.0/docs/RTI_RTC_UsersManual.pdf, values -1 (supposedly default value) and 0 should work with and tags, but when we try to put these values in the configuration file, the rtirtc_mysql daemon throws a file parsing error.

Any pointers on this would be appreciated.
Questions:
1. Is there any other way to overcome the 30 characters limit on the column name by RTIDDS? As this solution did not work for us.
2. Is there any way to bypass the 64 character limit on the column name, imposed by MySQL?

Please note:
Platform used: Suse11
Version of RTIDDS: 5.2.0
Version of MySQL: 5.5

Источник

Column-name length exceeds maximum length of 30 characters

We were trying to create a table in the mysql database for a complex topic using RTIDDS.
PFA the IDL that we are using — KungfuPandaMovie.idl

When we add the entry corresponding to this IDL in the RTIDDS_SUBSCRIPTIONS file, which is:
«insert into RTIDDS_SUBSCRIPTIONS (table_owner, table_name, domain_Id, topic_name, type_name) values (‘TestDB’, ‘KungfuPandaExample’, 0, ‘Example KungfuPandaMovie’, ‘KungfuPandaMovie’);«
And start the rtirtc_mysql daemon, we get the error:
«RTI Database Integration Service to MySQL, Release 5.2.0.2: startup succeeded
[DDSQLDaemonDynamic_getPrimitiveFieldDcl,line 424:ERROR:50] The column name ‘cast$HeroesInThisMovie$Oougway’ exceeds the maximum length of 30 characters.
Use the XML tags and to control the length of the column name.
[DDSQLDaemonDynamic_createDatabaseTable,line 1787:ERROR:20] Error generating ‘CREATE TABLE’ SQL statement string
[DDSQLDaemonCore_onUpdateMetaTableEntry,line 3476:ERROR:8] Error creating table ‘rts.KungfuPandaExample’
[DDSQLDaemonCore_ConnectionThreadProc,line 4463:ERROR:4] Error creating subscription associated with the table ‘rts.KungfuPandaExample’
«

We tried adding the following entries in the «RTI_REAL_TIME_CONNECT.xml» file which is at the location » /resource/xml»:

$
[
]
512
512

We tried with different values here — 64/256/512/1024, and also restarted the rtirtc_mysql daemon and mysql process before testing.
But still we get this error.
We understand that MySQL database imposes a limit of 64 characters on the column name, but the 30 character limit on column name imposed by RTIDDS can be overcome with the addition of the above two tags in the configuration file.
But here we are not even able to go past the 30 characters limit of RTIDDS, and create a column name of length between 30 to 64 characters.

As per the document: http://community.rti.com/rti-doc/510/RTI_Real-Time_Connect_5.1.0/docs/RTI_RTC_UsersManual.pdf, values -1 (supposedly default value) and 0 should work with and tags, but when we try to put these values in the configuration file, the rtirtc_mysql daemon throws a file parsing error.

Any pointers on this would be appreciated.
Questions:
1. Is there any other way to overcome the 30 characters limit on the column name by RTIDDS? As this solution did not work for us.
2. Is there any way to bypass the 64 character limit on the column name, imposed by MySQL?

Please note:
Platform used: Suse11
Version of RTIDDS: 5.2.0
Version of MySQL: 5.5

Источник

Suppressing exceeds max allowed length(30) ERROR messages

As far as I understand, the latest Oracle 12c R2 has dropped the 30 char limit for columns etc. Is there already a setting to suppress such messages:

— ERROR: Column name length exceeds maximum allowed length(30)

— ERROR: FK name length exceeds maximum allowed length(30)

That would be very nice 🙂

Best Answer

That is correct.

To take advantage of that, it is best to use a version of SQL Developer that is 12cR2 — 12.2.0.1.0 aware.

SQL Developer 4.2.0.17.088.1709 is.

Synchronize with Data Dictionary worked.

Synchronize Data Dictionary worked.

. without knowing EXACTLY what you did to generate those messages, I am not able to comment further.

Answers

That is correct.

To take advantage of that, it is best to use a version of SQL Developer that is 12cR2 — 12.2.0.1.0 aware.

SQL Developer 4.2.0.17.088.1709 is.

Synchronize with Data Dictionary worked.

Synchronize Data Dictionary worked.

. without knowing EXACTLY what you did to generate those messages, I am not able to comment further.

Источник

C# Error CS7013 – Name exceeds the maximum length allowed in metadata

C# Compiler Error Message

Error CS7013 – Name exceeds the maximum length allowed in metadata

Reason for the Error

You will usually receive the C# compiler error CS7013 Name exceeds the maximum length allowed in metadata error when you have an identifier is 1023 or more characters in length including its qualified name that is not allowed in the metadata of the .NET assembly.

Below is a sample code snippet that generates the error code CS7013 in C#

You receive the below error when running this.

1>C:UsersSenthilsourcereposConsoleApp1ConsoleApp1Program.cs(5,18,5,528): error CS7013: Name ‘ttttZ6prejlojREIEu9juDzgtaAms16VoxweD79tdJWq9Cj1NR9fd32XwfgnlKa5H4C1sL0V4LDMJXwle3tijEFYDLYi1A1sPCKcXyzE36wDsUTQpnuFyl7CmwbLv5FfPwA0Teyuq7ai33zR9tImc59pJ6uLGlmU4cNjvCE0mJhHVSsYqdX8YiE2LOHALeIXzcBs1FCT41yzPsROe7xUm93Gjvw5Yt0vXDdprbETZq4fLRUJvNNbirch1kEj3RinD83hbMy9zDdj5n8WuWoYtDaJaLu82VNM1r09RLwZjDyH8mwZ9gAbe9pW5FdjM7um8F6Zwlm8J3K7h8qSPvBi8XNit9R352Z9FAB5vV3sqkf5SvDgDwTLVE45RzyCVzB8yOxsuGvpGnX43p5KVSrDicyK5QJ9uThDak0QUOGkVtqQBSIPpxjb5YBuQ8K7kYklGPJcexRLtHLPeXgKd2LDrI3Y6MCnbKPd2Gv8tHM6ZUO9vnHF3ZDTOdM3fSKMo4xPQ.tZ6prejlojREIEu9juDzgtaAms16VoxweD79tdJWq9Cj1NR9fd32XwfgnlKa5H4C1sL0V4LDMJXwle3tijEFYDLYi1A1sPCKcXyzE36wDsUTQpnuFyl7CmwbLv5FfPwA0Teyuq7ai33zR9tImc59pJ6uLGlmU4cNjvCE0mJhHVSsYqdX8YiE2LOHALeIXzcBs1FCT41yzPsROe7xUm93Gjvw5Yt0vXDdprbETZq4fLRUJvNNbirch1kEj3RinD83hbMy9zDdj5n8WuWoYtDaJaLu82VNM1r09RLwZjDyH8mwZ9gAbe9pW5FdjM7um8F6Zwlm8J3K7h8qSPvBi8XNit9R352Z9FAB5vV3sqkf5SvDgDwTLVE45RzyCVzB8yOxsuGvpGnX43p5KVSrDicyK5QJ9uThDak0QUOGkVtqQBSIPpxjb5YBuQ8K7kYklGPJcexRLtHLPeXgKd2LDrI3Y6MCnbKPd2Gv8tHM6ZUO9vnHF3ZDTOdM3fSKMo4xPQ’ exceeds the maximum length allowed in metadata.

Solution

To fix the C# compiler error CS7013, ensure that your identifier along with the qualified name is always less than 1024 characters.

In the above example, just remove/ reduce one character form namespace or from class name.

Источник

Attribute exceeding maximum lenght in Azure AD

I’ve tried to enable Azure AD Sync for ThumbnailPhoto attribute in our hybrid deployment, and it came out that three of our on-premises users (me included) got this error:

I’ve tried to remove thumbnailPhoto attribute via ADSI in our local AD, but the error persists. Is there maybe another place I have to check, or maybe I have to edit Azure AD? If so, do you have any idea how can I accomplish that?

Thanks a lot!
Luca

• local_offer Tagged Items
• Microsoft Azure star 4.3
• Active Directory star 4.8

Ransomware 3.0: Prep for A New Malicious Threat Level

5 Replies

I had this problem once before. It was resolved by uninstalling and re-installing the Azure Active Directory Connect software and specifically un-selecting the option «Directory extension attribute sync(Preview)» when installing.

Thanks for the reply. I tried to clear out the attribute with ADSI for the affected users and then editing synching options with Azure AD Connect: I’ll keep this post updated.

Hi. I managed to remove the error for all users but one, which whose thumbnailPhoto attribute I cleaned out. I’d avoid to reinstall Azure AD Connect from scratch since we’re in a hybrid deployment with ADFS authentication and I’d prefer not to touch those settings too. I just don’t understand why I was able to fix all users but not that one, even if the error is exactly the same.

I am having the same issue.

How did you clear out the attribute using ADSI ?

How did you clear out the attribute using ADSI ?

Hi Vishal,
sorry for the late reply, I missed the notification. You can open ADSI Edit (adsiedit.msc) in any server with AD DS role installed.

This topic has been locked by an administrator and is no longer open for commenting.

To continue this discussion, please ask a new question.

Read these next.

Strange server behaviour

I have a Microsoft Server 2019 which is virtualized. It behaves very strange all of a sudden. It takes a VERY long time to RDP into the server, and when I get logged in it is slow as £&¤/(( When I start some program it will start, but it takes forever.Ive.

User wants to leave files open overnight, leads to problems.

We have a user that states they need files to remain open over night so she can pick back up where she left off the next day. The problem is when she leaves them open, we suspect our nightly backup takes issue with those open files, pulls rights and then.

Snap! — Windows 7, AI-written Malware, Fighting Cancer, Air-to-Water Technology

Your daily dose of tech news, in brief. Welcome to the Snap! Flashback: Back on January 9, 2007: Apple Computer, Inc. drops the “Computer” from its official name (Read more HERE.) Bonus Flashback: Back on January 9, 1643: ‘Ashen Light’ di.

if im in over my head

hey guys I’m a fellow graduate to NST program and pending cybersecurity program. I’m wondering if what I’m feeling is normal per say. going into school i didn’t have any it history and so when i went to college for network security technician i was a litt.

What are your career goals for the upcoming year?

January always seems full of possibility and hope. Time to start fresh and work towards who we want to be. In that spirit, I thought it would be good to see who is thinking about where they want to be, career-wise, and really relating to other things too .

Источник

    CREATE TABLE items (
        item_no   NUMBER (8) NOT NULL,
        "year"      DATE,
        price     NUMBER (20,2)
    );

    ALTER TABLE items ADD CONSTRAINT items_pk PRIMARY KEY ( item_no );

    CREATE TABLE purchase_order (
        order_no            NUMBER(8) NOT NULL,
        quantity            INTEGER,
        item_description    VARCHAR2(200),
        unit_price          NUMBER(20,2),
        total               NUMBER(20,2),
        order_date          DATE,
        sales_person_code   VARCHAR2(5) NOT NULL,
        supplier_id         NUMBER(3) NOT NULL
    );

    ALTER TABLE purchase_order ADD CONSTRAINT purchase_order_pk PRIMARY KEY ( order_no );

    CREATE TABLE purchase_order_items (
        purchase_order_order_no   NUMBER(8) NOT NULL,
        items_item_no             NUMBER(8) NOT NULL
    );

    ALTER TABLE purchase_order_items ADD CONSTRAINT purchase_order_items_pk PRIMARY KEY ( items_item_no,
                                                                                          purchase_order_order_no );

    ALTER TABLE purchase_order_items
        ADD CONSTRAINT purchase_order_items_items_fk FOREIGN KEY ( items_item_no )
            REFERENCES items ( item_no );

    --  ERROR: FK name length exceeds maximum allowed length(30) 
    ALTER TABLE purchase_order_items
        ADD CONSTRAINT purchase_order_items_purchase_order_fk FOREIGN KEY ( purchase_order_order_no )
            REFERENCES purchase_order ( order_no );

    ALTER TABLE purchase_order
        ADD CONSTRAINT purchase_order_sales_person_fk FOREIGN KEY ( sales_person_code )
            REFERENCES sales_person ( code );

    ALTER TABLE purchase_order
        ADD CONSTRAINT purchase_order_supplier_fk FOREIGN KEY ( supplier_id )
            REFERENCES supplier ( id );

1 ответ

Лучший ответ

OK guys, I'm struggling.
I've watched the on-line tutorials, read the white papers (especially "Naming Standardization"), and looked through the help pages. There are so many options for controlling naming that I think I'm lost in the trees trying to find the forest. Between the name, short name, synonym, synonym to display, preferred abbreviation, naming rules, glossaries, naming standards, templates, object name prefixes, name abbreviations, name translations, design rules, and classification types, I'm a bit overwhelmed.
I'd like to take this logical model:
---------------------
| facility category |
---------------------
facility category identifier <PK>
     1:1
      |
      |
      |
     0:M
------------
| facility |
------------
facility identifier <PK>
facility category id <FK>and create the following relational model:
-------------
| t_fac_cat |
-------------
fac_cat_id <PK>
  1:1
   |
   |
   |
  0:M
---------
| t_fac |
---------
fac_id <PK>
fac_cat_id <NN,FK>which, in turn, generates this DDL:
create table t_fac (
     fac_id integer  not null ,
     fac_cat_id integer  not null ) ;
alter table t_fac add constraint pk_fac primary key ( fac_id ) ;
create table t_fac_cat ( fac_cat_id integer  not null ) ;
alter table t_fac_cat add constraint pk_fac_cat primary key ( fac_cat_id ) ;
alter table t_fac
    add constraint fk_fac_cat_2_fac foreign key ( fac_cat_id )
    references t_fac_cat ( fac_cat_id ) ;Note: I could live with the relational model table names not being prefixed with a 't_' as long as the DDL includes it.
I think the tool has the ability to do what I want, but I'm missing how all the parts fit together to enable me to accomplish my goal.
Does anyone have a simple approach that might decrease the learning curve a bit? 

Well,
I'll try to explain it in several posts.
I. Name abbreviations tool
this is something separate from big picture but can be useful in some cases.
1. Create a text file containing <name,abbreviation> pairs - name forum.csv and content:
facility,fac
category,cat
identifier,id
2. Set following template for PK name (it's in Naming standard>Templates):
pk_{table}
3. Set following template for FK column name:
{ref column}
4. Set following template for FK name:
fk_{parent abbr}_2_{child abbr}
2), 3) and 4) are according your example
5. Create tables (not entities) in relation model using full names, no abbreviations
following DDL is generated:
CREATE TABLE facility
(
facility_identifier INTEGER NOT NULL ,
facility_category_identifier INTEGER NOT NULL
)
;
ALTER TABLE facility
ADD CONSTRAINT pk_facility PRIMARY KEY ( facility_identifier ) ;
CREATE TABLE facility_category
(
facility_category_identifier INTEGER NOT NULL
)
;
ALTER TABLE facility_category
ADD CONSTRAINT pk_facility_category PRIMARY KEY ( facility_category_identifier ) ;
-- ERROR: FK name length exceeds maximum allowed length(30)
ALTER TABLE facility
ADD CONSTRAINT fk_facility_category_2_facility FOREIGN KEY
(
facility_category_identifier
)
REFERENCES facility_category
(
facility_category_identifier
)
;
6. Invoke "Name abbreviations" and select forum.csv file, leave the rest as it is, press ok - message appear with list of changed objects and here is the new status:
CREATE TABLE fac
(
fac_id INTEGER NOT NULL ,
fac_cat_id INTEGER NOT NULL
)
;
ALTER TABLE fac
ADD CONSTRAINT pk_fac PRIMARY KEY ( fac_id ) ;
CREATE TABLE fac_cat
(
fac_cat_id INTEGER NOT NULL
)
;
ALTER TABLE fac_cat
ADD CONSTRAINT pk_fac_cat PRIMARY KEY ( fac_cat_id ) ;
ALTER TABLE fac
ADD CONSTRAINT fk_fac_cat_2_fac FOREIGN KEY
(
fac_cat_id
)
REFERENCES fac_cat
(
fac_cat_id
)
;
7. Change abbreviations with related full name - it's possible to restore initial status if abbreviations in file (forum.csv in our example) are unique - they are in our case;
- repeat step 6) but now for "Direction" select "Abbreviation to name".
8. Well, if we want to have long names for table and columns (i.e. table "facility_category" and column "facility_category_identifier") we'll get into problem with names for objectors that use these names in their name template (see 'Naming standard>Templates"). We can use table abbreviation and column abbreviation in name templates however we have to define these abbreviations. This can be done manually using dialogs for each table/column or "Name abbreviations" toll can be used.
8.1 change template for primary key to *pk_{table abbr}* - (we already set template for FK as fk_{parent abbr}_2_{child abbr}
8.2 Invoke "Name abbreviations" and select forum.csv file, and select "Abbreviations" for "Scope"; press ok - report is shown for applied changes
9. We need to generate names according changed templates and defined abbreviations. This could be done at relation model level or at table level - at least this is the idea however bug was introduced in patch 1 (build 584) and "Apply naming standards for key and Constraints" at model level works only for foreign key columns. So, we have to apply naming templates at table level. And new DDL is:
CREATE TABLE facility
(
facility_identifier INTEGER NOT NULL ,
facility_category_identifier INTEGER NOT NULL
)
;
ALTER TABLE facility
ADD CONSTRAINT pk_fac PRIMARY KEY ( facility_identifier ) ;
CREATE TABLE facility_category
(
facility_category_identifier INTEGER NOT NULL
)
;
ALTER TABLE facility_category
ADD CONSTRAINT pk_fac_cat PRIMARY KEY ( facility_category_identifier ) ;
ALTER TABLE facility
ADD CONSTRAINT fk_fac_cat_2_fac FOREIGN KEY
(
facility_category_identifier
)
REFERENCES facility_category
(
facility_category_identifier
)
;
10. Logical model - at this point I will transform tables to entities in relational model in order to have starting point for next example. You can check that table abbreviation goes to "Short Name" for entity
Philip 

Philip,
Thanks for your detailed, helpful response. I'm sure it will not only help me, but many others utilizing the forums.
I've worked through your example and have a few thoughts/questions.
My idea was to start with the logical model and provide full names with no abbreviations and spaces between the names. This gives everyone a nice reference in which everything is spelled out and helps prevent confusion over abbreviated names. Thus, I assumed that I would start in the logical model (with full names and spaces) and then engineer to the relational model with the tool automatically creating abbreviations and constraint names, and replacing spaces with underscores.
1) If I define a logical model with full names and spaces and then engineer to the relational model, applying the Name Abbreviations function to the relational model does not work consistently. In my example, only facility changed to fac and pk_facility changed to pk_fac. Everything else stayed the same.
2) In your example, you started modeling with the relational model. Was that preference or is there an advantage to doing things in that order?
3) Is there an advantage to using the Name Abbreviations versus using a Glossary?
4) How would you apply the 't_' prefix to the tables? My thought was to classify each table (I like to use 'fundamental', 'lookup', 'associative', 'super/subtype', and 'system') and associate the 't_' prefix to each class. Then I could use the Change Object Names Prefix in the relational model.
That's all for now. Thanks again for your time.
Brian 

Brian,
I'll quote my post:
I'll try to explain it in several posts.
I. Name abbreviations tool
this is something separate from big picture but can be useful in some cases.I'll come to the rest.
2) In your example, you started modeling with the relational model. Was that preference or is there an advantage to doing things in that order?This explanation will be helpful for people that work mainly on relational and physical models.
3) Is there an advantage to using the Name Abbreviations versus using a Glossary?No, Glossary is the way to go if you want to set your naming standard. "Name Abbreviations" is just a handy tool.
Philip 

II. Prefix manipulation
it works on the whole relational model or can be applied on objects belonging to specific subview. The latter allow easy to set different prefix for each subject area. Wizard works in two modes (invoked in pop-up menu for relational model or its suview):
1) add new prefix
1.1 using classification types - data modeler comes with 5 predefined classification types (see "General options>Diagram>classification types") and new types also can be defined - color and prefix can be defined for each type
select "Add classification prefix"
1.2 select "add new prefix" - new prefix has to be defined in "New prefix" field; also it's good "cases sensitive" to be checked otherwise the names will go all capital letters (bug)
2) change existing prefix with new one - the dialog is explanatory enough - old and new prefix, objects to which will be applied
these changes are permanent - they become part of object name. However these prefixed can be changed during DDL generation process without changing the name of the object.
Steps to do it - it's in DDL generation option dialog when DDL is generated:
1) check "Apply Name substitution"
2) At "Name substitution" tab :
- go to "Object types" and select table
- in "Name substitution" - create substitution definition and check selected, define old prefix (TT_) and new one (TX_)
- generate DDL - there is no change in the model;
Philip 

III. Glossary and name translation
you need glossary in order to have name translation working during engineering between Logical and Relational models.
1. Start Glossary editor - it starts with open file dialog; we don't have glossary, just go to you working directory and type the name of new glossary (my is "forum") - Glossary editor appears
2. you can add your pairs (word,abbreviation) but I'll use shortcut here - select import, then select "Erwin exported names(*.csv)" as import type (it's "Files of Type" combo box); find previously created forum.csv file and open it.
report appear showing definition without classification - we don't have classification there:
Unclassified words:
facility,fac
category,cat
identifier,id
3. Close report and import dialog appears showing all definitions in the file - we'll import all of them
4. We have starting point for our glossary. At this moment we only should check "Incomplete Modifiers" - in short it says glossary is not complete and name parts (that constitute object name) not found in glossary are valid words - you can find more here http://www.oracle.com/technology/products/database/datamodeler/pdf/DataModelerNamingStandards.pdf
-well we can put something for name and description for our glossary. Save and close glossary editor
5. I'll open my existing design (I named it forum) and set preferences in "naming standards" page - settings here could be design specific depending on your settings on "General" page:
- add created glossary
- set _ as separator for relational model and space as separator for logical model
6. There is already relational model and I'll create new one; Select logical model diagram and engineer to relational model - select new one in engineering dialog.
7. Check "Apply name translation" (options tab of engineering dialog) and press "Engineer" button
8. Logical model is transformed to relational and name translation is applied to entities and attributes. The only remaining task is to generate proper names for foreign keys (and/or FK columns) - as I wrote before there is a bug that doesn't allow to set FK names at model level so the only choice is at table level
Well this is the simplest usage of glossary.
Tests:
you can use following pairs to make tests with SCOTT and SH schema
COUNTRY,CTRY,,,,,,,
IDENTIFICATION,ID,,,,,,,
REGION,REG,,,,,,,
DEPARTMENT,DEPT,,,,,,,
MANAGER,MGR,,,,,,,
LOCATION,LOC,,,,,,,
EMPLOYEE,EMP,,,,,,,
FIRST,FIRST,,,,,,,
LAST,LAST,,,,,,,
EMAIL,EMAIL,,,,,,,
PHONE,PH,,,,,,,
NUMBER,NO,,,,,,,
HIRE,HIRE,,,,,,,
DATE,DATE,,,,,,,
JOB,JOB,,,,,,,
SALARY,SAL,,,,,,,
COMMISSION,COMM,,,,,,,
PERCENTAGE,PCT,,,,,,,
TITLE,TITLE,,,,,,,
MINIMUM,MIN,,,,,,,
MAXIMUM,MAX,,,,,,,
START,START,,,,,,,
END,END,,,,,,,
STATE,STATE,,,,,,,
PROVINCE,PROV,,,,,,,
POSTAL,POST,,,,,,,
CODE,CODE,,,,,,,
STREET,STR,,,,,,,
ADDRESS,ADDR,,,,,,,
Lowest,LO,,,,,,,
Highest,HI,,,,,,,
Employee Name,ENAME,,,,,,,
Department Name,DNAME,,,,,,,
Calendar,CAL,,,,,,,
Fiscal,FIS,,,,,,,
Description,DESC,,,,,,,
Product,PROD,,,,,,,
Effective Date,EFF,,,,,,,
Source,SRC,,,,,,,
Customer,CUST,,,,,,,
Promotion,PROMO,,,,,,,
History,HIST,,,,,,,
Year Of Birth,YOB,,,,,,,
NAME,NAME,,,,,,,
Of course you have to import them in glossary.
Notes:
1) Default separator for composite words in glossary is '-' character. Change it to space and press "Apply New separator" button. Save
2) Names in SCOTT schema do not use separators - you have to delete separator character for relational model in Naming standards options
3) you have to set again _ as separator when work on relational model with tables from SH schema. If you engineer logical model to new relational model then you'll see "Effective Date" going to EFF and "Year Of Birth" transformed to YOB
I hope this will help.
Philip

Related

I'm exporting my last weeks relational model project to DDL in hopes of getting a quick accurate way to create my 17 tables. In the relational model, Cust_ID is the PK Yet there is nothing indicating it in the script that is created from the export to DDL File.  This is what it created.  Now when I right click on the customer table in the relational model view, and then pick DDL preview, it adds the alter table line. But when I do that for most of the opther tables including the phones table in the picture below, it's just the fields, no PK or PK info. How do I get the PK and FK's to show up? Thanks, Peter CREATE TABLE Customer  (    Cust_ID INTEGER NOT NULL ,    Name    VARCHAR2 (128) ,    Address VARCHAR2 (128 CHAR)  ) ; ALTER TABLE Customer ADD CONSTRAINT Customer_PK PRIMARY KEY ( Cust_ID ) ;     <------This only shows up in the DDL Preview, not in export to DDL File. The phone table doesn't show any constraints or keys in either view. Same with almost all tables. This is what I get for phones CREATE TABLE Phones  (    Phone_ID INTEGER NOT NULL ,    Phone_Type NVARCHAR2 (10) ,    Phone_Number     CHAR (14) ,    Customer_Cust_ID INTEGER NOT NULL  ) ; 

Hi Peter, A couple of things to check for the missing Primary Key and Foreign Key definitions:1.  The Relational Model dialogs for Primary Keys and Foreign Keys both have "Generate in DDL" check boxes, which are normally set.  Check that these are still set.2.  When you do a DDL Generation, on the DDL Generation Options screen, select the PK and UK Constraints tab and the Foreign Keys tab and check that the "Selected" check boxes for the relevant PKs and FKs are set.  (These settings are remembered from one DDL Generation to the next, and a DDL Preview will normally use the same settings as the most recent DDL Generation.) What version of Data Modeler are you using? David

Thanks those check boxes to generate are in several places but they work, except for the customer table but the DDL view is fine to cut and paste from. I'm on SQL Developer 4.1.1.19. Now I have another related question. Two of the foreign keys created exceed 30 characters. For example, I have a table called Distributor with it's PK as Distributor_ID . So the FK on the Shipment table is Shipment_Distributor_FK (Distributor.Distributor_ID) or something really long that is over 30. So in the relation model I shortened the table names and the longer column names. How do I "refresh" the whole model to reflect the table/field name changes? The keys created are still showing the old longer names.   Peter

Two of the foreign keys created exceed 30 characters. For example, I have a table called Distributor with it's PK as Distributor_ID . So the FK on the Shipment table is Shipment_Distributor_FK (Distributor.Distributor_ID) or something really long that is over 30. So in the relation model I shortened the table names and the longer column names. How do I "refresh" the whole model to reflect the table/field name changes? The keys created are still showing the old longer names.
The initial name given to a Foreign Key is determined by the Foreign Key Template.  This can be set by going to the Design Properties Dialog (by right-clicking on the node for the Design in the Browser tree) and going to its Settings > Naming Standard > Templates page.  This includes Templates for Foreign Keys and for the generated Foreign Key Columns. Unfortunately there is no option to automatically update the names of existing constraints (although you could write a transformation script to do this). David

I've been hacking away before I saw this response. I have a clean DDL script now. Here is the awful way I did it.1)I engineered a new logical model from my relation model.2)I went into domain admin and shortened a few names.3)I shortened some name in the logical model4)I shortened some name in the relations of the browser as suggested above.5)I engineered a new relational model6)Exported the DDL and it worked without error. My last question hopefully is how to I create my tables with this DDL? And are the in one database or just a bunch of tables? I ran the script in worksheet ant it created the tables. Part of my assignment is to add 5 rows to each of my 17 tables. Any efficient way to do this given I have the create script? Or do I have to do 5 x 17 insert statements? Thanks, Peter

Hi,
It's possible keep the name of FK like the PK name with this rules below:
1) I define preferred abbreviation of an PK for CONTASSO_ID, but the name in logical view is CONTAINER ASSOCIADO ID.
2) In the logical model I define a relationship between two tables.
3) In my glossary I define that CONTAINER will be CONT and ASSOCIADO will transform in ASSO.
4) When I do engineering, PK in my TABLE stays contasso_id, but my FK transform in CONT_ASSO_ID
5) I want keep equal FK and PK, but I need use the Glossary for table names, other fields, etc. Is possible to keep the name only for PK and FK ?
What is the better option to do this ?
Thank's 

Yes you can do this easily. What you see now is the default preference setting in the column name template. Go to Tools-> Preferences-> Data Modeler -> Naming Standards -> Templates. Change the entry for "Column Foreign Key" to "{ref column}" and save. After that all generated FK column will have the same name as the parent PK column they are derived from. 

Kent,
Not works, I think first that ODM modify FK using Glossary, by last modify the name of PK using preferred abbreviation, so, my PK have a name and my FK have another name ( with _ )...

Hi
How do i disable the auto foreign keys discoveries/creation completely in sql developer data modeler?
I am using 3.1.4.710.
Thanks! 

The question is not clear, what do you want:
1) to remove FKs generated by FK discovery wizard
2) or to not generate FKs by that wizard
3) to not generate FK columns when FK is created when "new FK" tool is used
Philip 

Hi Philip
Sorry for not able to make it clear enough.
When we define a logical diagram with relationship (say 1 to many), the tool always auto discover the foreign keys and
the engineered Relational diagram/DDL always contain the foreign keys.
Example:
Company Table has ComID as the Primary key.
Staff Table has ComID + Staff_ID as the primary key.
In Relational Diagram or DDL , the file always show there is a foreign key call ComID1 under the Company Table and it is created by Data Modeler program.
CREATE TABLE "Company profile"
(
ComID CHAR (3) NOT NULL ,
"Desc" VARCHAR2 (50) ,
Sts CHAR (1) ,
ComID1 CHAR (3) NOT NULL ,
StaffID CHAR NOT NULL
)
;
We do not want the ComID1 auto discovered or included in the DDL automatically. How should we do ?
PS: In Data Modeler user manual:
Edit menu
Contains options related to the discovery of hidden foreign key relationships.
Create Discovered Foreign Keys: Displays any discovered hidden foreign key relationships in a relational model. (See Create Discovered Foreign Keys.)
Remove Discovered Foreign Keys: Removes any discovered foreign keys from the relational model diagram.
We not able to locate this Screen function "Remove Discovered Foreign Keys" in the Menu, my software version is 3.1.4.710.
Thanks 

Are you saying that the Staff entity (in the logical model) has ComId defined as an attribute and that is defined as part of the Pk? Seems to me from this that you want the Company table (ComId PK) to be the Parent table and that Staff table (ComId+ Staff_Id PK) to be the child (i.e., Staff must be part of one and only one Company. and a Company can have one or more Staff)? Is that correct? (if not, ignore the rest of this and tell us what the final table structure is that you are trying to get)
If so:
1. Your relationship in the logical model appears to be pointed the wrong direction (else the ComId and Staff_Id would not migrate to Company)
2. If you get the relationship going the right way from Company to Staff and define it as an Identifying relationship, then the ComId attribute will migrate to Staff on forward engineering and make it part of the PK. Therefore - you need to delete the ComId attribute from the Staff entity (logical) to get the relational table mode you want. This is how the Oracle/Barker style logical model notation works (whihc is a bit different from the way other tools work) 

Hi
In the logical diagram ,
"Company" has 2 fields, i.e. ComID and Name. ComID is the PK.
"Staff" has "ComID" and StaffID, both are PK.
After export to DDL, Data Modeler created this DDL (See the ComID1 was auto-discovered in Staff Table
CREATE TABLE Company
(
ComID CHAR (3) NOT NULL ,
Name VARCHAR2 (12)
)
;
CREATE TABLE Staff
(
ComID CHAR (3) NOT NULL ,
StaffID VARCHAR2 (16 BYTE) NOT NULL ,
ComID1 CHAR (3) NOT NULL
)
;
I want the Data Modeler create the DDL without the auto-discovered ComID1 in Staff table. That's all.
Thanks 

Hi,
as Kent explained you should not have ComID attribute in entity Staff. When you engineer to relational model you get additional column ComID1as FK column in such case. You can open FK dialog and replace ComID1 column with ComID in FK definition - that at "Associated columns" page. Then you can delete ComID1 column after that. And if you switch to DM 3.3 ComID1 column won't be created again if next engineering to relational model.
Philip 

Thanks Kent & philip!

I created my identity-style primary keys in the logical model as logical type "Integer". On the relational model it's still "Integer", and for Oracle it created them as "number(38,0)" (or as SQL Developer tells me, "number(*,0)"). This is a good result, and I like it. My trouble is how to compare my model to a created database without it reporting that the data types are different and wanting to recreate the tables.
Maybe it's a problem with how I'm doing the comparison, so I'll spell out my steps:
1) File->Import->Data Dictionary
2) I pick my connection, and [Next >].
3) I pick my schema, click "Swap Target Model" to on, and leave the "Import To" as my relational model and "Oracle Database 11g" as the database. [Next >]
4) My tables are already clicked, so I leave them. [Next >]
5) It reports that I have changes in my tables, and there are three in the list:
a) The "Data Type" for the column in question in the model is "INTEGER", and the table is "NUMBER". It's checkmarked as a difference.
b) The "Data Type Kind" for the model is "Logical Type(Integer)", and the table is "Logical Type(NUMERIC)". (This one isn't checkmarked because I turned off the "Compare Option" for "Use 'Data Type Kind' Property".)
c) Under "Indexes", it wants "To Drop" my primary key (index). That's odd, because it still lists the primary key under "PK and UK Constraints". It's not really a problem and I just ignore it, but I wonder if it's related to the data type issue.
If I do "DDL Preview", it's (leaving out some of the storage and logging options):
ALTER TABLE CAM.ADDRESSES DROP CONSTRAINT ADDRESSES_PK CASCADE ;
DROP INDEX CAM.ADDRESSES_PK
;
DROP TRIGGER CAM.ADDRESSES_ROW_ID_TRG
;
ALTER TABLE CAM.ADDRESSES RENAME TO bcp_ADDRESSES
;
CREATE TABLE CAM.ADDRESSES
    (
     ROW_ID INTEGER  NOT NULL ,
[...]
    );
INSERT INTO CAM.ADDRESSES
    (ROW_ID ,[...] )
SELECT
    ROW_ID , [...]
FROM
    bcp_ADDRESSES
;
CREATE UNIQUE INDEX CAM.ADDRESSES_PK ON CAM.ADDRESSES
    (
     ROW_ID ASC
    ) [...]
;
ALTER TABLE CAM.ADDRESSES
    ADD CONSTRAINT ADDRESSES_PK PRIMARY KEY ( ROW_ID  )
    USING INDEX CAM.ADDRESSES_PK ;So it looks like it'd create the column as an "INTEGER" again, so on the next attempt I'd be back where I started.
In the "Types Administration" I tried changing the logical type mapping to "number(38,0)" and then "number(38)", but that didn't help. I didn't write down how it reported the difference -- I think it was that "number(38)" was different from "number". If I changed the type mapping to "number", then it stopped reporting the data type as a difference -- but then it wanted to create the columns as "number", which has a scale, and I'd rather not have one.
Any suggestions on how to get it to leave this alone?
(Version 3.3.0.747. I think that when I was running 3.1.4, it just wanted to alter column addresses modify (row_id integer), which was annoying but I could just delete all of those lines. Now that it wants to recreate the table altogether, it's harder to work around. But maybe the table recreation is due to the index business, which it didn't bother me about before.) 

Hi,
thanks for feedback.
Now that it wants to recreate the table altogetherStorage properties are tracked also in DM 3.3.0.747 - change in data type can lead to "recreate table" sequence if you explicitly set it for that table in "Data type conversion" tab. Some changes in storage properties (there is a "Storage properties" tab you can check it)
can lead to such behavior. You can check "Tabular view" tab (next to "Tree view" tab) - there is a " Generate report " button and you can generate report about changes - the easiest way to see them all.
About Integer data type - it won't report difference if logical type integer is mapped to Integer data type or to Number(38). We'll improve it to cover mapping to Number as well.
Philip 

Thanks for the quick reply and about the storage properties. I'll check into that.
About Integer data type - it won't report difference if logical type integer is mapped to Integer data type or to Number(38). We'll improve it to cover mapping to Number as well.I just did some experiments and learned something about Oracle today. If you create a column as type "INTEGER" and look at the table in SQL Developer (3.0.04)'s Schema Browser's "Columns" tab, it says that the column is of type "NUMBER(38,0)", but it isn't. The USER_TAB_COLUMNS view says that the DATA_PRECISION is null, not 38, with DATA_SCALE 0. That explains why the Modeler sees the model's "INTEGER" or "NUMBER(38)" is different from the database column's precision-less data type. If the database column is explicitly created as a "NUMBER(38,0)", and USER_TAB_COLUMNS.DATA_PRECISION is really 38, the Modeler reports that as type "NUMBER(38)", which is sees as the same as the model's "INTEGER". Very interesting.

HiI have an attribute name called sign with data type varchar (255). The attribute sign is written in lower case. It is part of of the primary key (consisting of four attributes). I engineer the entity to the relational model and it appears in lower case as expected. But if I generate DDL code, the column sign suddenly appears in upper case as SIGN. This is the only column appearing in upper case letters.Best regards,Robert

Hi Robert, I could not reproduce this behaviour.  I get: CREATE TABLE Entity_1    (     abc  VARCHAR2 (22)  NOT NULL ,     def  VARCHAR2 (33)  NOT NULL ,     ghi  VARCHAR2 (44)  NOT NULL ,     sign VARCHAR2 (255)  NOT NULL    );ALTER TABLE Entity_1    ADD CONSTRAINT Entity_1_PK PRIMARY KEY ( sign, abc, def, ghi ) ; Do you have a Physical Model open?  If so, is it possible that the name of the column is overridden in the Physical Model? David

Hi David The magical transformation happens when code generation is done. In DDL preview it's also there. Please see attached detail from my relational model. In the model, the column is sign (lower case). When doing DDL preview (or generating code), sign is suddenly in upper case. I have no idea why. It's in the CREATE TABLE statement as well as in (the third) ALTER TABLE statement. It's the only column with such a behavior. Best regards,Robert    

Hi David A second note: In the constraint for the recursive relationship (the third ALTER TABLE) the order of the foreign key columns are getting changed in the code generation. It should be (in the FOREIGN KEY part as well as the REFERENCES part) (level_number, period_fraction_name, period_school_year, sign). But sign and level_number are reversed compared to the CREATE TABLE statement. In other occurrences in my model this is (rightly) not done by the code generator. Regards,Robert

Hi Robert, Did you check the name of the column in the Physical Model?If the Physical Model is open, this is the name that is used.  (It normally defaults to the name in the Relational Model.) David

Hi David Thanks for your hint. I checked the column name sign in the physical model and it appears also in lower case letters. Have you got an additional idea where I could look for this strange behavior?As a side, note I noticed that the tables under the Tables [no] node are not in alphabetical order. I think it would be nice in alphabetical order. Regards,Robert

A second note: In the constraint for the recursive relationship (the third ALTER TABLE) the order of the foreign key columns are getting changed in the code generation. It should be (in the FOREIGN KEY part as well as the REFERENCES part) (level_number, period_fraction_name, period_school_year, sign). But sign and level_number are reversed compared to the CREATE TABLE statement. In other occurrences in my model this is (rightly) not done by the code generator.
 The Foreign Key columns are generated in the order they appear in the Foreign Key properties.  This may not be the same order as the order of the columns in the Table definition. Regards,David

Modeling is cute when you’re in a class and the sample application is for a rotisserie league for fantasy football, or you need a shopping cart ordering system for online retail. You spend a few hours and might come up with several dozen tables.

But what about in the real world when you are trying to wrap your head around something with several thousand (or more!) objects?

Enter the SubView

SubViews allow the user to take a group of entities or objects from the main model area and place them in a separate space. It’s always easier to break down really big problems into smaller, easier to digest pieces. So instead of having the ENTIRE list of VIEWS from SYS in 11gR2, I might only be looking at the views that the SCHEDULER depends on. There’s only 30 or so of those objects.

There are 2 ways to add an object to a SubView

• Drag the object from the tree into an existing SubView diagram space
• Multi-select the objects from the model space, and choose ‘Create SubView from selected

To get an idea of why this might be necessary, check out how far out I need to zoom to just see all the objects in my model.

Create the SubView

Since my objects are all over the model, I need to drag them from the tree to an existing SubView. So I need to create the SubView first. Easiest way to do that is mouse-right-click on the ‘SubView’ tree parent node and choose ‘New SubView.’ Once it’s created, you’ll see a new model space on the right. If you don’t see it, simply select the new SubView in the tree and mouse-right-click and choose ‘Show Diagram.’

Add the Objects to the new SubView

Find your objects in the model tree, select them, and drag to the SubView.

A few notes about SubViews (from the Help)

“There is no difference between performing changes in one of the subviews or in the complete relational model. Any changes made are immediately reflected in the complete relational model and any relevant subviews. However, you can remove tables and views from a subview without deleting them from the complete relational model.”

So, making changes to objects in the SubView is no different than making changes to the objects in the main modeling space. Also, if you do a RE (fancy acronym for Reverse-Engineer) from multiple schemas, each schema will automatically be represented in the model with a SubView.

OK, so now you have 30+ odd views in a model, now what?

I did something ‘fun.’ After reading Kris’ blog on how to build your own data dictionary view posters, I decided to do the same for my SCHEDULER views. So step 1, use the Tools – Views to Table Wizard for the views. Then – and here’s the tricky part – figure out what SHOULD be the Primary Key (PK) for the tables. Once set, you can right-click on the model and choose ‘Discover Foreign Keys.’ It will use the column names of those PKs and matching column names in other entities to identify what’s ‘related.’

How can we share our model?

Use the ‘File – Print Diagram – …’ feature to export your ERD (or any of the other diagrams) to the format you desire. I chose to share mine via image file (PNG.)