What are the challenges you face when working across database platforms? Take the survey
Options

Primary Keys not scripted

Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
edited May 22, 2004 9:11AM in SQL Compare Previous Versions
Hi Roumen,

Maybe SQL Compare is simply still scripting the keys out of order. It
needs to drop the foreign key before it can modify a primary key that's on
the same column. Can you please check the SQl Compare migration script to
ensure that the keys are both being scripted?

Thanks!

Regards,

Brian Donahue
Red Gate Technical Support

"Roumen Stoyanov" <Roumen.Stoyanov@childrens.harvard.edu> wrote in message
news:4r3ajbePEHA.2140@server53...
> I am trying to synch tables, but SQL Compare (3.1.6.210) doesn't script
the
> primary keys of the tables. It does, however, script foreign keys causing
> the script to fail when other tables are scripted. The following table has
> the OID filed set as Primary Key in the database.
>
> CREATE TABLE [dbo].[ReportDef]
> (
> [OID] [varchar] (100) NOT NULL,
> [Name] [varchar] (250) NOT NULL,
> [SpName] [varchar] (50) NULL,
> [CustomUri] [varchar] (250) NULL,
> [SecurityRoleOID] [varchar] (100) NULL,
> [Description] [varchar] (500) NULL,
> [ReportType] [varchar] (10) NOT NULL CONSTRAINT [DF_Report_type_oid]
DEFAULT
> ('generic')
> )
> ALTER TABLE [dbo].[ReportDef] ADD
> CONSTRAINT [FK_ReportDef_SecurityRole] FOREIGN KEY ([SecurityRoleOID])
> REFERENCES [dbo].[SecurityRole] ([OID]) ON UPDATE CASCADE
>
> Thank you.
>
> Roumen Stoyanov
>
>

