Proper foreign keys for MS SQL Server 2005 without UNIQUE constraints?

Question

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.

Answer 1

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.

Answer 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!