Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2004
    Posts
    15

    Unanswered: Primary key means it should not accept null values is it?

    Friends,

    I am inserting a value from vb.net to sql server 7.
    i created a table in the sqlserver with primary key.
    In VB.net form i didnt check the values of the check box.
    when i try to save the empty or null the sql server should throw the error. but its accepting the null value.
    how is it possible?
    can anyone help me?
    FYI:
    table name: JobMast
    Fields:
    jobcode int primary key
    jobtitle varchar(10)

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You sure?

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(jobcode int PRIMARY KEY, jobtitle varchar(10))
    GO
    
    INSERT INTO myTable99(jobcode, jobtitle)
    SELECT 1, 'Windows'
    GO
    
    INSERT INTO myTable99(jobcode, jobtitle)
    SELECT Null, 'Windows'
    GO
    
    DROP TABLE myTable99
    GO
    
    CREATE TABLE myTable99(jobcode int NOT NULL PRIMARY KEY, jobtitle varchar(10))
    GO
    INSERT INTO myTable99(jobcode, jobtitle)
    SELECT 1, 'Windows'
    GO
    
    INSERT INTO myTable99(jobcode, jobtitle)
    SELECT Null, 'Windows'
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2003
    Posts
    364
    I think what Brett was trying to say is PK columns can be NULL if you haven't specified them to be NOT NULL.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You know...I WAS gonna say that, till I ran the test.

    A PK CAN NOT be Null...

    It's the UNIQUE INDEX thing that allows a single NULL...but not a PK..

    Cut and paste the code.

    You'll see the failure....

    DB2 allows the 1 value in the PK...and will allow as many as you want with the option "ALLOW NULLS" in the PK syntax..

    I get so confused sometime...

    That's why I try and test everything before I post....

    (Notice the word "Try")
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Sep 2003
    Posts
    364
    Hmm, I'd never tried it but had always assumed a PK column could be nullable like a column with a unique index. I wonder why MS chose this?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It's one of those relational algebra thingies... A PK has to be comparable, and NULL values aren't comparable.

    With that said of course, in relational algebra any NULL value is unique even though it isn't comparable. This leads to a nasty connundrum of making it possible for an attribute with a domain of 0-100 to have 1000 unique tuples... Not the kind of place you want to go without a LOT of beer!

    -PatP

  7. #7
    Join Date
    Mar 2004
    Posts
    15
    So whenever im giving primary key..........

    Either "Set NoCount on"

    or

    Give "Not Null Primary key"

    is it so?

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sorry to confuse you, but NOCOUNT has nothing to do with it...

    And Yes, I would normally cose NOT NULL PRIMARY KEY...

    But if you cut and paste the code, AND run it, you will see you CAN NOT insert a NULL in to a PRIMARY KEY....


    Sooooo....unless you can give us an example where that's happening, we are skeptical of your claim.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Mar 2004
    Posts
    15
    I created a job_mast table using Enterprise Manager. I assigned a primary key to JobCode and not null for jobtitle.
    using the below vb.net code if i insert the empty form its inserting.
    i checked in the table its showing empty values in the table.




    Imports System.Data.SqlClient

    Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
    Dim cn As New SqlConnection(SqlMISConnString)
    cn.Open()
    Dim sql As String

    sql = "Insert into job_mast(job_code,job_title)values('" & Trim(txtjobcode.Text) & "', '" & Trim(txtjobtitle.Text) & "')"

    Dim cmd As New SqlCommand(sql, cn)

    Try
    cmd.ExecuteNonQuery()
    MsgBox("Record Saved", MsgBoxStyle.OKOnly, "Job Master Form")

    Catch ex As Exception
    MsgBox(ex.Message)
    Exit Sub
    Finally
    End Try

    cmd.Dispose()
    cn.Close()
    cn.Dispose()

    End Sub

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think you have a unique index, not a primary key.

    Script our your table using Enterprise Manager, and be sure to include all the indexes and Primary Key, and then post it on the forum.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Oct 2003
    Posts
    706
    A primary-key is supposed to be implicitly "NOT NULL," although not all DBMSes enforce it.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  12. #12
    Join Date
    Mar 2004
    Posts
    15
    Below is the script generated by Enterprise Manager

    if exists (select * from sysobjects where id = object_id(N'[dbo].[JOB_MAST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[JOB_MAST]
    GO

    CREATE TABLE [dbo].[JOB_MAST] (
    [JOB_CODE] [varchar] (3) NOT NULL ,
    [JOB_TITLE] [varchar] (30) NOT NULL ,
    [Del_Stat] [char] (1) NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[JOB_MAST] WITH NOCHECK ADD
    CONSTRAINT [PK_JOB_MAST] PRIMARY KEY NONCLUSTERED
    (
    [JOB_CODE]
    ) ON [PRIMARY]
    GO

  13. #13
    Join Date
    Feb 2002
    Location
    Assam, India
    Posts
    55

    Smile

    Are you sure that "null" is being passed to the stored procedure for insertion?
    Because in VB if you have a text box say Text1.text and you do not write anything in it, what is passed is not null but an empty string.

    So inside the stored procedure for doing INSERTs, you will have to write the following piece of code before the INSERT statement. for example

    -- SQL CODE BEGINS HERE
    create proc insert_authors_table
    @author_id varchar(20),
    @author_name varchar(50)
    @address varchar(80)
    as
    begin
    -- if empty string, make it null using this code
    set @author_id = ltrim(rtrim(@author_id))

    INSERT INTO authors_table (author_id, author_name, address)
    select @author_id, @author_name, @address

    end

    -- SQL CODE ENDS HERE

    This should throw an error.

    Roshmi Choudhury

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •