is it possible to create a check constraint on a column using vb script?

[Former Member]

Hi

I would like to know if there is a automated way to create a check constraint on a column across all the tables in the model? For example, I have a current flag column in most of the tables. this column should take only Y or N; is there any easy way to implement this check constraint across all the tables which have this column?

thanks

Krishna

[marc_ledier · Accepted solution]

Hi,

The best approach for this is the use of domains.

You can assign the constraint to domain and it is applied to the columns.

First create your domain with expected constraint.

Then open the list of columns from Model menu and sort or filter them display the ones you want to apply the domain to.

Also display domain if not already shown in list.

Multi-select your columns and select the domain in combo.

Marc

[Former Member · Accepted solution]

Marc's answer is better!

I guess I just like to show off how PowerDesigner scripting is so easy to do....

Domains is a better way to model this because it is where this type of centralized control is meant to be.  By the way, you can also assign domains to columns in the Model -> Columns... list, and sort/filter and multi-apply to make that easy too.

[Former Member · Accepted solution]

Thank you guys. this is really great.

thank you

Krishna

[Former Member]

Yes, here's a sample:

dim modl

set modl=ActiveModel

dim tab, col, dom

dim vals

vals =      "y"&vbTab&"Yes"&vbCrLf

vals = vals&"n"&vbTab&"No" &vbCrLf

for each tab in modl.Tables

   for each col in tab.Columns

      col.ListOfValues=vals

      output col.name&", "&col.ListOfValues

   next

next

This can be run from "Tools -> Execute Commands -> Edit/Run Script

What is does:

Gets the current active model constant and sets a variable to it

Loops for each table in the model

Loops for each column on the table

Sets the ListOfValues property to the string that represents the y/n constraint (this will essentially populate the "List of values" fields in the column checks tab).

Note:  Format of the string is "Value<TAB>Label<LINERETURN>value<TAB>Label<LINERETURN>..."  vbTab represents a Tab character, and vbCrLf is the "return" character.  I like to list the values one each line to make readability easier for longer lists of values, which is why the vals variable is set up the way it is above.

You can also add additional logic (if statements to limit the change only to columns that meet certain criteria, for example) and embed this in a menu or for even fancier automation, as an event handler, but as an event handler, be careful, it could be triggered more frequently than expected and may take a long time to run on large models, so events should be used with caution.  If they do not behave well, model editing performance could go way down.

Also, after running this script and generating DDL, my tables now contain the constaint syntax like the following:

create table ORDER_ITEMS

(

   ITEM_ID              INTEGER              not null

      constraint CKC_ITEM_ID_ORDER_IT check (ITEM_ID in (y,n)),

   ORDERID              CHAR(2)              not null

      constraint CKC_ORDERID_ORDER_IT check (ORDERID in ('y','n')),

   MESAURE              CHAR(10)           

      constraint CKC_MESAURE_ORDER_IT check (MESAURE is null or (MESAURE in ('y','n'))),

   constraint PK_ORDER_ITEMS primary key (ITEM_ID, ORDERID)

);

so it shows the script manipulating the metadata, which in turn comes out in the DDL.