Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

strange DDL-failure

7 posts in General Discussion Last posting was on 2003-10-31 13:31:12.0Z
Uwe Sauerbrey Posted on 2003-10-30 12:38:46.0Z
From: "Uwe Sauerbrey" <sauerbrey@solution-execute.de>
Newsgroups: ianywhere.public.general
Subject: strange DDL-failure
Lines: 255
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="----=_NextPart_000_0015_01C39EF2.6B36E5E0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: p3ee273d2.dip.t-dialin.net
X-Original-NNTP-Posting-Host: p3ee273d2.dip.t-dialin.net
Message-ID: <3fa11466@forums-1-dub>
Date: 30 Oct 2003 05:38:46 -0700
X-Trace: forums-1-dub 1067521126 62.226.115.210 (30 Oct 2003 05:38:46 -0700)
X-Original-Trace: 30 Oct 2003 05:38:46 -0700, p3ee273d2.dip.t-dialin.net
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1961
Article PK: 4205

Hi,
 
I'm using ASA 8.0.2.3601 on Win XP professional.
 
I experienced a strange error when I ran some DDL-Statements. They were generated from PowerDesigner 8.
The statements:
 
/*==============================================================*/
/* Table: T_REPFKSPALTEN                                        */
/*==============================================================*/
create table T_REPFKSPALTEN
(
    REPOSITORY           varchar(50)                    not null,
    FREMDSCHLUESSEL      varchar(50)                    not null,
    SPALTE               varchar(50)                    not null,
    AKTIV                bit                            default 1,
    ERFASSER             varchar(50)                    default current user,
    ERFDATUM             timestamp                      default current timestamp,
    GEAENDERTVON         varchar(50)                    default last user,
    GEAENDERTAM          timestamp                      default timestamp,
    primary key (FREMDSCHLUESSEL, SPALTE, REPOSITORY)
);
 
/*==============================================================*/
/* Table: T_REPSPALTEN                                          */
/*==============================================================*/
create table T_REPSPALTEN
(
    REPOSITORY           varchar(50)                    not null,
    TABELLE              varchar(50)                    not null,
    SPALTE               varchar(50)                    not null,
    AKTIV                bit                            default 1,
    ERFASSER             varchar(50)                    default current user,
    ERFDATUM             timestamp                      default current timestamp,
    GEAENDERTVON         varchar(50)                    default last user,
    GEAENDERTAM          timestamp                      default timestamp,
    DATENTYP             varchar(50),
    PK                   bit,
    NULLABLE             bit,
    STANDARDWERT         varchar(30000),
    INTEGRITAET          varchar(30000),
    primary key (SPALTE, REPOSITORY, TABELLE)
);
 
alter table T_REPFKSPALTEN
   add foreign key T_FKSPALTEN_REF_T_SPALTEN (SPALTE, REPOSITORY)
      references T_REPSPALTEN (SPALTE, REPOSITORY)
      on update cascade
      on delete cascade;
 
At the stage of the creation of the foreign key, I get the error "-113 column "Spalte" in the foreign key has a different definition than in the primary key".  
 
But that isn't true.
 
Can someone point me in the right direction?
 
TIA
Uwe
 
 


"Bruce Hay" Posted on 2003-10-30 13:45:50.0Z
From: "Bruce Hay" <hay at sybase dot com>
Newsgroups: ianywhere.public.general
References: <3fa11466@forums-1-dub>
Subject: Re: strange DDL-failure
Lines: 79
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: hay-xp.sybase.com
X-Original-NNTP-Posting-Host: hay-xp.sybase.com
Message-ID: <3fa1241e$1@forums-1-dub>
Date: 30 Oct 2003 06:45:50 -0700
X-Trace: forums-1-dub 1067525150 172.31.142.57 (30 Oct 2003 06:45:50 -0700)
X-Original-Trace: 30 Oct 2003 06:45:50 -0700, hay-xp.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1964
Article PK: 4203

The primary table has a 3-column primary key. To create a foreign key
referencing this table, the foreign key must also use three columns.