Comments

  • Options
    Roumen,

    Can you check that the option to include primary keys is checked.

    If it still doesn't work, is there any chance you can take snapshots of the
    two databases, zip them up and e-mail them to me at support@red-gate.com? Do
    not post them in this forum.

    Thanks,

    Neil
    "Roumen Stoyanov" <Roumen.Stoyanov@childrens.harvard.edu> wrote in message
    news:8myf5UpPEHA.3156@server53...
    > Brian,
    >
    > I've attached the script file SQL Compare created for my table and there
    is
    > no primary key in there being scripted. I did run it in SQL Analyzer and
    the
    > table it created has no PK. I believe I had the same issue with the
    previous
    > version of SQL Compare and was under the impression that it is a bug you
    are
    > fixing in the latest release.
    > Thank you.
    >
    > Roumen Stoyanov
    >
    > "Brian Donahue (Red Gate)" <brian.donahue@red-gate.com> wrote in message
    > news:7wE7e6oPEHA.1220@server53...
    > > Hi Roumen,
    > >
    > > Maybe SQL Compare is simply still scripting the keys out of order.
    It
    > > needs to drop the foreign key before it can modify a primary key that's
    on
    > > the same column. Can you please check the SQl Compare migration script
    to
    > > ensure that the keys are both being scripted?
    > >
    > > Thanks!
    > >
    > > Regards,
    > >
    > > Brian Donahue
    > > Red Gate Technical Support
    > >
    > > "Roumen Stoyanov" <Roumen.Stoyanov@childrens.harvard.edu> wrote in
    message
    > > news:4r3ajbePEHA.2140@server53...
    > > > I am trying to synch tables, but SQL Compare (3.1.6.210) doesn't
    script
    > > the
    > > > primary keys of the tables. It does, however, script foreign keys
    > causing
    > > > the script to fail when other tables are scripted. The following table
    > has
    > > > the OID filed set as Primary Key in the database.
    > > >
    > > > CREATE TABLE [dbo].[ReportDef]
    > > > (
    > > > [OID] [varchar] (100) NOT NULL,
    > > > [Name] [varchar] (250) NOT NULL,
    > > > [SpName] [varchar] (50) NULL,
    > > > [CustomUri] [varchar] (250) NULL,
    > > > [SecurityRoleOID] [varchar] (100) NULL,
    > > > [Description] [varchar] (500) NULL,
    > > > [ReportType] [varchar] (10) NOT NULL CONSTRAINT [DF_Report_type_oid]
    > > DEFAULT
    > > > ('generic')
    > > > )
    > > > ALTER TABLE [dbo].[ReportDef] ADD
    > > > CONSTRAINT [FK_ReportDef_SecurityRole] FOREIGN KEY ([SecurityRoleOID])
    > > > REFERENCES [dbo].[SecurityRole] ([OID]) ON UPDATE CASCADE
    > > >
    > > > Thank you.
    > > >
    > > > Roumen Stoyanov
    > > >
    > > >
    > >
    > >
    >
    >
    >
    - Neil Davidson
    Red Gate Software Ltd
  • Options
    Roumen,

    SQL Compare treats primary keys as a type of index so it is not scripting
    these because of the options you have set. Can you try unchecking the option
    'Ignore indexes'. I would actually recommend you restore the options to the
    defaults.

    Yours,

    Neil

    defaults
    "Roumen Stoyanov" <Roumen.Stoyanov@childrens.harvard.edu> wrote in message
    news:eFsRbZ0PEHA.2140@server53...
    > Neil,
    >
    > I don't have that option. Please see the attached file for options I have
    > and their settings. The script generated with those options is:
    >
    > CREATE TABLE [dbo].[ReportDef]
    > (
    > [OID] [varchar] (100) NOT NULL,
    > [Name] [varchar] (250) NOT NULL,
    > [SpName] [varchar] (50) NULL,
    > [CustomUri] [varchar] (250) NULL,
    > [SecurityRoleOID] [varchar] (100) NULL,
    > [Description] [varchar] (500) NULL,
    > [ReportType] [varchar] (10) NOT NULL CONSTRAINT [DF_Report_type_oid]
    DEFAULT
    > ('generic')
    > )
    >
    > GO
    >
    > -- Constraints and indexes
    > ALTER TABLE [dbo].[ReportDef] ADD CONSTRAINT [CK_Report_ReportType] CHECK
    > (([ReportType] = 'custom' or [ReportType] = 'generic'))
    > GO
    >
    > -- Foreign keys
    > ALTER TABLE [dbo].[ReportDef] ADD CONSTRAINT [FK_ReportDef_SecurityRole]
    > FOREIGN KEY ([SecurityRoleOID]) REFERENCES [dbo].[SecurityRole] ([OID]) ON
    > UPDATE CASCADE
    > GO
    >
    >
    > "Neil Davidson" <neil.davidson@red-gate.com> wrote in message
    > news:dX9iID0PEHA.2168@server53...
    > > Roumen,
    > >
    > > Can you check that the option to include primary keys is checked.
    > >
    > > If it still doesn't work, is there any chance you can take snapshots of
    > the
    > > two databases, zip them up and e-mail them to me at
    support@red-gate.com?
    > Do
    > > not post them in this forum.
    > >
    > > Thanks,
    > >
    > > Neil
    > > "Roumen Stoyanov" <Roumen.Stoyanov@childrens.harvard.edu> wrote in
    message
    > > news:8myf5UpPEHA.3156@server53...
    > > > Brian,
    > > >
    > > > I've attached the script file SQL Compare created for my table and
    there
    > > is
    > > > no primary key in there being scripted. I did run it in SQL Analyzer
    and
    > > the
    > > > table it created has no PK. I believe I had the same issue with the
    > > previous
    > > > version of SQL Compare and was under the impression that it is a bug
    you
    > > are
    > > > fixing in the latest release.
    > > > Thank you.
    > > >
    > > > Roumen Stoyanov
    > > >
    > > > "Brian Donahue (Red Gate)" <brian.donahue@red-gate.com> wrote in
    message
    > > > news:7wE7e6oPEHA.1220@server53...
    > > > > Hi Roumen,
    > > > >
    > > > > Maybe SQL Compare is simply still scripting the keys out of
    order.
    > > It
    > > > > needs to drop the foreign key before it can modify a primary key
    > that's
    > > on
    > > > > the same column. Can you please check the SQl Compare migration
    script
    > > to
    > > > > ensure that the keys are both being scripted?
    > > > >
    > > > > Thanks!
    > > > >
    > > > > Regards,
    > > > >
    > > > > Brian Donahue
    > > > > Red Gate Technical Support
    > > > >
    > > > > "Roumen Stoyanov" <Roumen.Stoyanov@childrens.harvard.edu> wrote in
    > > message
    > > > > news:4r3ajbePEHA.2140@server53...
    > > > > > I am trying to synch tables, but SQL Compare (3.1.6.210) doesn't
    > > script
    > > > > the
    > > > > > primary keys of the tables. It does, however, script foreign keys
    > > > causing
    > > > > > the script to fail when other tables are scripted. The following
    > table
    > > > has
    > > > > > the OID filed set as Primary Key in the database.
    > > > > >
    > > > > > CREATE TABLE [dbo].[ReportDef]
    > > > > > (
    > > > > > [OID] [varchar] (100) NOT NULL,
    > > > > > [Name] [varchar] (250) NOT NULL,
    > > > > > [SpName] [varchar] (50) NULL,
    > > > > > [CustomUri] [varchar] (250) NULL,
    > > > > > [SecurityRoleOID] [varchar] (100) NULL,
    > > > > > [Description] [varchar] (500) NULL,
    > > > > > [ReportType] [varchar] (10) NOT NULL CONSTRAINT
    [DF_Report_type_oid]
    > > > > DEFAULT
    > > > > > ('generic')
    > > > > > )
    > > > > > ALTER TABLE [dbo].[ReportDef] ADD
    > > > > > CONSTRAINT [FK_ReportDef_SecurityRole] FOREIGN KEY
    > ([SecurityRoleOID])
    > > > > > REFERENCES [dbo].[SecurityRole] ([OID]) ON UPDATE CASCADE
    > > > > >
    > > > > > Thank you.
    > > > > >
    > > > > > Roumen Stoyanov
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >
    > > >
    > >
    > >
    >
    >
    >
    - Neil Davidson
    Red Gate Software Ltd
This discussion has been closed.