Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (No Skin)
  • No Skin
Collapse
Code Project
CODE PROJECT For Those Who Code
  • Home
  • Articles
  • FAQ
Community
  1. Home
  2. Database & SysAdmin
  3. Database
  4. Primary key on Bit Datatype in sql server 2000

Primary key on Bit Datatype in sql server 2000

Scheduled Pinned Locked Moved Database
databasesql-serversysadminquestion
7 Posts 3 Posters 0 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • T Offline
    T Offline
    Tufail Ahmad
    wrote on last edited by
    #1

    How can i assign primary key to a field having BIT datatype in sql server 2000. Thanks Tufail

    M L 2 Replies Last reply
    0
    • T Tufail Ahmad

      How can i assign primary key to a field having BIT datatype in sql server 2000. Thanks Tufail

      M Offline
      M Offline
      Mycroft Holmes
      wrote on last edited by
      #2

      A bit data type stores ones or zeros, you intend to have only 2 records in your table? I generally use int but even there I have run into the 32k limit.

      1 Reply Last reply
      0
      • T Tufail Ahmad

        How can i assign primary key to a field having BIT datatype in sql server 2000. Thanks Tufail

        L Offline
        L Offline
        Lost User
        wrote on last edited by
        #3

        To an existing table, or a new table?

        DECLARE @TestTable TABLE(
        [col1] bit NOT NULL PRIMARY KEY,
        [col2] varchar(10) NULL
        )

        INSERT @TestTable
        SELECT 0, 'item 1'
        UNION ALL
        SELECT 1, 'item 2'

        SELECT *
        FROM @TestTable;

        Results in; col1 col2 ----- ---------- 0 item 1 1 item 2

        INSERT @TestTable (col1, col2) VALUES (9, 'item 3')

        Will fail, since "9" is too large a value for type 'BIT'. The error will say that it's duplicate, since it tries to convert the "9" to a BIT, resulting in the value "1":

        PRINT CAST(9 AS BIT)

        Results in "1"

        INSERT @TestTable (col1, col2) VALUES (1, 'item 42')

        Will fail due to the primary key, since there's already an item with a value "1" for col1.

        I are Troll :suss:

        M 1 Reply Last reply
        0
        • L Lost User

          To an existing table, or a new table?

          DECLARE @TestTable TABLE(
          [col1] bit NOT NULL PRIMARY KEY,
          [col2] varchar(10) NULL
          )

          INSERT @TestTable
          SELECT 0, 'item 1'
          UNION ALL
          SELECT 1, 'item 2'

          SELECT *
          FROM @TestTable;

          Results in; col1 col2 ----- ---------- 0 item 1 1 item 2

          INSERT @TestTable (col1, col2) VALUES (9, 'item 3')

          Will fail, since "9" is too large a value for type 'BIT'. The error will say that it's duplicate, since it tries to convert the "9" to a BIT, resulting in the value "1":

          PRINT CAST(9 AS BIT)

          Results in "1"

          INSERT @TestTable (col1, col2) VALUES (1, 'item 42')

          Will fail due to the primary key, since there's already an item with a value "1" for col1.

          I are Troll :suss:

          M Offline
          M Offline
          Mycroft Holmes
          wrote on last edited by
          #4

          Slow day up North is it, besides 42 ALWAYS works, your definition of BIT must be wrong.

          Never underestimate the power of human stupidity RAH

          L 1 Reply Last reply
          0
          • M Mycroft Holmes

            Slow day up North is it, besides 42 ALWAYS works, your definition of BIT must be wrong.

            Never underestimate the power of human stupidity RAH

            L Offline
            L Offline
            Lost User
            wrote on last edited by
            #5

            Mycroft Holmes wrote:

            Slow day up North is it, besides 42 ALWAYS works, your definition of BIT must be wrong.

            I'm slow in thinking today. Item 42 shouldn't work, since there already is an item with the pk-value of "1" inserted into the table. BIT = { 0 | 1 }, and the first INSERT statement should fill the table to it's maximum capacity.

            I are Troll :suss:

            M 1 Reply Last reply
            0
            • L Lost User

              Mycroft Holmes wrote:

              Slow day up North is it, besides 42 ALWAYS works, your definition of BIT must be wrong.

              I'm slow in thinking today. Item 42 shouldn't work, since there already is an item with the pk-value of "1" inserted into the table. BIT = { 0 | 1 }, and the first INSERT statement should fill the table to it's maximum capacity.

              I are Troll :suss:

              M Offline
              M Offline
              Mycroft Holmes
              wrote on last edited by
              #6

              Eddy Vluggen wrote:

              I'm slow in thinking today

              Sure are, 42 is tha answer to EVERYTHING, reference Hitchhikers Guide to the Galaxy. Oops missed the joke icon....

              Never underestimate the power of human stupidity RAH

              L 1 Reply Last reply
              0
              • M Mycroft Holmes

                Eddy Vluggen wrote:

                I'm slow in thinking today

                Sure are, 42 is tha answer to EVERYTHING, reference Hitchhikers Guide to the Galaxy. Oops missed the joke icon....

                Never underestimate the power of human stupidity RAH

                L Offline
                L Offline
                Lost User
                wrote on last edited by
                #7

                That's one magic number that I shouldn't have missed :wtf:

                I are Troll :)

                1 Reply Last reply
                0
                Reply
                • Reply as topic
                Log in to reply
                • Oldest to Newest
                • Newest to Oldest
                • Most Votes


                • Login

                • Don't have an account? Register

                • Login or register to search.
                • First post
                  Last post
                0
                • Categories
                • Recent
                • Tags
                • Popular
                • World
                • Users
                • Groups