Given the sizes of the columns in the current primary keys, you might
consider using surrogate integer keys for efficiency.

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

"Uwe Sauerbrey" <sauerbrey@solution-execute.de> wrote in message
news:3fa11466@forums-1-dub...
Hi,

I'm using ASA 8.0.2.3601 on Win XP professional.

I experienced a strange error when I ran some DDL-Statements. They were
generated from PowerDesigner 8.
The statements:

/*==============================================================*/
/* Table: T_REPFKSPALTEN */
/*==============================================================*/
create table T_REPFKSPALTEN
(
REPOSITORY varchar(50) not null,
FREMDSCHLUESSEL varchar(50) not null,
SPALTE varchar(50) not null,
AKTIV bit default 1,
ERFASSER varchar(50) default current
user,
ERFDATUM timestamp default current
timestamp,
GEAENDERTVON varchar(50) default last user,
GEAENDERTAM timestamp default timestamp,
primary key (FREMDSCHLUESSEL, SPALTE, REPOSITORY)
);

/*==============================================================*/
/* Table: T_REPSPALTEN */
/*==============================================================*/
create table T_REPSPALTEN
(
REPOSITORY varchar(50) not null,
TABELLE varchar(50) not null,
SPALTE varchar(50) not null,
AKTIV bit default 1,
ERFASSER varchar(50) default current
user,
ERFDATUM timestamp default current
timestamp,
GEAENDERTVON varchar(50) default last user,
GEAENDERTAM timestamp default timestamp,
DATENTYP varchar(50),
PK bit,
NULLABLE bit,
STANDARDWERT varchar(30000),
INTEGRITAET varchar(30000),
primary key (SPALTE, REPOSITORY, TABELLE)
);

alter table T_REPFKSPALTEN
add foreign key T_FKSPALTEN_REF_T_SPALTEN (SPALTE, REPOSITORY)
references T_REPSPALTEN (SPALTE, REPOSITORY)
on update cascade
on delete cascade;

At the stage of the creation of the foreign key, I get the error "-113
column "Spalte" in the foreign key has a different definition than in the
primary key".

But that isn't true.

Can someone point me in the right direction?

TIA
Uwe


Uwe Sauerbrey Posted on 2003-10-30 14:03:39.0Z
From: "Uwe Sauerbrey" <sauerbrey@solution-execute.de>
Newsgroups: ianywhere.public.general
References: <3fa11466@forums-1-dub> <3fa1241e$1@forums-1-dub>
Subject: Re: strange DDL-failure
Lines: 101
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: p3ee273d2.dip.t-dialin.net
X-Original-NNTP-Posting-Host: p3ee273d2.dip.t-dialin.net
Message-ID: <3fa1284b@forums-1-dub>
Date: 30 Oct 2003 07:03:39 -0700
X-Trace: forums-1-dub 1067526219 62.226.115.210 (30 Oct 2003 07:03:39 -0700)
X-Original-Trace: 30 Oct 2003 07:03:39 -0700, p3ee273d2.dip.t-dialin.net
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1966
Article PK: 31454

uups.. I didn't see that.
Of course that won't work.

Well, concerning the integer keys, would you say that an integer-pk will be
more efficient in any case? I defined the cols as varchar(50), but I think
the average length will be around 10 characters. I know that one
professional says, that the pk shouldn't have anything to do with the data.
So integer-PKs should be used. The other says that some kind of
autoincrement-columns are overhead and it would be more efficient to use
data-cols for the primary key.

I'm a bit confused of this different flavours, so some time ago I decided
always to use data-columns for pk. Do you have some more pros and cons?

Thanks
Uwe


"Bruce Hay" <hay at sybase dot com> schrieb im Newsbeitrag
news:3fa1241e$1@forums-1-dub...

