Link to home
Start Free TrialLog in
Avatar of BlakeMcKenna
BlakeMcKennaFlag for United States of America

asked on

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!
Avatar of Randy Poole
Randy Poole
Flag of United States of America image

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
Avatar of Jim Horn
>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.
Avatar of BlakeMcKenna

ASKER

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
Jim,

I tried using your code above and it gave an error (see screenshot). The "creep_ID" columns exists in both tables.
Screenshot.jpg
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

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
Hi,

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

which is missing in your screen shot
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.
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.
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
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
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
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.
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
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.
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

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
The "intervalPoint" column is not in the TEST_HEADER_Creep Table...
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?
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ok, well that makes sense then. I can just set that up in my Stored Procedure!

Thanks for your help!
Thanks for your help Scott!
No prob.  A trigger might be more consistent than just a stored proc.  Let me know if you need help with coding a trigger.
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!
I would be passing the "creep_ID" as a parameter...
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
Thanks Scott...that worked great!
You're welcome, glad it all worked out!