Forum Stats

  • 3,784,122 Users
  • 2,254,894 Discussions
  • 7,880,697 Comments

Discussions

Prevent inserting null into table

mikrimouse
mikrimouse Member Posts: 121
edited May 7, 2018 12:54PM in SQL & PL/SQL

MERGE INTO PRIORITY_LEAGUES M

      USING  (

      SELECT DISTINCT COMP_NAME AS COMP_NAME FROM LIVE_MATCHES_TZ

      ) LM ON (LM.COMP_NAME=M.SUB_LIST)

      WHEN NOT MATCHED THEN

        INSERT

        (

          M.SUB_LIST,

          M.PRIORITY,

          M.SPORT,

          M.CREATED

        )

        VALUES

        (

          LM.COMP_NAME,

          P_PRIORITY,

          p_sport_s,

          SYSDATE

        );

        COMMIT;

I need to prevent inserting null if value M.SUB_LIST is null. I set m.sub_list is nullable , but how can i prevent to insert null.
Thanks !!

Best Answer

  • Hans Steijntjes
    Hans Steijntjes Member Posts: 614 Bronze Trophy
    edited May 4, 2018 4:07AM Accepted Answer

    You could change

    SELECT DISTINCT COMP_NAME AS COMP_NAME FROM LIVE_MATCHES_TZ

    into

    SELECT DISTINCT COMP_NAME AS COMP_NAME FROM LIVE_MATCHES_TZ WHERE COMP_NAME IS NOT NULL

    As an alternative you could also add  a WHERE clause to the insert-statement, as in:

    INSERT

            (

              M.SUB_LIST,

              M.PRIORITY,

              M.SPORT,

              M.CREATED

            )

            VALUES

            (

              LM.COMP_NAME,

              P_PRIORITY,

              p_sport_s,

              SYSDATE

            )

    WHERE LM.COMP_NAME IS NOT NULL

Answers

  • Hans Steijntjes
    Hans Steijntjes Member Posts: 614 Bronze Trophy
    edited May 4, 2018 4:07AM Accepted Answer

    You could change

    SELECT DISTINCT COMP_NAME AS COMP_NAME FROM LIVE_MATCHES_TZ

    into

    SELECT DISTINCT COMP_NAME AS COMP_NAME FROM LIVE_MATCHES_TZ WHERE COMP_NAME IS NOT NULL

    As an alternative you could also add  a WHERE clause to the insert-statement, as in:

    INSERT

            (

              M.SUB_LIST,

              M.PRIORITY,

              M.SPORT,

              M.CREATED

            )

            VALUES

            (

              LM.COMP_NAME,

              P_PRIORITY,

              p_sport_s,

              SYSDATE

            )

    WHERE LM.COMP_NAME IS NOT NULL

  • mikrimouse
    mikrimouse Member Posts: 121
    edited May 4, 2018 4:24AM

    Thanks I will try it... Can I now return column sub_list to not nullable ?

  • Hans Steijntjes
    Hans Steijntjes Member Posts: 614 Bronze Trophy
    edited May 4, 2018 4:34AM

    As long as there a no NULL values currently in the sub_list column, you can.

    Otherwise you can create a check constraint on the column with enable novalidate, but it is better to set the column to NOT NULL if possible.

  • Unknown
    edited May 7, 2018 12:54PM
    Thanks I will try it... Can I now return column sub_list to not nullable ?

    Why would you do that if the values are NOT supposed to be NULL? Your question doesn't even make any sense if you are now going to do that.

    I suggest you make up your mind what the data model needs to be and then design your objects based on that.

    If the data shouldn't be null then use a NOT NULL constraint.

    If the data is allowed to be null then you don't need to change your query to deal with null values.

This discussion has been closed.