> The primary table has a 3-column primary key. To create a foreign key
> referencing this table, the foreign key must also use three columns.
>
> Given the sizes of the columns in the current primary keys, you might
> consider using surrogate integer keys for efficiency.
>
> Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
> Developer Community at http://www.ianywhere.com/developer
>
> "Uwe Sauerbrey" <sauerbrey@solution-execute.de> wrote in message
> news:3fa11466@forums-1-dub...
> Hi,
>
> I'm using ASA 8.0.2.3601 on Win XP professional.
>
> I experienced a strange error when I ran some DDL-Statements. They were
> generated from PowerDesigner 8.
> The statements:
>
> /*==============================================================*/
> /* Table: T_REPFKSPALTEN */
> /*==============================================================*/
> create table T_REPFKSPALTEN
> (
> REPOSITORY varchar(50) not null,
> FREMDSCHLUESSEL varchar(50) not null,
> SPALTE varchar(50) not null,
> AKTIV bit default 1,
> ERFASSER varchar(50) default current
> user,
> ERFDATUM timestamp default current
> timestamp,
> GEAENDERTVON varchar(50) default last user,
> GEAENDERTAM timestamp default timestamp,
> primary key (FREMDSCHLUESSEL, SPALTE, REPOSITORY)
> );
>
> /*==============================================================*/
> /* Table: T_REPSPALTEN */
> /*==============================================================*/
> create table T_REPSPALTEN
> (
> REPOSITORY varchar(50) not null,
> TABELLE varchar(50) not null,
> SPALTE varchar(50) not null,
> AKTIV bit default 1,
> ERFASSER varchar(50) default current
> user,
> ERFDATUM timestamp default current
> timestamp,
> GEAENDERTVON varchar(50) default last user,
> GEAENDERTAM timestamp default timestamp,
> DATENTYP varchar(50),
> PK bit,
> NULLABLE bit,
> STANDARDWERT varchar(30000),
> INTEGRITAET varchar(30000),
> primary key (SPALTE, REPOSITORY, TABELLE)
> );
>
> alter table T_REPFKSPALTEN
> add foreign key T_FKSPALTEN_REF_T_SPALTEN (SPALTE, REPOSITORY)
> references T_REPSPALTEN (SPALTE, REPOSITORY)
> on update cascade
> on delete cascade;
>
> At the stage of the creation of the foreign key, I get the error "-113
> column "Spalte" in the foreign key has a different definition than in the
> primary key".
>
> But that isn't true.
>
> Can someone point me in the right direction?
>
> TIA
> Uwe
>
>
>


Breck Carter [TeamSybase] Posted on 2003-10-30 14:49:04.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: strange DDL-failure
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <lub2qv8tf604isisrj5e3in75lq13eonpp@4ax.com>
References: <3fa11466@forums-1-dub> <3fa1241e$1@forums-1-dub> <3fa1284b@forums-1-dub>
X-Newsreader: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 30 Oct 2003 07:49:04 -0700
X-Trace: forums-1-dub 1067528944 64.7.134.118 (30 Oct 2003 07:49:04 -0700)
X-Original-Trace: 30 Oct 2003 07:49:04 -0700, bcarter.sentex.ca
Lines: 122
X-Authenticated-User: TeamPS
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1970
Article PK: 4206

One rule of thumb is to never *change* a primary key value. If a
column is going to change in value, make it part of an index or a
unique constraint but use only *unchanging* columns for primary keys.
For MobiLink, in particular, that's a *really* good idea. But it also
makes life simpler in non-synchronizing situations... no more ON
UPDATE CASCADE crap.

As far as the pros and cons are concerned, you will find equally
strident arguments on both sides, it's a take-no-prisoners battle for
a lot of people. Me, I like to annoy *everyone* and have a mix of
business and surrogate keys whenever I can :)

Breck

On 30 Oct 2003 07:03:39 -0700, "Uwe Sauerbrey"

<sauerbrey@solution-execute.de> wrote:

