Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Creating a Foreign-Key constraint on Parent/Child Tables

Posted on 2014-09-02
28
Medium Priority
?
161 Views
Last Modified: 2014-09-03
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!
0
Comment
Question by:BlakeMcKenna
  • 15
  • 7
  • 3
  • +2
28 Comments
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40298991
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40299014
>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
 

Author Comment

by:BlakeMcKenna
ID: 40299150
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:BlakeMcKenna
ID: 40299173
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40299209
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
 

Author Comment

by:BlakeMcKenna
ID: 40299220
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
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40300786
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
 

Author Comment

by:BlakeMcKenna
ID: 40300949
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
 

Author Comment

by:BlakeMcKenna
ID: 40300967
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
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40300990
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
 

Author Comment

by:BlakeMcKenna
ID: 40301052
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
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40301058
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40301164
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
 

Author Comment

by:BlakeMcKenna
ID: 40301264
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40301414
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
 

Author Comment

by:BlakeMcKenna
ID: 40301439
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40301442
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
 

Author Comment

by:BlakeMcKenna
ID: 40301447
The "intervalPoint" column is not in the TEST_HEADER_Creep Table...
0
 

Author Comment

by:BlakeMcKenna
ID: 40301451
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 40301486
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
 

Author Comment

by:BlakeMcKenna
ID: 40301492
Ok, well that makes sense then. I can just set that up in my Stored Procedure!

Thanks for your help!
0
 

Author Closing Comment

by:BlakeMcKenna
ID: 40301495
Thanks for your help Scott!
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40301515
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
 

Author Comment

by:BlakeMcKenna
ID: 40301563
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
 

Author Comment

by:BlakeMcKenna
ID: 40301570
I would be passing the "creep_ID" as a parameter...
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40301577
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
 

Author Comment

by:BlakeMcKenna
ID: 40301615
Thanks Scott...that worked great!
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40301621
You're welcome, glad it all worked out!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Integration Management Part 2
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question