BlakeMcKenna
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!
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!
>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
Executing the below T-SQL will work, after renaming the obvious
ALTER TABLE ChildTable
ADD CONSTRAINT FK_ChildTable_ParentTable FOREIGN KEY (ColumnName)
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.
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
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
ASKER
Jim,
I tried using your code above and it gave an error (see screenshot). The "creep_ID" columns exists in both tables.
Screenshot.jpg
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)
ASKER
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
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 think For foreign key PK relationship the other reference tables column should be Primary key
which is missing in your screen shot
ASKER
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.
ASKER
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.
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
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
ASKER
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
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
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.
>> 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.
ASKER
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
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.
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.
ASKER
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
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)
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
ADD CONSTRAINT FK_TEST_HEADER_Creep FOREIGN KEY (creep_ID, intervalPoint)
REFERENCES TEST_HEADER_Creep(creep_ID
ASKER
The "intervalPoint" column is not in the TEST_HEADER_Creep Table...
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, well that makes sense then. I can just set that up in my Stored Procedure!
Thanks for your help!
Thanks for your help!
ASKER
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.
ASKER
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!
Thanks!
ASKER
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
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
ASKER
Thanks Scott...that worked great!
You're welcome, glad it all worked out!
http://technet.microsoft.c