>uups.. I didn't see that.
>Of course that won't work.
>
>Well, concerning the integer keys, would you say that an integer-pk will be
>more efficient in any case? I defined the cols as varchar(50), but I think
>the average length will be around 10 characters. I know that one
>professional says, that the pk shouldn't have anything to do with the data.
>So integer-PKs should be used. The other says that some kind of
>autoincrement-columns are overhead and it would be more efficient to use
>data-cols for the primary key.
>
>I'm a bit confused of this different flavours, so some time ago I decided
>always to use data-columns for pk. Do you have some more pros and cons?
>
>Thanks
>Uwe
>
>
>"Bruce Hay" <hay at sybase dot com> schrieb im Newsbeitrag
>news:3fa1241e$1@forums-1-dub...
>> The primary table has a 3-column primary key. To create a foreign key
>> referencing this table, the foreign key must also use three columns.
>>
>> Given the sizes of the columns in the current primary keys, you might
>> consider using surrogate integer keys for efficiency.
>>
>> Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
>> Developer Community at http://www.ianywhere.com/developer
>>
>> "Uwe Sauerbrey" <sauerbrey@solution-execute.de> wrote in message
>> news:3fa11466@forums-1-dub...
>> Hi,
>>
>> I'm using ASA 8.0.2.3601 on Win XP professional.
>>
>> I experienced a strange error when I ran some DDL-Statements. They were
>> generated from PowerDesigner 8.
>> The statements:
>>
>> /*==============================================================*/
>> /* Table: T_REPFKSPALTEN */
>> /*==============================================================*/
>> create table T_REPFKSPALTEN
>> (
>> REPOSITORY varchar(50) not null,
>> FREMDSCHLUESSEL varchar(50) not null,
>> SPALTE varchar(50) not null,
>> AKTIV bit default 1,
>> ERFASSER varchar(50) default current
>> user,
>> ERFDATUM timestamp default current
>> timestamp,
>> GEAENDERTVON varchar(50) default last user,
>> GEAENDERTAM timestamp default timestamp,
>> primary key (FREMDSCHLUESSEL, SPALTE, REPOSITORY)
>> );
>>
>> /*==============================================================*/
>> /* Table: T_REPSPALTEN */
>> /*==============================================================*/
>> create table T_REPSPALTEN
>> (
>> REPOSITORY varchar(50) not null,
>> TABELLE varchar(50) not null,
>> SPALTE varchar(50) not null,
>> AKTIV bit default 1,
>> ERFASSER varchar(50) default current
>> user,
>> ERFDATUM timestamp default current
>> timestamp,
>> GEAENDERTVON varchar(50) default last user,
>> GEAENDERTAM timestamp default timestamp,
>> DATENTYP varchar(50),
>> PK bit,
>> NULLABLE bit,
>> STANDARDWERT varchar(30000),
>> INTEGRITAET varchar(30000),
>> primary key (SPALTE, REPOSITORY, TABELLE)
>> );
>>
>> alter table T_REPFKSPALTEN
>> add foreign key T_FKSPALTEN_REF_T_SPALTEN (SPALTE, REPOSITORY)
>> references T_REPSPALTEN (SPALTE, REPOSITORY)
>> on update cascade
>> on delete cascade;
>>
>> At the stage of the creation of the foreign key, I get the error "-113
>> column "Spalte" in the foreign key has a different definition than in the
>> primary key".
>>
>> But that isn't true.
>>
>> Can someone point me in the right direction?
>>
>> TIA
>> Uwe
>>
>>
>>
>

--
bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com


Uwe Sauerbrey Posted on 2003-10-31 12:32:37.0Z
From: "Uwe Sauerbrey" <sauerbrey@solution-execute.de>
Newsgroups: ianywhere.public.general
References: <3fa11466@forums-1-dub> <3fa1241e$1@forums-1-dub> <3fa1284b@forums-1-dub>
Subject: Re: strange DDL-failure
Lines: 130
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: p3ee2747b.dip.t-dialin.net
X-Original-NNTP-Posting-Host: p3ee2747b.dip.t-dialin.net
Message-ID: <3fa25665$1@forums-1-dub>
Date: 31 Oct 2003 04:32:37 -0800
X-Trace: forums-1-dub 1067603557 62.226.116.123 (31 Oct 2003 04:32:37 -0800)
X-Original-Trace: 31 Oct 2003 04:32:37 -0800, p3ee2747b.dip.t-dialin.net
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1979
Article PK: 4207

