5 Replies Latest reply: Sep 30, 2013 7:54 AM by Kent Graziano RSS

    How to get standard naming template to apply to column check constraint from domain

    Kent Graziano

      We have several domains (like Y/N Flag) with a constraint definition attached. The DDL generator does build the constraint correctly based on the domain definition but the constraint name is SYS_012456 (or something like that).

       

      I would like to be able to apply the column check constraint name template automatically to all domain inherited constraints. Is there a way to do that?

       

      In addition we would like to have all out NOT NULL constraints automatically named using a template to. Either apply the same column check constraint template or give us a new template for NOT NULL constraints (and maybe an option to use it or not). Again this is because the default NOT NULL gets names SYS_99999 in the database.

        • 1. Re: How to get standard naming template to apply to column check constraint from domain
          AGM

          Kent,

          I do not know if I fully understand your question but I have similar scenarios using columns with domains for flags and I'm using naming standard templates to define these names for column check constraint.

          I'm using DM v3.3.0.747:

          Preferences->Naming Standard -> Templates ->Column Check Constraint: {table abbr}_{column}_CHK

           

          Applying naming rules to column check constraints I'm ensure this standard naming.

          I agree with you that also will be useful to have a way to define a template for the column's property "Not Null Constraint Name"

           

          Hope this help you.

          • 2. Re: How to get standard naming template to apply to column check constraint from domain
            Kent Graziano

            I have the template defined as well but in the case of a domain inherited check constraint, the template is not being applied. Seems like a bug? Or I am missing one other little setting somewhere?

            • 3. Re: How to get standard naming template to apply to column check constraint from domain
              AGM

              Kent,

              in the DM v3.3.0.747 I have defined domains using value lists that lets you specify data values for the domain instead of define a check constraint, example Domain Name: "YesNo" and Value List -> "Y", "N".

              After adding a new column and set with the domain created, I force to apply the standard names to table with the button "Naming Rules", and the constraint created is correctly named . Notice that I need to force to apply the standard names because by default these constraints are created without name.

               

              Now I checked create a domain with check constraint definition for 11g (instead of using a list of values) and the behavior was the same.

               

              I recently upgraded to version 4.0EA2 and it works in the same way for both, so see if forcing to apply the "naming rules" solves your issue.

               

              Regards.

              Ariel.

              • 4. Re: How to get standard naming template to apply to column check constraint from domain
                Philip Stoyanov-Oracle

                Hi Kent,

                 

                as Ariel pointed the name of column check constraint doesn't depend on domain usage.

                About template for name of column not null constraint - I logged an enhancement request, otherwise you can use following script as starting point:

                 

                var nnc = "NNC";
                var undes = "_";
                tables = model.getTableSet().toArray();
                for (var t = 0; t<tables.length;t++){
                 table = tables[t];
                     columns = table.getElements();
                     for (var i = 0; i < columns.length; i++) {
                        column = columns[i];
                        var abbr = java.lang.String.valueOf(column.getAbbreviation());
                        //get column name if there is no abbreviation defined
                        if(abbr == null || "".equals(abbr)){
                            abbr = column.getName();
                        }
                        //set name of not null constraint - prefix+table+column abbr (name)
                        column.setNotNullConstraintName(nnc+undes+table.getName()+undes+abbr);
                     }
                }
                

                 

                Philip