Problem adding PRIMARY KEY to tables

[eferreyramontes]

I'm having some issues at the time of adding PRIMARY KEYs to tables that were already created. Generally, those ALTER statements look like this:

ALTER TABLE [dbo].[system_actions] ADD 
    CONSTRAINT [PK_system_action] PRIMARY KEY CLUSTERED 
    (
        [Code]
    )  ON [PRIMARY] 
GO

I tried something I found on the internet moving the second line to the first one like this:

ALTER TABLE [dbo].[system_actions] ADD CONSTRAINT [PK_system_action] PRIMARY KEY CLUSTERED 
    (
        [Code]
    )  ON [PRIMARY] 
GO

but it doesn't understand the parentheses (

The error message is:

Cannot understand       (
. at ./sqlserver2pgsql.pl line 2039, <$file> line 4913.
        main::parse_dump() called at ./sqlserver2pgsql.pl line 3187

[beaud76]

I guess the problem comes from the double tab character on the [Code] line. Before solving the issue, as a workaround, you could try to delete one of both tabs.

[eferreyramontes]

it seems to work removing all the tabs:

ALTER TABLE [dbo].[system_actions] ADD CONSTRAINT [PK_system_action] PRIMARY KEY CLUSTERED 
(
    [Code]
)  ON [PRIMARY] 
GO

[eferreyramontes]

It also seems to be failing in cases with multi PKs:

ALTER TABLE [dbo].[group_x_actions] ADD CONSTRAINT [PK_group_x_actions] PRIMARY KEY CLUSTERED 
(
    [groupCode],
    [frmCode]
)  ON [PRIMARY] 
GO

It doesn't recognise the comma. It worked after I changed it into:

ALTER TABLE [dbo].[group_x_actions] ADD CONSTRAINT [PK_group_x_actions] PRIMARY KEY CLUSTERED 
(
    [groupCode]
    [frmCode]
)  ON [PRIMARY] 
GO

[eferreyramontes]

I've found another issue regarding Foreign Keys:

ALTER TABLE [dbo].[Web_File_fileWord] ADD CONSTRAINT [FK_Web_File_fileWord_Web_File_files] FOREIGN KEY 
(
    [idFile]
) REFERENCES [dbo].[Web_File_files] (
    [id]
) ON DELETE CASCADE  ON UPDATE CASCADE , CONSTRAINT [FK_Web_File_fileWord_Web_File_words] FOREIGN KEY 
(
    [idWord]
) REFERENCES [dbo].[Web_File_words] (
    [id]
) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

Line <ALTER TABLE [dbo].[Web_File_wordTranslations] ADD CONSTRAINT [FK_Web_File_wordTranslations_Pais] FOREIGN KEY 
> (6227) not understood. This is a bug at ./sqlserver2pgsql.pl line 2462, <$file> line 6227.

I tried leaving it as simple as it can be:

ALTER TABLE [dbo].[Web_File_fileWord] ADD CONSTRAINT [FK_Web_File_fileWord_Web_File_files] FOREIGN KEY 
(
    [idFile]
) 
GO

but it also fails.

[madtibo]

Hello @eferreyramontes

Just to make sure: sqlserver2pgsql is built to parse a schema dumped by SQL Server (see "Usage" in the README.md).
It is not designed to understand an hand written schema for SQL Server.

Does your SQL schema come from an "official" dump?

If yes, could you give the table creation orders so we can test what is going wrong?

Cordialement,

[eferreyramontes]

Hey @madtibo, yes. It came from an official dump:

CREATE TABLE "public"."web_file_fileword"
(
    "idfile"      int         NOT NULL,
    "idword"      int         NOT NULL,
    "selected"    boolean     NOT NULL,
    "filtered"    boolean     NOT NULL,
    "repetitions" int         NOT NULL,
    "locations"   varchar(50) NOT NULL
);

[madtibo]

Hello @eferreyramontes

It seems to me the "web_file_fileword" creation order you gave me is in PostgreSQL format, not the SQLServer dump.

Usually, the SQLServer dumps have the primary key defined in the CREATE TABLE query. Which tool did you use to get the SQLServer dump?

Cordialement,

[madtibo]

I close this issue. Do not hesitate to reopen it if you still experience your problem.