Thank you both for your input!

I think I'll go and annoy everyone in the future, like Breck does. Although
I don't love surrogate integer keys.

Some time ago I used numeric(10) and identity as primary key for
customer-rows. This values did reflect the customer number which was known
and used by the users of the app. Then I had to implement number-ranges and
different usergroups did use different ranges. This was the moment where
identity didn't fit any longer. I wrote my own procedure to get the next
free number out of a range. But then I also had to implement a routine which
locked a number whenever an insert was going to start. What if the insert
fails? I had the problem that the number had to be released when the planned
insert didn't take place. It was a mess.

Anyway, thanks again and have fun!
Uwe

"Uwe Sauerbrey" <sauerbrey@solution-execute.de> schrieb im Newsbeitrag
news:3fa1284b@forums-1-dub...

> uups.. I didn't see that.
> Of course that won't work.
>
> Well, concerning the integer keys, would you say that an integer-pk will
be
> more efficient in any case? I defined the cols as varchar(50), but I think
> the average length will be around 10 characters. I know that one
> professional says, that the pk shouldn't have anything to do with the
data.
> So integer-PKs should be used. The other says that some kind of
> autoincrement-columns are overhead and it would be more efficient to use
> data-cols for the primary key.
>
> I'm a bit confused of this different flavours, so some time ago I decided
> always to use data-columns for pk. Do you have some more pros and cons?
>
> Thanks
> Uwe
>
>
> "Bruce Hay" <hay at sybase dot com> schrieb im Newsbeitrag
> news:3fa1241e$1@forums-1-dub...
> > The primary table has a 3-column primary key. To create a foreign key
> > referencing this table, the foreign key must also use three columns.
> >
> > Given the sizes of the columns in the current primary keys, you might
> > consider using surrogate integer keys for efficiency.
> >
> > Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
> > Developer Community at http://www.ianywhere.com/developer
> >
> > "Uwe Sauerbrey" <sauerbrey@solution-execute.de> wrote in message
> > news:3fa11466@forums-1-dub...
> > Hi,
> >
> > I'm using ASA 8.0.2.3601 on Win XP professional.
> >
> > I experienced a strange error when I ran some DDL-Statements. They were
> > generated from PowerDesigner 8.
> > The statements:
> >
> > /*==============================================================*/
> > /* Table: T_REPFKSPALTEN */
> > /*==============================================================*/
> > create table T_REPFKSPALTEN
> > (
> > REPOSITORY varchar(50) not null,
> > FREMDSCHLUESSEL varchar(50) not null,
> > SPALTE varchar(50) not null,
> > AKTIV bit default 1,
> > ERFASSER varchar(50) default current
> > user,
> > ERFDATUM timestamp default current
> > timestamp,
> > GEAENDERTVON varchar(50) default last
user,
> > GEAENDERTAM timestamp default
timestamp,
> > primary key (FREMDSCHLUESSEL, SPALTE, REPOSITORY)
> > );
> >
> > /*==============================================================*/
> > /* Table: T_REPSPALTEN */
> > /*==============================================================*/
> > create table T_REPSPALTEN
> > (
> > REPOSITORY varchar(50) not null,
> > TABELLE varchar(50) not null,
> > SPALTE varchar(50) not null,
> > AKTIV bit default 1,
> > ERFASSER varchar(50) default current
> > user,
> > ERFDATUM timestamp default current
> > timestamp,
> > GEAENDERTVON varchar(50) default last
user,
> > GEAENDERTAM timestamp default
timestamp,
> > DATENTYP varchar(50),
> > PK bit,
> > NULLABLE bit,
> > STANDARDWERT varchar(30000),
> > INTEGRITAET varchar(30000),
> > primary key (SPALTE, REPOSITORY, TABELLE)
> > );
> >
> > alter table T_REPFKSPALTEN
> > add foreign key T_FKSPALTEN_REF_T_SPALTEN (SPALTE, REPOSITORY)
> > references T_REPSPALTEN (SPALTE, REPOSITORY)
> > on update cascade
> > on delete cascade;
> >
> > At the stage of the creation of the foreign key, I get the error "-113
> > column "Spalte" in the foreign key has a different definition than in
the
> > primary key".
> >
> > But that isn't true.
> >
> > Can someone point me in the right direction?
> >
> > TIA
> > Uwe
> >
> >
> >
>
>


