Error when I do table structure changes and press Update Database button

Discussion of open issues, suggestions and bugs regarding database management and development tools for SQL Server
Post Reply
LiPo
Posts: 2
Joined: Thu 18 Jul 2013 15:38

Error when I do table structure changes and press Update Database button

Post by LiPo » Tue 03 Sep 2013 16:12

Hi,
I made changes in the table (add table field).
When I press the button Update Database to save changes to db, the following error message appears.

Incorect syntax near ')'.
Could not drop object 'dbo.tmp_devart_myTable' because it is referenced by a FOREIGN KEY constraint.

Thank you for help

alexa

Re: Error when I do table structure changes and press Update Database button

Post by alexa » Wed 04 Sep 2013 10:14

Could you please provide us a screenshot of the error message and let us know whether you performed any other actions in addition to adding a column?

Please also provide us the CREATE definition of the table.

You can send the reply straight to our support system at supportATdevartDOTcom, so we will keep further correspondence with you on this issue via that system.

saied_cp
Posts: 1
Joined: Fri 11 Jan 2019 20:35

Re: Error when I do table structure changes and press Update Database button

Post by saied_cp » Fri 11 Jan 2019 21:00

Unfortunately, when I change tables, I encounter this error. Please guide me.

"Could not drop object 'dbo.tmp_devart_tblPayrollPersonel' because it is referenced by a FOREIGN KEY
constraint."

image is : https://ufile.io/2qkyg

In the sql server managemen environment, this problem is resolved by checked the "Prevent saving changes that require the table to be re-created" option.

dbforg version is : 5.6.104


my script is:

CREATE TABLE Hesabdari.dbo.tblPayrollPersonel (
PersonelID INT NOT NULL
,PersonelCode INT NOT NULL
,Shaxs BIT NOT NULL
,Tabeiyat BIT NOT NULL
,MelliCode NVARCHAR(10) NOT NULL
,Name NVARCHAR(25) NOT NULL
,LName NVARCHAR(50) NOT NULL
,KeshvarID INT NOT NULL
,ShenasnameNo NVARCHAR(10) NULL
,shenasnameserial NVARCHAR(10) NULL
,Sex BIT NOT NULL
,TavalodDate NVARCHAR(10) NOT NULL
,EzdevajDate NVARCHAR(10) NULL
,SodurDate NVARCHAR(10) NOT NULL
,MahaleTavalod NVARCHAR(30) NOT NULL
,MahalSodur NVARCHAR(30) NOT NULL
,Mobile NVARCHAR(14) NULL
,Tell NVARCHAR(13) NULL
,Email NVARCHAR(50) NULL
,Address NVARCHAR(500) NULL
,Pic NVARCHAR(MAX) NULL
,Postallcode NVARCHAR(10) NULL
,Tahol BIT NULL
,FarzandID INT NULL
,TahsilatID INT NULL
,MadarekpicID INT NULL
,BimeNum NVARCHAR(50) NULL
,SavabeghID INT NULL
,NezamVazife BIT NULL
,MohitID INT NULL
,MoafiyatTypeID INT NULL
,EstexdamDate VARCHAR(10) NULL
,HesabBankiID INT NULL
,CONSTRAINT PK_tblPayrollPersonel PRIMARY KEY CLUSTERED (PersonelID)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE Hesabdari.dbo.tblPayrollPersonel
ADD CONSTRAINT FK_tblPayrollPersonel_tblMohit_MohitID FOREIGN KEY (MohitID) REFERENCES dbo.tblMohit (MohitID)
GO

ALTER TABLE Hesabdari.dbo.tblPayrollPersonel
ADD CONSTRAINT FK_tblPayrollPersonel_tblPayrollFarzand FOREIGN KEY (FarzandID) REFERENCES dbo.tblPayrollFarzand (FarzandID)
GO

ALTER TABLE Hesabdari.dbo.tblPayrollPersonel
ADD CONSTRAINT FK_tblPayrollPersonel_tblPayrollHesabbanki FOREIGN KEY (HesabBankiID) REFERENCES dbo.tblPayrollHesabbanki (HesabBankiID)
GO

ALTER TABLE Hesabdari.dbo.tblPayrollPersonel
ADD CONSTRAINT FK_tblPayrollPersonel_tblPayrollKeshvar FOREIGN KEY (KeshvarID) REFERENCES dbo.tblPayrollKeshvar (KeshvarID)
GO

ALTER TABLE Hesabdari.dbo.tblPayrollPersonel
ADD CONSTRAINT FK_tblPayrollPersonel_tblPayrollMadarek FOREIGN KEY (MadarekpicID) REFERENCES dbo.tblPayrollMadarek (MadarekpicID)
GO

ALTER TABLE Hesabdari.dbo.tblPayrollPersonel
ADD CONSTRAINT FK_tblPayrollPersonel_tblPayrollMoafiyatsarbazi FOREIGN KEY (MoafiyatTypeID) REFERENCES dbo.tblPayrollMoafiyatsarbazi (MoafiyatTypeID)
GO

ALTER TABLE Hesabdari.dbo.tblPayrollPersonel
ADD CONSTRAINT FK_tblPayrollPersonel_tblPayrollSavabegh1 FOREIGN KEY (SavabeghID) REFERENCES dbo.tblPayrollSavabegh (SavabeghID)
GO

ALTER TABLE Hesabdari.dbo.tblPayrollPersonel
ADD CONSTRAINT FK_tblPayrollPersonel_tblPayrollTahsilat_TahsilatID FOREIGN KEY (TahsilatID) REFERENCES dbo.tblPayrollTahsilat (TahsilatID)
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'شخص حقیقی 0 /حقوقی 1'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'Shaxs'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'تابعیت : ایرانی 0 / خارجی 1'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'Tabeiyat'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'کد ملی'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'MelliCode'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'نام'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'Name'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'نام حانوادگی'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'LName'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'کشور'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'KeshvarID'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'شماره شناسنامه'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'ShenasnameNo'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'سریال شناسنامه'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'shenasnameserial'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'جنسیت : مرد 0 /زن 1'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'Sex'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'تاریخ تولد'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'TavalodDate'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'تاریخ ازدواج'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'EzdevajDate'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'تاریخ صدور'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'SodurDate'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'محل تولد'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'MahaleTavalod'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'محل صدور'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'MahalSodur'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'تلفن همراه'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'Mobile'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'تلفن ثابت'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'Tell'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'ایمیل'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'Email'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'آدرس'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'Address'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'تصویر'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'Pic'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'کد پستی'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'Postallcode'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'وضعیت تاهل : مجرد 0 / متاهل 1'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'Tahol'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'کد کلید فرزندان'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'FarzandID'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'کد کلید مقطع تحصیلی'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'TahsilatID'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'کد کلید تصویر مدارک'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'MadarekpicID'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'کد کلید سوابق کاری'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'SavabeghID'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'وضعیت خدمت سربازی : ندارد 0 / دارد 1'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'NezamVazife'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N' کد کلید نوع معافیت خدمت سربازی'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'MoafiyatTypeID'
GO

EXEC sys.sp_addextendedproperty N'MS_Description'
,N'کد کلید حساب بانکی'
,'SCHEMA'
,N'dbo'
,'TABLE'
,N'tblPayrollPersonel'
,'COLUMN'
,N'HesabBankiID'
GO

alexa

Re: Error when I do table structure changes and press Update Database button

Post by alexa » Mon 14 Jan 2019 10:33

We are planning to fix this issue in one of the next product versions and will notify you once it's available for downloading.

pcripps
Posts: 4
Joined: Wed 09 Dec 2015 14:01

Re: Error when I do table structure changes and press Update Database button

Post by pcripps » Tue 19 Mar 2019 18:24

Has this issue been fixed in the latest version?


pcripps
Posts: 4
Joined: Wed 09 Dec 2015 14:01

Re: Error when I do table structure changes and press Update Database button

Post by pcripps » Wed 22 May 2019 12:27

Is there any date when this issue will be resolved?

We often have to do schema comparisons on tables with Foreign Keys where we need to keep the table structure.

alexa

Re: Error when I do table structure changes and press Update Database button

Post by alexa » Wed 22 May 2019 15:07

Unfortunately, there is no estimated date for this.

pcripps
Posts: 4
Joined: Wed 09 Dec 2015 14:01

Re: Error when I do table structure changes and press Update Database button

Post by pcripps » Thu 23 May 2019 10:57

This is really poor. It is a fairly fundamental requirement of a schema comparison tool to be able to maintain the column order during a comparison and the fact that it doesn't work if you have foreign keys is simply not acceptable. This should be at the top of your list of priorities for this application.

I should never have switched from RedGate. They might be more expensive but at least they deal with customer issues when they arise.

James3
Posts: 1
Joined: Wed 27 Jan 2021 16:01

Re: Error when I do table structure changes and press Update Database button

Post by James3 » Wed 27 Jan 2021 16:10

I've been on the Enterprise trial of dbForge Studio for a few days now and just encountered this error. As a prospective customer (who was getting ready to buy 2 copies of dbForge Studio Enterprise today), it's quite concerning that something as simple as an alter table doesn't work. Even more concerning is that this issue was brought up in May of 2019 (615 days ago!).

As @saied_cp mentioned, in SSMS one can simply uncheck "Prevent saving changes that require the table to be re-created".

Does Devart plan to fix this? If so, when? I'm hoping before my trial is up so I can confirm that it's working before spending thousands of dollars.

alexa

Re: Error when I do table structure changes and press Update Database button

Post by alexa » Fri 05 Feb 2021 08:50

We already started working on this feature.

Maxwell175
Posts: 3
Joined: Thu 20 Jan 2022 20:09

Re: Error when I do table structure changes and press Update Database button

Post by Maxwell175 » Thu 20 Jan 2022 20:15

alexa wrote: Fri 05 Feb 2021 08:50 We already started working on this feature.
I was also evaluating dbForge studio as the primary SQL Server development environment for my company and it is absolutely baffling that within quite literally the first day of testing I encountered this error which was first reported 10 (!) years ago. I can't believe such essential functionality that even SSMS has working is not functional in this commercial product.

I was trying to add a new column to the middle of an existing code table, which obviously required the table to be dropped and recreated, but since it is a code table it has foreign keys connected to it, as any database usually would, the foreign keys need to be dropped and recreated. SSMS is smart enough to just do this on the fly.

alexa

Re: Error when I do table structure changes and press Update Database button

Post by alexa » Wed 26 Jan 2022 10:58

Unfortunately, we still have no estimated date for this.

vladcoretchi3sd
Posts: 1
Joined: Thu 30 Jun 2022 23:04

Re: Error when I do table structure changes and press Update Database button

Post by vladcoretchi3sd » Thu 30 Jun 2022 23:32

We just started evaluating dbForge Studio and encountered the problem on the first day of testing.

Yes, there are many, many nice and useful features and we were ready to acquire multiple enterprise licenses but, more than the problem itself, the fact that the first report on this thread goes back to almost 10 years ago is unacceptable and a real deal breaker.

We are developers ourselves and understand that bugs do happen but a problem like this should have been caught and, after the report came in, should have been addressed immediately.

The support is very important for a commercial tool and this issue was not well managed.

Sorry but we will have to discourage the use of your software to our team.

dzhanhira
Devart Team
Posts: 239
Joined: Mon 26 Oct 2020 13:49

Re: Error when I do table structure changes and press Update Database button

Post by dzhanhira » Fri 01 Jul 2022 09:23

Kindly be infored that we are aware of this problem, but in order to start solving it, it is necessary to refactor the entire application architecture, we are doing this gradually from release to release, there are no exact implementation dates, but next year we will try to solve it.

Post Reply