Creating a Foreign-Key constraint on Parent/Child Tables

Ok,

I thought I understood this but apparently I don't. If I want to have a Parent/Child Table relationship and when a row is deleted from the Parent Table and rows are automatically deleted from the Child Table, how do I construct a Foreign Key so that it will facilitate this functionality? I'm using SQL Server 2008 R2.

Thanks!
BlakeMcKennaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Randy PooleCommented:
Check out the following article, pay attention to the On delete cascade and on update cascade.

http://technet.microsoft.com/en-us/library/ms186973(v=sql.105).aspx
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>how do I construct a Foreign Key so that it will facilitate this functionality?
Executing the below T-SQL will work, after renaming the obvious
ALTER TABLE ChildTable
ADD CONSTRAINT FK_ChildTable_ParentTable FOREIGN KEY (ColumnName) 

Open in new window

If there are any existing rows in ChildTable.ColumnName doesn't have a related row in ParentTable.ColumnName, you'll have to clean that up first before the above statement will succeed.
0
BlakeMcKennaAuthor Commented:
Ok,

I am wanting to create this FK within SSMS. There is one thing I'm not sure how to do and that is create an "Expression"...see attached screenshot.
Screenshot.jpg
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

BlakeMcKennaAuthor Commented:
Jim,

I tried using your code above and it gave an error (see screenshot). The "creep_ID" columns exists in both tables.
Screenshot.jpg
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Left out a line.  My bad.
ALTER TABLE ChildTable 
ADD CONSTRAINT fk_childtable_parenttable FOREIGN KEY(Creep_ID)
REFERENCES ParentTable (Creep_ID)

Open in new window

0
BlakeMcKennaAuthor Commented:
Well...I'm getting closer! See attached image.

The column "creep_ID" is the part of the Primary Key in the TEST_DETAILS_Creep table. The PK is comprised of two columns.

The "creep_ID" column is just an "identity" column in the TEST_HEADER_Creep table.
Screenshot.jpg
0
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

I think For foreign key PK relationship the other reference tables column should be Primary key

which is missing in your screen shot
0
BlakeMcKennaAuthor Commented:
I'm not sure what you mean. Do I need to list all the columns that comprise the PK for the Reference Table? If so, there are 5 columns that make up the PK.
0
BlakeMcKennaAuthor Commented:
The "creep_ID" in the TEST_HEADER_Creep Table is not part of the PK. Not sure if that makes a difference or not. Below is the PK Structure for both Tables.

TEST_DETAILS_Creep
  PK
      "creep_ID",
      "intervalPoint"

TEST_HEADER_Creep
  PK
      "main_ID",
      "seqNO",
      "channel_ID",
      "direction_ID",
      "angle_ID"
     
The "creep_ID" in the TEST_HEADER_Creep Table is just an Identity Column.
0
Vikas GargBusiness Intelligence DeveloperCommented:
HI,


creep_ID of table TEST_HEADER_Creep should be PK if you want to reference it as foreign key in  TEST_DETAILS_Creep Table

Please follow the link for PK , FK understanding

http://www.tutorialspoint.com/sql/sql-foreign-key.htm
0
BlakeMcKennaAuthor Commented:
Vikas,

I attached a screenshot of the link you provided. If you'll notice in this example, "customer_ID" in the Orders Table is not part of the PK of the Orders Table and yet it references the "ID" column (which is the PK) of the Customers Table. So that example is clearly using a non-PK column as a FK. At least that's what it looks like to me. Please correct me if I'm wrong.

Thanks!
Screenshot.jpg
0
Vikas GargBusiness Intelligence DeveloperCommented:
You Missunderstood this

customer_ID should not be part of PK but the reference of customer table which is ID is primary key

Which is the basic requirement
0
Scott PletcherSenior DBACommented:
There is no PK requirement.  The requirement is that a FK be supported by a single, unique index on the "parent" table.  That's it.

>> The column "creep_ID" is the part of the Primary Key in the TEST_DETAILS_Creep table. The PK is comprised of two columns. <<

You'll have to create a separate unique index with only creep_ID in it.  It can be nonclustered, and it does not have to be a PK, but it does have to contain only the column(s) directly involved in the FK relationship.
0
BlakeMcKennaAuthor Commented:
Scott,

This Table TEST_DETAILS_Creep has a many to 1 relationship with the TEST_HEADER_Creep Table. Therefore, when you say unique index...are you implying that the creep_ID can only exist one time in the TEST_DETAILS_Creep Table? If so, then creating a "Unique Index" would not work. I'm at a loss here. See the attached screenshot. It will show a picture of some sample data in the TEST_DETAILS_Creep Table
Screenshot.jpg
0
Scott PletcherSenior DBACommented:
For a FK relationship to be defined, the supporting index in the parent table must be a unique index (or constraint, which creates a unique index as well).

Therefore, either the FK relationship must include enough columns to make it unique, even if a column(s) must be NULL, or you have to re-think the FK relationship overall.
0
BlakeMcKennaAuthor Commented:
Ok,

This is what I was trying to do. Here's my code. Since I need a unique index, the "creep_ID" and "intervalPoint" are the two columns that make up the PK for the TEST_DETAILS_Creep Table

ALTER TABLE TEST_DETAILS_Creep
ADD CONSTRAINT FK_TEST_HEADER_Creep FOREIGN KEY (creep_ID, intervalPoint)
REFERENCES TEST_HEADER_Creep(creep_ID)

Open in new window

0
Scott PletcherSenior DBACommented:
ALTER TABLE TEST_DETAILS_Creep
ADD CONSTRAINT FK_TEST_HEADER_Creep FOREIGN KEY (creep_ID, intervalPoint)
REFERENCES TEST_HEADER_Creep(creep_ID, intervalPoint) ON DELETE CASCADE
0
BlakeMcKennaAuthor Commented:
The "intervalPoint" column is not in the TEST_HEADER_Creep Table...
0
BlakeMcKennaAuthor Commented:
What would I need to do in order to create this relationship so that when a row from the TEST_HEADER_Creep Table is deleted....it's associated rows in the TEST_DETAILS_Creep Table are deleted also?
0
Scott PletcherSenior DBACommented:
You'd need a trigger on the TEST_HEADER_Creep table for that, since this is a one-to-many relationship.  A FK is only one-to-one.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BlakeMcKennaAuthor Commented:
Ok, well that makes sense then. I can just set that up in my Stored Procedure!

Thanks for your help!
0
BlakeMcKennaAuthor Commented:
Thanks for your help Scott!
0
Scott PletcherSenior DBACommented:
No prob.  A trigger might be more consistent than just a stored proc.  Let me know if you need help with coding a trigger.
0
BlakeMcKennaAuthor Commented:
Actually I do need help. I've never written one before and I looked at the MSDN examples and they just confused me. I just need to have one fire when a Row is deleted in the TEST_HEADER_Creep Table that will delete associated rows in the TEST_DETAILS_Creep Table.

Thanks!
0
BlakeMcKennaAuthor Commented:
I would be passing the "creep_ID" as a parameter...
0
Scott PletcherSenior DBACommented:
CREATE TRIGGER TEST_HEADER_Creep__TRG_DEL
ON TEST_HEADER_Creep
AFTER DELETE
AS
SET NOCOUNT ON;
DELETE FROM TEST_DETAILS_Creep
WHERE creep_ID IN (
    SELECT DISTINCT creep_ID
    FROM deleted
    )
GO
0
BlakeMcKennaAuthor Commented:
Thanks Scott...that worked great!
0
Scott PletcherSenior DBACommented:
You're welcome, glad it all worked out!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.