Breck Carter [TeamSybase] Posted on 2003-10-31 13:31:12.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: strange DDL-failure
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <ego4qv0phhcvmjea8b5qsjom2ujv8j63f3@4ax.com>
References: <3fa11466@forums-1-dub> <3fa1241e$1@forums-1-dub> <3fa1284b@forums-1-dub> <3fa25665$1@forums-1-dub>
X-Newsreader: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
X-Original-Trace: 31 Oct 2003 05:35:56 -0800, bcarter.sentex.ca
Lines: 140
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 31 Oct 2003 05:23:24 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 31 Oct 2003 05:31:12 -0800
X-Trace: forums-1-dub 1067607072 10.22.108.75 (31 Oct 2003 05:31:12 -0800)
X-Original-Trace: 31 Oct 2003 05:31:12 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1980
Article PK: 17347

UNSIGNED BIGINT works well, as does DEFAULT GLOBAL AUTOINCREMENT, for
what it's worth.

On 31 Oct 2003 04:32:37 -0800, "Uwe Sauerbrey"

<sauerbrey@solution-execute.de> wrote:

>Thank you both for your input!
>
>I think I'll go and annoy everyone in the future, like Breck does. Although
>I don't love surrogate integer keys.
>
>Some time ago I used numeric(10) and identity as primary key for
>customer-rows. This values did reflect the customer number which was known
>and used by the users of the app. Then I had to implement number-ranges and
>different usergroups did use different ranges. This was the moment where
>identity didn't fit any longer. I wrote my own procedure to get the next
>free number out of a range. But then I also had to implement a routine which
>locked a number whenever an insert was going to start. What if the insert
>fails? I had the problem that the number had to be released when the planned
>insert didn't take place. It was a mess.
>
>Anyway, thanks again and have fun!
>Uwe
>
>"Uwe Sauerbrey" <sauerbrey@solution-execute.de> schrieb im Newsbeitrag
>news:3fa1284b@forums-1-dub...
>> uups.. I didn't see that.
>> Of course that won't work.
>>
>> Well, concerning the integer keys, would you say that an integer-pk will
>be
>> more efficient in any case? I defined the cols as varchar(50), but I think
>> the average length will be around 10 characters. I know that one
>> professional says, that the pk shouldn't have anything to do with the
>data.
>> So integer-PKs should be used. The other says that some kind of
>> autoincrement-columns are overhead and it would be more efficient to use
>> data-cols for the primary key.
>>
>> I'm a bit confused of this different flavours, so some time ago I decided
>> always to use data-columns for pk. Do you have some more pros and cons?
>>
>> Thanks
>> Uwe
>>
>>
>> "Bruce Hay" <hay at sybase dot com> schrieb im Newsbeitrag
>> news:3fa1241e$1@forums-1-dub...
>> > The primary table has a 3-column primary key. To create a foreign key
>> > referencing this table, the foreign key must also use three columns.
>> >
>> > Given the sizes of the columns in the current primary keys, you might
>> > consider using surrogate integer keys for efficiency.
>> >
>> > Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
>> > Developer Community at http://www.ianywhere.com/developer
>> >
>> > "Uwe Sauerbrey" <sauerbrey@solution-execute.de> wrote in message
>> > news:3fa11466@forums-1-dub...
>> > Hi,
>> >
>> > I'm using ASA 8.0.2.3601 on Win XP professional.
>> >
>> > I experienced a strange error when I ran some DDL-Statements. They were
>> > generated from PowerDesigner 8.
>> > The statements:
>> >
>> > /*==============================================================*/
>> > /* Table: T_REPFKSPALTEN */
>> > /*==============================================================*/
>> > create table T_REPFKSPALTEN
>> > (
>> > REPOSITORY varchar(50) not null,
>> > FREMDSCHLUESSEL varchar(50) not null,
>> > SPALTE varchar(50) not null,
>> > AKTIV bit default 1,
>> > ERFASSER varchar(50) default current
>> > user,
>> > ERFDATUM timestamp default current
>> > timestamp,
>> > GEAENDERTVON varchar(50) default last
>user,
>> > GEAENDERTAM timestamp default
>timestamp,
>> > primary key (FREMDSCHLUESSEL, SPALTE, REPOSITORY)
>> > );
>> >
>> > /*==============================================================*/
>> > /* Table: T_REPSPALTEN */
>> > /*==============================================================*/
>> > create table T_REPSPALTEN
>> > (
>> > REPOSITORY varchar(50) not null,
>> > TABELLE varchar(50) not null,
>> > SPALTE varchar(50) not null,
>> > AKTIV bit default 1,
>> > ERFASSER varchar(50) default current
>> > user,
>> > ERFDATUM timestamp default current
>> > timestamp,
>> > GEAENDERTVON varchar(50) default last
>user,
>> > GEAENDERTAM timestamp default
>timestamp,
>> > DATENTYP varchar(50),
>> > PK bit,
>> > NULLABLE bit,
>> > STANDARDWERT varchar(30000),
>> > INTEGRITAET varchar(30000),
>> > primary key (SPALTE, REPOSITORY, TABELLE)
>> > );
>> >
>> > alter table T_REPFKSPALTEN
>> > add foreign key T_FKSPALTEN_REF_T_SPALTEN (SPALTE, REPOSITORY)
>> > references T_REPSPALTEN (SPALTE, REPOSITORY)
>> > on update cascade
>> > on delete cascade;
>> >
>> > At the stage of the creation of the foreign key, I get the error "-113
>> > column "Spalte" in the foreign key has a different definition than in
>the
>> > primary key".
>> >
>> > But that isn't true.
>> >
>> > Can someone point me in the right direction?
>> >
>> > TIA
>> > Uwe
>> >
>> >
>> >
>>
>>
>

