Page 1 of 1

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

Posted: Tue 03 Sep 2013 16:12
by LiPo
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

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

Posted: Wed 04 Sep 2013 10:14
by alexa
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.

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

Posted: Fri 11 Jan 2019 21:00
by saied_cp
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

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

Posted: Mon 14 Jan 2019 10:33
by alexa
We are planning to fix this issue in one of the next product versions and will notify you once it's available for downloading.

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

Posted: Tue 19 Mar 2019 18:24
by pcripps
Has this issue been fixed in the latest version?

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

Posted: Wed 20 Mar 2019 09:24
by alexa
It's not fixed yet.

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

Posted: Wed 22 May 2019 12:27
by pcripps
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.

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

Posted: Wed 22 May 2019 15:07
by alexa
Unfortunately, there is no estimated date for this.

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

Posted: Thu 23 May 2019 10:57
by pcripps
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.

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

Posted: Wed 27 Jan 2021 16:10
by James3
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.

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

Posted: Fri 05 Feb 2021 08:50
by alexa
We already started working on this feature.

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

Posted: Thu 20 Jan 2022 20:15
by Maxwell175
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.

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

Posted: Wed 26 Jan 2022 10:58
by alexa
Unfortunately, we still have no estimated date for this.

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

Posted: Thu 30 Jun 2022 23:32
by vladcoretchi3sd
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.

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

Posted: Fri 01 Jul 2022 09:23
by dzhanhira
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.