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
Devart Team
Posts: 2657
Joined: Fri 24 Jun 2011 14:17

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
Devart Team
Posts: 2657
Joined: Fri 24 Jun 2011 14:17

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?

alexa
Devart Team
Posts: 2657
Joined: Fri 24 Jun 2011 14:17

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

Post by alexa » Wed 20 Mar 2019 09:24

It's not fixed yet.

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
Devart Team
Posts: 2657
Joined: Fri 24 Jun 2011 14:17

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.

Post Reply