Drop constraint
-
hi guys, i'm trying to modify a clustered index defined on a table by adding more columns.
however i get this error whenever i try to drop its' contraints.
The constraint 'PK_Patient_Info' is being referenced by table 'Scheduled_Appointment', foreign key constraint 'FK_Scheduled_Appointment_Patient_Info'.
using...
alter
table Patient_Info
drop
constraint PK_Patient_Info
create
unique clustered index PK_Patient_Info on Patient_Info(KPAIDS_NO,Docket_num)
any help in solving this problem is greatly appreciated
thx much!
-
A foreign key constraint cannot be applied if the Parent table does NOT have a unique constraint of any kind. By removing the PK, you violate that rule.
Try this :
create unique clustered index PK_Patient_Info on Patient_Info(KPAIDS_NO,Docket_num) WITH DROP EXISTING
-
i already did try but got the following error, i forget to mention that the index was created on a primary key.
Cannot recreate index 'PK_Patient_Info'. The new index definition does not match the constraint
-
I didn't know that. You'll have to drop/recreate all the objects that the server complains about.
-
Or disable the FK but then after you do you will probably need to run dbcc checkconstraint on the FK to mark it as trusted
* Noel -
An alternative to dbcc checkconstraint in this case is if you aks SQL Server to check the trustedness of the foreign key when you reenable it. So instead of:
ALTER TABLE tableReferencing CHECK CONSTRAINT FK_References
type:
ALTER TABLE tableReferencing WITH CHECK CHECK CONSTRAINT FK_References
Note the WITH CHECK. This will set the foreign key state back to trusted, if the data that is in your tables satisfies the referential itegrity.
(shameless plug: some more examples on the states of foreign keys are on Foreign Keys and their states )
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply