×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

adding rowguidcol

adding rowguidcol

adding rowguidcol

(OP)
Hi
I have been trying to add rowguidcol with no success

RE: adding rowguidcol

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

CODE

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

CODE

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

[u]Original script

CODE

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

[u]Additional script

CODE

;
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close