--
bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com


Glenn Paulley Posted on 2003-10-30 15:02:44.0Z
From: Glenn Paulley <paulley@ianywhere.com>
Reply-To: paulley@ianywhere.com
Organization: iAnywhere Solutions
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.2.1) Gecko/20021130
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: strange DDL-failure
References: <3fa11466@forums-1-dub> <3fa1241e$1@forums-1-dub> <3fa1284b@forums-1-dub>
In-Reply-To: <3fa1284b@forums-1-dub>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: paulley-xp.sybase.com
X-Original-NNTP-Posting-Host: paulley-xp.sybase.com
Message-ID: <3fa13624@forums-1-dub>
Date: 30 Oct 2003 08:02:44 -0700
X-Trace: forums-1-dub 1067529764 172.31.142.134 (30 Oct 2003 08:02:44 -0700)
X-Original-Trace: 30 Oct 2003 08:02:44 -0700, paulley-xp.sybase.com
Lines: 146
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1971
Article PK: 31456

Let me add my 2 cents:

- surrogate keys are usually more efficient to store because their
length is normally shorter, sometimes considerably so, than the data
values themselves. One has to tradeoff the additional space in each row
for such a key, versus the increased space required for indexing the
actual data values (both PK and FK indexes).

- in my experience it is very difficult to "hide" surrogate keys from
users or even customers. One should pick the formats of such keys
carefully. It is usually a good idea to have different surrogate key
formats for different business entities, so that one can tell at a
glance that a key refers to an "order", say, rather than a "customer".

