مشکل با کلید خارجی

**hmm** · سه شنبه 04 اسفند 1383, 16:31 عصر

با سلام
یه جدول با کلید اصلی با نزدیک 50 جدول دیگه رابطه یک به چند داره
یعنی 50 تا کلید خارجی تعریف شده
حالا جدول اصلی و جداول فرعی ما مقدار دهی شده اند ولی متاسفانه مقادیر کلید اصلی اشتباه هستند حالا میخواهم همه constraint ها رو غیر فعال کنم (یکباره) و جدول اصلی رو پاک کنم بدون اینکه جداول فرعی ما بهم بریزه یا حداقل اینکه مقادیر کلید خارجی همگی null بشن و بعد مقدارشان را دستی پر کنم
من نمیتونم (وقت ندارم) که 50 تا constraint رو پاک و دوباره ایجاد کنم
حالا راه حل چیست؟

**titbasoft** · سه شنبه 04 اسفند 1383, 18:29 عصر

شاید این کد کمکتون کنه. البته به جای 'myParentTable' باید نام جدول parent رو بنویسید. :wink:

البته فکر نمی کنم برای این قصدی که شما دارید پاک کردن constraint ها تنها راه حل و یا شاید بهترین راه حل باشه . در هر صورت این کد تمام ارجاعاتی که مثلا به جدول myParentTable شده رو حذف می کنه



DECLARE @ParentTable varchar&#40;20&#41;

set @ParentTable = 'myParentTable' 



DECLARE @fkname varchar&#40;60&#41; , @ct varchar&#40;60&#41;



DECLARE fk_cursor CURSOR FOR

select fk.name fkName,ct.name childTable from sysforeignkeys sf

join sysobjects fk on fk.id=sf.constid

join sysobjects pt on pt.id=sf.rkeyid

join sysobjects ct on ct.id=sf.fkeyid

where pt.name=@ParentTable



OPEN fk_cursor



FETCH NEXT FROM fk_cursor INTO @fkname,@ct



WHILE @@FETCH_STATUS = 0

BEGIN

   exec &#40;'alter table ' + @ct + ' drop constraint ' + @fkname&#41;

   FETCH NEXT FROM fk_cursor INTO @fkname,@ct

END



CLOSE fk_cursor

DEALLOCATE fk_cursor

GO

فکر نمی کنم کد پیچیده ای باشه ولی در هر صورت اگه با جائیش مشکل داشتید من در خدمتم. :oops:

اما برای ایجاد مجدد fk ها آیا fk دوباره به همان صورت قبلی ایجاد می شوند؟

**hmm** · چهارشنبه 05 اسفند 1383, 07:33 صبح

ممنون

اما برای ایجاد مجدد fk ها آیا fk دوباره به همان صورت قبلی ایجاد می شوند؟

دقیقا به همون شکل

**AminSobati** · چهارشنبه 05 اسفند 1383, 08:03 صبح

دوست عزیزم،
شما میتونین موقتا FK Constraintها رو غیر فعال و بعد مجددا فعال کنین، ولی اگر حذف کنین، ساختن دوباره اونها دردسره!

ALTER TABLE ChildTable

NOCHECK CONSTRAINT FK__Const

لذا اسکریپتی که titbasoft عزیز زحمت کشیدن نوشتن رو با دستور بالا تلفیق کنین برای غیر فعال کردن.
بعد از انجام ویرایشات لازم در جداول، با این ساختار مجددا FK Constraintها رو فعال کنین:

ALTER TABLE ChildTable

CHECK CONSTRAINT FK__Const

**titbasoft** · چهارشنبه 05 اسفند 1383, 08:35 صبح

ته دلم می دونستم راه بهتری هم هست
اما یک چیز رو در نظر داشته باشید که وقتی که دارید relation هاتون رو غیر فعال می کنید نام جداول child رو یه جایی مثلا توی یک temp table ذخیره کنید تا بدونید دوباره روی کدام جدول ها باید relation ایجاد کنید :wink:
(امیدوارم این مساله رو دیر نگفته باشم)

**hmm** · چهارشنبه 05 اسفند 1383, 14:00 عصر

من میخوام کلید خارجی در تمام جداول دیگه مقدار null بگیره
نمیتونم دستی 50 جدول رو update کنم

**AminSobati** · چهارشنبه 05 اسفند 1383, 16:39 عصر

وقتی که دارید relation هاتون رو غیر فعال می کنید نام جداول child رو یه جایی مثلا توی یک temp table ذخیره کنید تا بدونید دوباره روی کدام جدول ها باید relation ایجاد کنید

وقتی Relationها سر جایشون هستند و فقط غیر فعال میشن، چه نیازی به ذخیره نام اونها وجود داره؟ بعدا میشه دوباره فعالشون کرد

من میخوام کلید خارجی در تمام جداول دیگه مقدار null بگیره
نمیتونم دستی 50 جدول رو update کنم

با استفاده از این دستور:

EXEC sp_fkeys @pktable_name = N'MyTable'

میتونین بدست بیارین که جدول اصلی، با چه جداولی و دقیقا با چه فیلدی در ارتباطه. بعد دستور Update رو به صورت Dynamic تولید و اجرا کنین. البته باید خروجی این SP رو در یک جدول بریزید تا بتونین Cursor رو باهاش ایجاد کنین.

