adding rowguidcol

[dba1963]

Hi
I have been trying to add rowguidcol with no success

 

[Jamoncito]

I had the same problem for MSSQL 7 database generation. Here is my solution:

Step 1
Change the internal datatype for the [uniqueidentifier] datatype from VA36 to N36. Now you can define (newid()) as a default value. With VA36, PowerDesigner generates '(newid())' with quotes. As N36 (newid()) is generated without quotes.

Where: Choose "Edit Current DBMS..." in menu "Database". Go to tab "General", tree node Script/DataType/PhysDataType.

Step 2
Add a domain, i.e. "_rowguidcol", with datatype: [uniqueidentifier] and default value: (newid())

The generated script will look like this:

Script sample

EXECUTE sp_addtype _rowguidcol, 'uniqueidentifier', 'not null'
go

CREATE DEFAULT D__rowguidcol
    AS (newid())
go

sp_binddefault D__rowguidcol, _rowguidcol
go

Step 3
Attach this domain to every rowguidcol column.

Step 4
Alter the script for every table with an rowguidcol column. When every table has a rowguidcol column with a constant name, you can make the changes at once by altering the DBMS defenition (Edit Current DBMS...).

In my database every table has a rowguid-column with the name [%Tablename%_id]. This rowguid is added directly after the table creation

Example

CREATE TABLE tablename (
   tablename_id _rowguidcol NOT NULL,
   [...]
    CONSTRAINT pk_tablename PRIMARY KEY (tablename_id)
)
;
go

ALTER TABLE tablename
  ALTER COLUMN tablename_id ADD ROWGUIDCOL;
go

To get this piece of script generated automatically, change the Create script of the table object.

Where: Choose "Edit Current DBMS..." in menu "Database". Go to tab "General", tree node Script/Objects/Table/Create

Original script

CREATE TABLE [%QUALIFIER%]%TABLE% (
%TABLDEFN%
)
[%OPTIONS%]

Additional script

;
go

ALTER TABLE %TCODE%
ALTER COLUMN %TNAME%_id ADD ROWGUIDCOL;

You can also put this additional script manually in the End Script tab of every table definition.

The only thing to do is attaching every rowguidcol column to the _rowguidcol domain.

know issue (help!?!)
With every FK-column, referencing to a column with newid() as default value, I recieve a "Contraint name uniqueness" error while checking the model. I ignore this error, without problems (just a little anoyance). Any suggestions?


Regards,
Ron van der Ham