Primary Keys not scripted

Brian DonahueBrian Donahue Posts: 6,590 New member
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" <[email protected]> wrote in message
news:[email protected]
> 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

  • 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 [email protected]? Do
    not post them in this forum.

    Thanks,

    Neil
    "Roumen Stoyanov" <[email protected]> wrote in message
    news:[email protected]
    > 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)" <[email protected]> wrote in message
    > news:[email protected]
    > > 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" <[email protected]> wrote in
    message
    > > news:[email protected]
    > > > 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
  • 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" <[email protected]> wrote in message
    news:[email protected]
    > 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" <[email protected]> wrote in message
    > news:[email protected]
    > > 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
    [email protected]?
    > Do
    > > not post them in this forum.
    > >
    > > Thanks,
    > >
    > > Neil
    > > "Roumen Stoyanov" <[email protected]> wrote in
    message
    > > news:[email protected]
    > > > 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)" <[email protected]> wrote in
    message
    > > > news:[email protected]
    > > > > 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" <[email protected]> wrote in
    > > message
    > > > > news:[email protected]
    > > > > > 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.