**hmm** · چهارشنبه 05 اسفند 1383, 17:15 عصر

من با استفاده از query



select 'alter table ACC.DL NOCHECK CONSTRAINT ' + fk.name fkName into tmp  from sysforeignkeys sf 

join sysobjects fk on fk.id=sf.constid 

join sysobjects pt on pt.id=sf.rkeyid 

join sysobjects ct on ct.id=sf.fkeyid 

where pt.name='dl'

و جدول tmp رو به یه فایل متنی export کردم و اونرو بعنوان یه فایل batch میخوام اجرا کنم ولی در همه خطها
پیغام



Constraint 'FK_AccCloseRelation_DLFive' does not belong to table 'DL'.

رو میده
چرا؟

آقای ثباتی میشه بیشتر در مورد جوابتون توضیح بدین( update منظورمه)

**AminSobati** · چهارشنبه 05 اسفند 1383, 21:49 عصر

دلیل خطا اینه که همیشه دارین جدول اصلی رو ALTER میکنین در حالیکه FK Constraint به جدول Child تعلق داره. به این صورت عمل کنین:

select 'alter table ' + ct.name + ' NOCHECK CONSTRAINT ' + fk.name fkName   from sysforeignkeys sf 

join sysobjects fk on fk.id=sf.constid 

join sysobjects pt on pt.id=sf.rkeyid 

join sysobjects ct on ct.id=sf.fkeyid 

where pt.name='dl'

در مورد Update، میبایست همونطور که برای ALTER یک Command رو تولید کردین و بعضی قسمتهای اون رو با کمک دستور Select ساختین، قسمتهای مختلف دستور Update رو هم بسازین. مثلا برای Null کردن به یک دستور با این ساختار کلی نیاز دارین:

UPDATE ChildTable SET RefColumn=NULL

در اینجا قسمتهای متغیر، یکی ChildTable هستش و یکی RefColumn
پس به کمک اون SP که در پست قبلیم اشاره شد، میتونین اسم جداول و فیلدهایی که باید Null بشن رو بدست بیارین و براشون دستور Update تولید کنین

**titbasoft** · پنج شنبه 06 اسفند 1383, 15:32 عصر

وقتی Relationها سر جایشون هستند و فقط غیر فعال میشن، چه نیازی به ذخیره نام اونها وجود داره؟ بعدا میشه دوباره فعالشون کرد

کاملا یک اشتباه لپی به علت در نظر گرفتن حذف relation ها :cry:

**AminSobati** · جمعه 07 اسفند 1383, 20:17 عصر

:wink: :)

**hmm** · شنبه 08 اسفند 1383, 15:37 عصر

آقای ثباتی ممنون از راهنمایی
سوال من اینه مگه یه constraint که تعریف میشه جزء اون table نیست یا اینکه وقتی کلید خارجی تعریف میکنیم چرا جدول مقصد باید alter بشه (تا حالا فکر میکردم constraint های یه جدول مربوط به اون جدوله ولی ظاهر برای کلید های خارجی این طور نیست)

در ضمن من به owner ها هم احتیاج دارم مثلا acc.t1 و...

**titbasoft** · شنبه 08 اسفند 1383, 17:34 عصر

وقتی کلید خارجی تعریف میکنیم چرا جدول مقصد باید alter بشه

با اجازه بزرگترها باید عرض کنم که ببینید دوست عزیز foreign key یا کلید خارجی هیچ چیزی جز یک محدودیت برای اعمال referencial entegrity بیشتر نیست به عبارت دیگر foreign key یک محدودیت است که روی یک فیلد (یا چند فیلد) اعمال می شود تا از ورود بعضی از دیتا هایی که مورد نظر ما نیستتند جلوگیری شود (ببخشید خیلی لری حرف میزنم ها) پس می توان این نتیجه را گرفت که foreign key روی جدول child یا جدولی که دیتاهایش را از جای دیگری می آورد اعمال می شود ، چون ما احتیاج به گذاشتن هیچ محدودیتی بر روی دیتاهایی که در جدول parent وارد می شوند نخواهیم داشت. (اندازه سواد خودم نوشتم) :wink:

در ضمن من به owner ها هم احتیاج دارم مثلا acc.t1 و...

به خدا sp_fkeys هم pktable_owner داره هم fktable_owner
:cry:

**hmm** · دوشنبه 10 اسفند 1383, 12:16 عصر

من با همون query میخوام مستقیما owner رو هم پیدا کنم اگه میشه راهنمایی کنید

**hmm** · دوشنبه 17 اسفند 1383, 16:15 عصر

**titbasoft** · سه شنبه 18 اسفند 1383, 00:18 صبح

select 'alter table ' + convert&#40;sysname,USER_NAME&#40;ct.uid&#41;&#41; + '.' + ct.name + ' NOCHECK CONSTRAINT ' + fk.name fkName   from sysforeignkeys sf 

join sysobjects fk on fk.id=sf.constid 

join sysobjects pt on pt.id=sf.rkeyid 

join sysobjects ct on ct.id=sf.fkeyid 

where pt.name='dl'

حله؟
:wink:

نام تاپیک: مشکل با کلید خارجی

ابزار های تاپیک

نمایش

مشکل با کلید خارجی

قوانین ایجاد تاپیک در تالار