Linking Two Foreign Keys to one Primary Key
up vote
0
down vote
favorite
I have two tables, a Photographer and an Influences table. In the Influences table, a Photographer is able to influence another Photographer.
Relational Schema:
Here is my Diagram in SQL:
Instance Example:
My issue is when I try to delete Photographer Tom, since he influences Jason (as shown in Influences table), I get a error stating:
The DELETE statement conflicted with the REFERENCE constraint "FK_Influences_Photographer1". The conflict occurred in database "jma59", table "dbo.Influences"
If Tom was in the column EPName of the Influences table, I have no issue deleting it. I know that this is a foreign key issue but I am not sure how to handle this situation. I created two separate foreign keys that reference to the Photographer primary key. But the problem is that I cannot make it so that both foreign keys cascade on update and delete.
First foreign key is EPName and EPBDate referencing to PName and PBDate of Photographer
Second foreign key is RPName and RPBDate referning to PName and PBDate of Photographer as well.
The error is:
Unable to create relationship 'FK_Influences_Photographer1'. Introducing FOREIGN KEY constraint 'FK_Influences_Photographer1' on table 'Influences' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Any advice is greatly appreciated!
sql
sql-server edited Nov 10 at 22:04
marc_s
565k12610911243
asked Nov 10 at 21:47
Johngianni
84
add a comment |
up vote
0
down vote
favorite
I have two tables, a Photographer and an Influences table. In the Influences table, a Photographer is able to influence another Photographer.
Relational Schema:
Here is my Diagram in SQL:
Instance Example:
My issue is when I try to delete Photographer Tom, since he influences Jason (as shown in Influences table), I get a error stating:
The DELETE statement conflicted with the REFERENCE constraint "FK_Influences_Photographer1". The conflict occurred in database "jma59", table "dbo.Influences"
If Tom was in the column EPName of the Influences table, I have no issue deleting it. I know that this is a foreign key issue but I am not sure how to handle this situation. I created two separate foreign keys that reference to the Photographer primary key. But the problem is that I cannot make it so that both foreign keys cascade on update and delete.
First foreign key is EPName and EPBDate referencing to PName and PBDate of Photographer
Second foreign key is RPName and RPBDate referning to PName and PBDate of Photographer as well.
The error is:
Unable to create relationship 'FK_Influences_Photographer1'. Introducing FOREIGN KEY constraint 'FK_Influences_Photographer1' on table 'Influences' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Any advice is greatly appreciated!
sql
sql-server edited Nov 10 at 22:04
marc_s
565k12610911243
asked Nov 10 at 21:47
Johngianni
84
If two photographers influence each other, I don't understand why you would want to be able to delete one of them. You need to delete the influence relationship first.
– Gordon Linoff
Nov 10 at 23:05
Say for instance I remove Tom from the Photographer table, it should remove the tuple shown above in the Influences table. Likewise if I delete Jason in the Photographer table it should remove that same tuple. I am not sure how to do this since it doesn't let me cascade from two different paths on the same attributes (PName,PBDate) of Photographer.
– Johngianni
Nov 11 at 0:01
forFK_Influences_Photographer1make sure ON DELETE/UPDATE is set to NO Action, then set Enforce Foreign Key Constraint toNOor set it by codeALTER TABLE Influences NOCHECK CONSTRAINT FK_Influences_Photographer1this should make you able to delete Tom while keeping both foreign keys.
– iSR5
Nov 11 at 1:43
This will not solve the issue. This will make it so that when I delete Tom from the Photographer table the tuple in the Influences table will still be there. It is supposed to be removed since Tom is referenced in the RPName attribute.
– Johngianni
Nov 11 at 6:22
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have two tables, a Photographer and an Influences table. In the Influences table, a Photographer is able to influence another Photographer.
Relational Schema:
Here is my Diagram in SQL:
Instance Example:
My issue is when I try to delete Photographer Tom, since he influences Jason (as shown in Influences table), I get a error stating:
The DELETE statement conflicted with the REFERENCE constraint "FK_Influences_Photographer1". The conflict occurred in database "jma59", table "dbo.Influences"
If Tom was in the column EPName of the Influences table, I have no issue deleting it. I know that this is a foreign key issue but I am not sure how to handle this situation. I created two separate foreign keys that reference to the Photographer primary key. But the problem is that I cannot make it so that both foreign keys cascade on update and delete.
First foreign key is EPName and EPBDate referencing to PName and PBDate of Photographer
Second foreign key is RPName and RPBDate referning to PName and PBDate of Photographer as well.
The error is:
Unable to create relationship 'FK_Influences_Photographer1'. Introducing FOREIGN KEY constraint 'FK_Influences_Photographer1' on table 'Influences' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Any advice is greatly appreciated!
sql
sql-server edited Nov 10 at 22:04
marc_s
565k12610911243
asked Nov 10 at 21:47
Johngianni
84
I have two tables, a Photographer and an Influences table. In the Influences table, a Photographer is able to influence another Photographer.
Relational Schema:
Here is my Diagram in SQL:
Instance Example:
My issue is when I try to delete Photographer Tom, since he influences Jason (as shown in Influences table), I get a error stating:
The DELETE statement conflicted with the REFERENCE constraint "FK_Influences_Photographer1". The conflict occurred in database "jma59", table "dbo.Influences"
If Tom was in the column EPName of the Influences table, I have no issue deleting it. I know that this is a foreign key issue but I am not sure how to handle this situation. I created two separate foreign keys that reference to the Photographer primary key. But the problem is that I cannot make it so that both foreign keys cascade on update and delete.
First foreign key is EPName and EPBDate referencing to PName and PBDate of Photographer
Second foreign key is RPName and RPBDate referning to PName and PBDate of Photographer as well.
The error is:
Unable to create relationship 'FK_Influences_Photographer1'. Introducing FOREIGN KEY constraint 'FK_Influences_Photographer1' on table 'Influences' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Any advice is greatly appreciated!
sql
sql-server sql
sql-server edited Nov 10 at 22:04
marc_s
565k12610911243
asked Nov 10 at 21:47
Johngianni
84
edited Nov 10 at 22:04
marc_s
565k12610911243
asked Nov 10 at 21:47
Johngianni
84
edited Nov 10 at 22:04
marc_s
565k12610911243
edited Nov 10 at 22:04
marc_s
565k12610911243
edited Nov 10 at 22:04
marc_s
565k12610911243
565k12610911243
asked Nov 10 at 21:47
Johngianni
84
asked Nov 10 at 21:47
Johngianni
84
asked Nov 10 at 21:47
Johngianni
84
84
If two photographers influence each other, I don't understand why you would want to be able to delete one of them. You need to delete the influence relationship first.
– Gordon Linoff
Nov 10 at 23:05
Say for instance I remove Tom from the Photographer table, it should remove the tuple shown above in the Influences table. Likewise if I delete Jason in the Photographer table it should remove that same tuple. I am not sure how to do this since it doesn't let me cascade from two different paths on the same attributes (PName,PBDate) of Photographer.
– Johngianni
Nov 11 at 0:01
forFK_Influences_Photographer1make sure ON DELETE/UPDATE is set to NO Action, then set Enforce Foreign Key Constraint toNOor set it by codeALTER TABLE Influences NOCHECK CONSTRAINT FK_Influences_Photographer1this should make you able to delete Tom while keeping both foreign keys.
– iSR5
Nov 11 at 1:43
This will not solve the issue. This will make it so that when I delete Tom from the Photographer table the tuple in the Influences table will still be there. It is supposed to be removed since Tom is referenced in the RPName attribute.
– Johngianni
Nov 11 at 6:22
add a comment |
If two photographers influence each other, I don't understand why you would want to be able to delete one of them. You need to delete the influence relationship first.
– Gordon Linoff
Nov 10 at 23:05
Say for instance I remove Tom from the Photographer table, it should remove the tuple shown above in the Influences table. Likewise if I delete Jason in the Photographer table it should remove that same tuple. I am not sure how to do this since it doesn't let me cascade from two different paths on the same attributes (PName,PBDate) of Photographer.
– Johngianni
Nov 11 at 0:01
forFK_Influences_Photographer1make sure ON DELETE/UPDATE is set to NO Action, then set Enforce Foreign Key Constraint toNOor set it by codeALTER TABLE Influences NOCHECK CONSTRAINT FK_Influences_Photographer1this should make you able to delete Tom while keeping both foreign keys.
– iSR5
Nov 11 at 1:43
This will not solve the issue. This will make it so that when I delete Tom from the Photographer table the tuple in the Influences table will still be there. It is supposed to be removed since Tom is referenced in the RPName attribute.
– Johngianni
Nov 11 at 6:22
If two photographers influence each other, I don't understand why you would want to be able to delete one of them. You need to delete the influence relationship first.
– Gordon Linoff
Nov 10 at 23:05
If two photographers influence each other, I don't understand why you would want to be able to delete one of them. You need to delete the influence relationship first.
– Gordon Linoff
Nov 10 at 23:05
Say for instance I remove Tom from the Photographer table, it should remove the tuple shown above in the Influences table. Likewise if I delete Jason in the Photographer table it should remove that same tuple. I am not sure how to do this since it doesn't let me cascade from two different paths on the same attributes (PName,PBDate) of Photographer.
– Johngianni
Nov 11 at 0:01
Say for instance I remove Tom from the Photographer table, it should remove the tuple shown above in the Influences table. Likewise if I delete Jason in the Photographer table it should remove that same tuple. I am not sure how to do this since it doesn't let me cascade from two different paths on the same attributes (PName,PBDate) of Photographer.
– Johngianni
Nov 11 at 0:01
for
FK_Influences_Photographer1 make sure ON DELETE/UPDATE is set to NO Action, then set Enforce Foreign Key Constraint to NO or set it by code ALTER TABLE Influences NOCHECK CONSTRAINT FK_Influences_Photographer1 this should make you able to delete Tom while keeping both foreign keys.– iSR5
Nov 11 at 1:43
for
FK_Influences_Photographer1 make sure ON DELETE/UPDATE is set to NO Action, then set Enforce Foreign Key Constraint to NO or set it by code ALTER TABLE Influences NOCHECK CONSTRAINT FK_Influences_Photographer1 this should make you able to delete Tom while keeping both foreign keys.– iSR5
Nov 11 at 1:43
This will not solve the issue. This will make it so that when I delete Tom from the Photographer table the tuple in the Influences table will still be there. It is supposed to be removed since Tom is referenced in the RPName attribute.
– Johngianni
Nov 11 at 6:22
This will not solve the issue. This will make it so that when I delete Tom from the Photographer table the tuple in the Influences table will still be there. It is supposed to be removed since Tom is referenced in the RPName attribute.
– Johngianni
Nov 11 at 6:22
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
You could consider using a Trigger rather than a cascading delete to enforce referential integrity: https://support.microsoft.com/en-gb/help/321843/error-message-1785-occurs-when-you-create-a-foreign-key-constraint-tha
answered Nov 10 at 21:55
Joe
98.4k24151279
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
You could consider using a Trigger rather than a cascading delete to enforce referential integrity: https://support.microsoft.com/en-gb/help/321843/error-message-1785-occurs-when-you-create-a-foreign-key-constraint-tha
answered Nov 10 at 21:55
Joe
98.4k24151279
add a comment |
up vote
0
down vote
You could consider using a Trigger rather than a cascading delete to enforce referential integrity: https://support.microsoft.com/en-gb/help/321843/error-message-1785-occurs-when-you-create-a-foreign-key-constraint-tha
answered Nov 10 at 21:55
Joe
98.4k24151279
add a comment |
up vote
0
down vote
up vote
0
down vote
You could consider using a Trigger rather than a cascading delete to enforce referential integrity: https://support.microsoft.com/en-gb/help/321843/error-message-1785-occurs-when-you-create-a-foreign-key-constraint-tha
answered Nov 10 at 21:55
Joe
98.4k24151279
You could consider using a Trigger rather than a cascading delete to enforce referential integrity: https://support.microsoft.com/en-gb/help/321843/error-message-1785-occurs-when-you-create-a-foreign-key-constraint-tha
answered Nov 10 at 21:55
Joe
98.4k24151279
answered Nov 10 at 21:55
Joe
98.4k24151279
answered Nov 10 at 21:55
Joe
98.4k24151279
answered Nov 10 at 21:55
Joe
98.4k24151279
98.4k24151279
add a comment |
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53243734%2flinking-two-foreign-keys-to-one-primary-key%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
If two photographers influence each other, I don't understand why you would want to be able to delete one of them. You need to delete the influence relationship first.
– Gordon Linoff
Nov 10 at 23:05
Say for instance I remove Tom from the Photographer table, it should remove the tuple shown above in the Influences table. Likewise if I delete Jason in the Photographer table it should remove that same tuple. I am not sure how to do this since it doesn't let me cascade from two different paths on the same attributes (PName,PBDate) of Photographer.
– Johngianni
Nov 11 at 0:01
for
FK_Influences_Photographer1make sure ON DELETE/UPDATE is set to NO Action, then set Enforce Foreign Key Constraint toNOor set it by codeALTER TABLE Influences NOCHECK CONSTRAINT FK_Influences_Photographer1this should make you able to delete Tom while keeping both foreign keys.– iSR5
Nov 11 at 1:43
This will not solve the issue. This will make it so that when I delete Tom from the Photographer table the tuple in the Influences table will still be there. It is supposed to be removed since Tom is referenced in the RPName attribute.
– Johngianni
Nov 11 at 6:22