This would imply that one shouldn't blindly implement auto-increment
integer surrogate keys for *everything* - pick which business entities
suit integer keys the most, and need autoincrement for efficiency. Use
different key formats and key generation methods for other business objects.

- surrogate keys can be made hard to type (eg. Canadian postal codes),
or self-checking (Canadian social insurance numbers), to prevent data
entry errors.

Glenn

Uwe Sauerbrey wrote:
> uups.. I didn't see that.
> Of course that won't work.
>
> Well, concerning the integer keys, would you say that an integer-pk will be
> more efficient in any case? I defined the cols as varchar(50), but I think
> the average length will be around 10 characters. I know that one
> professional says, that the pk shouldn't have anything to do with the data.
> So integer-PKs should be used. The other says that some kind of
> autoincrement-columns are overhead and it would be more efficient to use
> data-cols for the primary key.
>
> I'm a bit confused of this different flavours, so some time ago I decided
> always to use data-columns for pk. Do you have some more pros and cons?
>
> Thanks
> Uwe
>
>
> "Bruce Hay" <hay at sybase dot com> schrieb im Newsbeitrag
> news:3fa1241e$1@forums-1-dub...
>
>>The primary table has a 3-column primary key. To create a foreign key
>>referencing this table, the foreign key must also use three columns.
>>
>>Given the sizes of the columns in the current primary keys, you might
>>consider using surrogate integer keys for efficiency.
>>
>>Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
>>Developer Community at http://www.ianywhere.com/developer
>>
>>"Uwe Sauerbrey" <sauerbrey@solution-execute.de> wrote in message
>>news:3fa11466@forums-1-dub...
>>Hi,
>>
>>I'm using ASA 8.0.2.3601 on Win XP professional.
>>
>>I experienced a strange error when I ran some DDL-Statements. They were
>>generated from PowerDesigner 8.
>>The statements:
>>
>>/*==============================================================*/
>>/* Table: T_REPFKSPALTEN */
>>/*==============================================================*/
>>create table T_REPFKSPALTEN
>>(
>> REPOSITORY varchar(50) not null,
>> FREMDSCHLUESSEL varchar(50) not null,
>> SPALTE varchar(50) not null,
>> AKTIV bit default 1,
>> ERFASSER varchar(50) default current
>>user,
>> ERFDATUM timestamp default current
>>timestamp,
>> GEAENDERTVON varchar(50) default last user,
>> GEAENDERTAM timestamp default timestamp,
>> primary key (FREMDSCHLUESSEL, SPALTE, REPOSITORY)
>>);
>>
>>/*==============================================================*/
>>/* Table: T_REPSPALTEN */
>>/*==============================================================*/
>>create table T_REPSPALTEN
>>(
>> REPOSITORY varchar(50) not null,
>> TABELLE varchar(50) not null,
>> SPALTE varchar(50) not null,
>> AKTIV bit default 1,
>> ERFASSER varchar(50) default current
>>user,
>> ERFDATUM timestamp default current
>>timestamp,
>> GEAENDERTVON varchar(50) default last user,
>> GEAENDERTAM timestamp default timestamp,
>> DATENTYP varchar(50),
>> PK bit,
>> NULLABLE bit,
>> STANDARDWERT varchar(30000),
>> INTEGRITAET varchar(30000),
>> primary key (SPALTE, REPOSITORY, TABELLE)
>>);
>>
>>alter table T_REPFKSPALTEN
>> add foreign key T_FKSPALTEN_REF_T_SPALTEN (SPALTE, REPOSITORY)
>> references T_REPSPALTEN (SPALTE, REPOSITORY)
>> on update cascade
>> on delete cascade;
>>
>>At the stage of the creation of the foreign key, I get the error "-113
>>column "Spalte" in the foreign key has a different definition than in the
>>primary key".
>>
>>But that isn't true.
>>
>>Can someone point me in the right direction?
>>
>>TIA
>>Uwe
>>
>>
>>
>
>
>

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer