Select subquery CHECK constraint DB2





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty{ margin-bottom:0;
}






up vote
1
down vote

favorite












I have 2 tables: TVSHOW and ACTOR. I want to reject records if the inserted actor in TVSHOW does not exist in the ACTOR table using DB2.



db2 "ALTER TABLE TVSHOW ADD CONSTRAINT check_actor CHECK (actor in (select id from ACTOR))"


I keep running into, During SQL processing it returned: SQL0546N The check constraint "check_actor" is invalid.



How can I fix this?










share|improve this question







New contributor




user2251234 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.


























    up vote
    1
    down vote

    favorite












    I have 2 tables: TVSHOW and ACTOR. I want to reject records if the inserted actor in TVSHOW does not exist in the ACTOR table using DB2.



    db2 "ALTER TABLE TVSHOW ADD CONSTRAINT check_actor CHECK (actor in (select id from ACTOR))"


    I keep running into, During SQL processing it returned: SQL0546N The check constraint "check_actor" is invalid.



    How can I fix this?










    share|improve this question







    New contributor




    user2251234 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
    Check out our Code of Conduct.






















      up vote
      1
      down vote

      favorite









      up vote
      1
      down vote

      favorite











      I have 2 tables: TVSHOW and ACTOR. I want to reject records if the inserted actor in TVSHOW does not exist in the ACTOR table using DB2.



      db2 "ALTER TABLE TVSHOW ADD CONSTRAINT check_actor CHECK (actor in (select id from ACTOR))"


      I keep running into, During SQL processing it returned: SQL0546N The check constraint "check_actor" is invalid.



      How can I fix this?










      share|improve this question







      New contributor




      user2251234 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.











      I have 2 tables: TVSHOW and ACTOR. I want to reject records if the inserted actor in TVSHOW does not exist in the ACTOR table using DB2.



      db2 "ALTER TABLE TVSHOW ADD CONSTRAINT check_actor CHECK (actor in (select id from ACTOR))"


      I keep running into, During SQL processing it returned: SQL0546N The check constraint "check_actor" is invalid.



      How can I fix this?







      db2 constraint






      share|improve this question







      New contributor




      user2251234 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.











      share|improve this question







      New contributor




      user2251234 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      share|improve this question




      share|improve this question






      New contributor




      user2251234 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      asked Nov 24 at 19:33









      user2251234

      82




      82




      New contributor




      user2251234 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.





      New contributor





      user2251234 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






      user2251234 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






















          1 Answer
          1






          active

          oldest

          votes

















          up vote
          5
          down vote



          accepted










          You cannot use a subselect in a check constraint; you can only reference columns from the same table.



          However, what you want to accomplish is what referential integrity constraints, also known as foreign keys, are for.



          ALTER TABLE TVSHOW ADD CONSTRAINT actor_fk FOREIGN KEY (actor) REFERENCES ACTOR(id)


          Presumably you have already defined a primary key constraint on ACTOR.id; if not, you need to define it, or a unique constraint on that column.






          share|improve this answer























          • @MarkBarinstein thank you, corrected.
            – mustaccio
            Nov 25 at 14:17











          Your Answer








          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "182"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          convertImagesToLinks: false,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: null,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });






          user2251234 is a new contributor. Be nice, and check out our Code of Conduct.










           

          draft saved


          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f223340%2fselect-subquery-check-constraint-db2%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes








          up vote
          5
          down vote



          accepted










          You cannot use a subselect in a check constraint; you can only reference columns from the same table.



          However, what you want to accomplish is what referential integrity constraints, also known as foreign keys, are for.



          ALTER TABLE TVSHOW ADD CONSTRAINT actor_fk FOREIGN KEY (actor) REFERENCES ACTOR(id)


          Presumably you have already defined a primary key constraint on ACTOR.id; if not, you need to define it, or a unique constraint on that column.






          share|improve this answer























          • @MarkBarinstein thank you, corrected.
            – mustaccio
            Nov 25 at 14:17















          up vote
          5
          down vote



          accepted










          You cannot use a subselect in a check constraint; you can only reference columns from the same table.



          However, what you want to accomplish is what referential integrity constraints, also known as foreign keys, are for.



          ALTER TABLE TVSHOW ADD CONSTRAINT actor_fk FOREIGN KEY (actor) REFERENCES ACTOR(id)


          Presumably you have already defined a primary key constraint on ACTOR.id; if not, you need to define it, or a unique constraint on that column.






          share|improve this answer























          • @MarkBarinstein thank you, corrected.
            – mustaccio
            Nov 25 at 14:17













          up vote
          5
          down vote



          accepted







          up vote
          5
          down vote



          accepted






          You cannot use a subselect in a check constraint; you can only reference columns from the same table.



          However, what you want to accomplish is what referential integrity constraints, also known as foreign keys, are for.



          ALTER TABLE TVSHOW ADD CONSTRAINT actor_fk FOREIGN KEY (actor) REFERENCES ACTOR(id)


          Presumably you have already defined a primary key constraint on ACTOR.id; if not, you need to define it, or a unique constraint on that column.






          share|improve this answer














          You cannot use a subselect in a check constraint; you can only reference columns from the same table.



          However, what you want to accomplish is what referential integrity constraints, also known as foreign keys, are for.



          ALTER TABLE TVSHOW ADD CONSTRAINT actor_fk FOREIGN KEY (actor) REFERENCES ACTOR(id)


          Presumably you have already defined a primary key constraint on ACTOR.id; if not, you need to define it, or a unique constraint on that column.







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Nov 25 at 14:17

























          answered Nov 24 at 20:03









          mustaccio

          8,87762135




          8,87762135












          • @MarkBarinstein thank you, corrected.
            – mustaccio
            Nov 25 at 14:17


















          • @MarkBarinstein thank you, corrected.
            – mustaccio
            Nov 25 at 14:17
















          @MarkBarinstein thank you, corrected.
          – mustaccio
          Nov 25 at 14:17




          @MarkBarinstein thank you, corrected.
          – mustaccio
          Nov 25 at 14:17










          user2251234 is a new contributor. Be nice, and check out our Code of Conduct.










           

          draft saved


          draft discarded


















          user2251234 is a new contributor. Be nice, and check out our Code of Conduct.













          user2251234 is a new contributor. Be nice, and check out our Code of Conduct.












          user2251234 is a new contributor. Be nice, and check out our Code of Conduct.















           


          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f223340%2fselect-subquery-check-constraint-db2%23new-answer', 'question_page');
          }
          );

          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







          Popular posts from this blog

          AnyDesk - Fatal Program Failure

          How to calibrate 16:9 built-in touch-screen to a 4:3 resolution?

          QoS: MAC-Priority for clients behind a repeater