1

I have two tables users and bans.

The second table has two columns user_id_from and user_id_to with foreign keys to column id of the first table. I set ON UPDATE option to both and it throw me an error. I work in VS with SQL Server and only get a message "Script execution error" without description.

The script is executing if I add an option for only one key but I can't understand why it's not working for both together like in the code below.

CREATE TABLE [users] 
(
    [id]               INT IDENTITY (1, 1) NOT NULL,
    PRIMARY KEY CLUSTERED ([id] ASC)
);

CREATE TABLE [bans] 
(
    [id]                 INT IDENTITY (1, 1) NOT NULL,
    [user_id_from]       INT           NOT NULL,
    [user_id_to]         INT           NOT NULL,

    PRIMARY KEY CLUSTERED ([id] ASC),

    CONSTRAINT [FK_1_bans_TO_users] 
        FOREIGN KEY ([user_id_from]) REFERENCES [dbo].[users] ([id]) 
            ON UPDATE CASCADE,

    CONSTRAINT [FK_2_bans_TO_users] 
        FOREIGN KEY ([user_id_to]) REFERENCES [dbo].[users] ([id]) 
            ON UPDATE CASCADE
);
John-9198
  • 11
  • 2
  • What is your 1 question? Your title & 2 body questions disgree. And the last doesn't need the rest of the body. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. Reflect your research. See [ask] & the voting arrow mouseover texts. If you post a question, use one phrasing as title. PS Asking for off-site resources is off-topic. [help] PS " it's not executing" is not helpful. – philipxy Oct 13 '20 at 04:08
  • Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code in table format. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. – philipxy Oct 13 '20 at 04:10
  • 1
    Googling 'sql server can't add two on delete' ... [MS SQL “ON DELETE CASCADE” multiple foreign keys pointing to the same table?](https://stackoverflow.com/q/5018099/3404097) – philipxy Oct 13 '20 at 04:15
  • I apologize for the poor presentation of the question, this is the first time I do this, also I don't know much about the material. Now I figured it out, thanks for the link, I just assumed that this is a problem specifically in SQL, and not in SQL Server, so I didn't google it correctly. Thank you! – John-9198 Oct 13 '20 at 10:36
  • It won't be just SQL Server. However the details will probably vary for DBMSs. What could a rule be for chains of SQL FKs in general? It is not clear. What if the cascades disagree? Research it. I just started with that tag so you would get the most specific answers. I looked at exactly 1 hit after exactly 1 google. It answered your particular error message on your particular DBMS. Also research site protocols & goals. Good luck. PS Duplicates should be closed as duplicates, not answers. See my comment on your answer & google the topic & others with 'site:meta.stackoverflow.com'. – philipxy Oct 13 '20 at 11:13
  • Okay, thanks. It is difficult to understand how everything works here, but it is amazing how many people influence one small process. Should I delete my answer and if so how, because I see the "undelete" instead of "delete" under it? Or should I delete the whole question? – John-9198 Oct 13 '20 at 15:10
  • Poorly received posts (which includes, no upvotes) count towards question bans whether deleted or not. This post is around forever. Why not edit per the comments & links to improve it, to merit upvotes & not downvotes? (As a duplicate it is bypassed for some readers but visible to others.). [ask] [help] [meta] [meta.se] – philipxy Oct 13 '20 at 22:33
  • Do you mean that I should edit the question by adding an explanation of why this is happening and additional links if I find something I can refer to? – John-9198 Oct 14 '20 at 02:04
  • Please act on my 1st 2 comments. (Other than the duplicate search.) I don't understand how you can think you acted on them. If you have a question about a particular thing in my comments or links, ask for clarification in a comment. (Including, give minimal code & show "try to add ON DELETE or / and ON UPDATE to them, I can't do it for both".) – philipxy Oct 14 '20 at 02:42
  • Okay, I edited the question, hopefully corrected the disagree of the questions and deleted extra code. But again - if the question is a duplicate, and I was just dumb for not finding the same question, should I just leave it as it is? Because the answer already exists – John-9198 Oct 14 '20 at 04:10

0 Answers0