2 Replies Latest reply on Nov 9, 2015 4:45 PM by rp0428

    “BY DEFAULT”

    68894724-86b8-46f3-a78a-a96cda75ede5

      What happens when you use IDENTITY column defined as “BY DEFAULT” (users can explicitly insert values into the identity column) for a primary key column, will an insert statement fail in the case when the sequence next value already exists in the identity column?

        • 1. Re: “BY DEFAULT”
          thatJeffSmith-Oracle

          >>for a primary key column, will an insert statement fail in the case when the sequence next value already exists in the identity column?

          IDENTITY column or BY DEFAULT regardless, if you try to insert a non-unique, which is what you'll have if the sequence already exists, it's going to fail.

           

          I don't think that's your question though. Anyways, this isn't the right forum for it. You'll want the SQL space.

          • 2. Re: “BY DEFAULT”

            68894724-86b8-46f3-a78a-a96cda75ede5 wrote:

             

            What happens when you use IDENTITY column defined as “BY DEFAULT” (users can explicitly insert values into the identity column) for a primary key column, will an insert statement fail in the case when the sequence next value already exists in the identity column?

            What happened when you tried it?

             

            Don't be afraid of breaking Oracle by actually trying things. That is often the FASTEST, and best, way to learn.

             

            An INSERT/UPDATE will ALWAYS fail for duplicate values when the column is a primary key or has a unique key defined on it.

             

            It makes NO difference if the value being inserted was generated by a sequence or supplied by a user. The BY DEFAULT simply prevents the sequence from being used if the user provides the value.

             

            As already stated - if you need further help then mark this thread ANSWERED and repost in the proper forum.