1

I have two tables (MS SQL Server 2005) with an existing application (no DB alterations other than indexes, etc are allowed).

The two tables are:

ActivityDetails (Primary table)
    ActivityDetailkey (Primary key)

SubActivities (Child table)
    ActivityDetailKey (Refers to ActivityDetails)

Now, there are no contraints on SubActivities for the ActivityDetailKey. Basically, for EACH ActivityDetail row, there can be MANY SubActivities rows and there is nothing stopping the user from deleting an ActivityDetails row and leaving the SubActivities orphaned.

There was supposed to be some "soft locks" in the application that would prevent this but it isn't working and I wanted to put some better integrity in the DB layer too.

But I can't seem to add the foreign key because the SubActivities's ActivityDetailKey column isn't UNIQUE.

How can I prevent people from deleting the ActivityDetails rows if there are children present?

Thanks

EDIT

I apologize for the complexity. The SubActivities table is actually named TEDetailSubActivities. I changed it in the question so that it would be easier to read.

But anyway, here is a gist of the complete schema for both tables.

https://gist.github.com/840479

I appreciate any help.

cbmeeks
  • 11,248
  • 22
  • 85
  • 136
  • I've tried both methods and I get: "The columns in table SubActivities do not match an existing primary key or UNIQUE constraint." Thanks guys. – cbmeeks Feb 23 '11 at 13:44
  • What is the *exact* schema for ActivityDetails please? – gbn Feb 23 '11 at 13:49
  • I'm unable to reproduce your error message in SQL Server 2005 - if there's no PK in ActivityDetails, or it's a different PK, I get the message "There are no primary or candidate keys in the referenced table 'ActivityDetails' that match..." – Damien_The_Unbeliever Feb 23 '11 at 13:56
  • It would appear that your message is generated when attempting to create a relationship using the SSMS visual tools rather than a query, and you still appear to be trying to set up the relationship the wrong way around. – Damien_The_Unbeliever Feb 23 '11 at 13:59
  • @gbn, @Damien_The_Unbeliever, I have posted a gist of the schema here: https://gist.github.com/840479 – cbmeeks Feb 23 '11 at 14:27
  • @cbmeeks - I've updated my answer, and the first code sample now runs exactly against the schema you've posted. – Damien_The_Unbeliever Feb 23 '11 at 15:42
  • @Damien_The_Unbeliever: Thanks, but I'm getting this??? Msg 547, Level 16, State 0, Line 1 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_TEDetailSubActivities_ActivityDetails". The conflict occurred in database "Development_2010_09_10", table "dbo.ActivityDetails", column 'ActivityDetailKey'. – cbmeeks Feb 23 '11 at 16:24
  • 1
    @cbmeeks - you have entries in TEDetailsSubActivities which don't correspond to a row in ActivityDetails - you need to delete these orphaned rows or insert new "dummy" rows in the ActivityDetails table for them. – Damien_The_Unbeliever Feb 23 '11 at 17:14
  • @Damien_The_Unbeliever: AH! I bet you are right. This is actually why we found this error in the first place. Now I must figure out how to do that over a bajilion rows. lol – cbmeeks Feb 23 '11 at 18:35
  • Thanks everyone for their help. Damien nailed it. The FK didn't set because of orphaned records. I removed the orphaned records and it worked. Plus, I tried to orphan more records and the DB stopped me. Gotta love integrity. – cbmeeks Feb 23 '11 at 18:54

2 Answers2

2

It sounds like you're trying to set up your foreign key the wrong way around - if there are multiple rows in SubActivities with the same ActivityDetailKey value, and these are references to the primary key in ActivityDetails, then the following should work (based on your posted schema, and now tested):

ALTER TABLE TEDetailSubActivities ADD CONSTRAINT FK_TEDetailSubActivities_ActivityDetails FOREIGN KEY
      (ActivityDetailKey) references dbo.ActivityDetails (ActivityDetailKey)

previous version, based on table names in post:

ALTER TABLE SubActivities ADD CONSTRAINT FK_SubActivities_ActivityDetails FOREIGN KEY
      (ActivityDetailKey) references ActivityDetails (ActivityDetailKey)

There's no uniqueness requirement on the ActivityDetailKey column in SubActivities.

As-is, that'll stop deletion of rows from ActivityDetails if there are rows in SubActivities that reference them. If, on the other hand, you want the application to be able to continue with its deletes, but avoid leaving orphaned rows in SubActivities, add ON DELETE CASCADE after the final closing bracket above.

The above works based on the following table definitions. If it doesn't work in your database, you need to help us out by posting either the actual table definitions from your database, or something "close enough" for us to mimic what you're seeing:

create table ActivityDetails (
    ActivityDetailkey int not null Primary key
)
go
create table SubActivities (
    ActivityDetailKey int not null
)
go

Sigh. If you're going to insist on using the SSMS designers:

  • Right click on SubActivities, choose "Design".
  • Press the "Relationships" toolbar button
  • Press "Add"
  • Press "..." against the "Tables and Columns Specification" property
  • In the "Primary key table" drop down, choose "ActivityDetails"
  • In the grid below, choose ActivityDetailKey on both sides
  • Press "OK", "Close", the "Save" toolbar button, and (if necessary) "Yes" to the save warning
  • Close the Designer.
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
2

You can have a foreign key, even if its duplicated on the child table, you should add the constraint WITH CHECK option, for example:

ALTER TABLE [dbo].[SubActivities]  WITH CHECK ADD  CONSTRAINT [FK_SubActivities_ActivityDetails] FOREIGN KEY([ActivityDetailkey])
REFERENCES [dbo].[ActivityDetails ] ([ActivityDetailkey])

Hope it helps!

jorgebg
  • 6,560
  • 1
  • 22
  • 31