none
Violation of PRIMARY KEY RRS feed

  • Question

  • I am making one client server application in which at server side I made tables using this code


    Database db = server.Databases["dbo"];

    nonqueryCommand.CommandText = "CREATE TABLE UserInfo (ID varchar(50),LoginName varchar(50),Date varchar(50),Time varchar(50) CONSTRAINT PKid PRIMARY KEY (ID))";
    nonqueryCommand.ExecuteNonQuery();

    nonqueryCommand.CommandText = "CREATE TABLE RAM ( ID varchar(50),PartNo integer,SerialNo integer,SizeMb integer ,SizeGB integer,TotalRam integer CONSTRAINT FKRid FOREIGN KEY(ID) REFERENCES UserInfo) ";
    nonqueryCommand.ExecuteNonQuery();


    and it works fine create the tables and link to primary and foriegn key too .

    Error comes when i want to insert the data in the database named "dbo"

    and this error comes when i enter the same data second time .

    error is

    Violation of PRIMARY KEY constraint 'PKid'. Cannot insert duplicate key in object 'dbo.UserInfo'.
    The statement has been terminated.


    and Inserting Code is

    nonqueryCommand.CommandText = "INSERT INTO UserInfo([ID],[LoginName],[Date],[Time]) VALUES (@stloginname,@sthostname,@stdate,@sttime)";
    nonqueryCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@stloginname", stloginname));
    nonqueryCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sthostname", sthostname));
    nonqueryCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@stdate", stdate));
    nonqueryCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sttime", sttime));
    nonqueryCommand.ExecuteNonQuery();

    gives error @ nonqueryCommand.ExecuteNonQuery();
    Wednesday, February 17, 2010 2:31 AM

Answers

  • Well, if you want to insert the same data twice, the primary key violation would be expected.

    If you want the ID column to get a new value automatically when inserting the same data twice, then you can set the ID column to be of type IDENTITY.

                using (SqlConnection con = new SqlConnection(cs))
                {
                    try
                    {
                        con.Open();
                        SqlCommand cmd = con.CreateCommand();
    
                        // This will fail
                        //cmd.CommandText = "CREATE TABLE TempTable(cid int primary key, ctxt nvarchar(20))";
                        //cmd.ExecuteNonQuery();
                        //cmd.CommandText = "INSERT INTO TempTable(cid, ctxt) VALUES (1, 'xxxxx')";
                        //cmd.ExecuteNonQuery();
                        //cmd.CommandText = "INSERT INTO TempTable(cid, ctxt) VALUES (1, 'xxxxx')";
                        //cmd.ExecuteNonQuery();
    
                        // This will automatically create ID, then same data can be inserted many times
                        cmd.CommandText = "CREATE TABLE TempTable(cid int identity primary key, ctxt nvarchar(20))";
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "INSERT INTO TempTable(ctxt) VALUES ('xxxxx')";
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "INSERT INTO TempTable(ctxt) VALUES ('xxxxx')";
                        cmd.ExecuteNonQuery();
    
                        con.Close();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex);
                    }
                }

    Creating and Modifying Identifier Columns
    http://msdn.microsoft.com/en-us/library/ms191131.aspx

    //Michael
    This posting is provided "AS IS" with no warranties.
    • Marked as answer by uahmed Thursday, February 18, 2010 1:18 AM
    Wednesday, February 17, 2010 8:34 AM

All replies

  • Well, if you want to insert the same data twice, the primary key violation would be expected.

    If you want the ID column to get a new value automatically when inserting the same data twice, then you can set the ID column to be of type IDENTITY.

                using (SqlConnection con = new SqlConnection(cs))
                {
                    try
                    {
                        con.Open();
                        SqlCommand cmd = con.CreateCommand();
    
                        // This will fail
                        //cmd.CommandText = "CREATE TABLE TempTable(cid int primary key, ctxt nvarchar(20))";
                        //cmd.ExecuteNonQuery();
                        //cmd.CommandText = "INSERT INTO TempTable(cid, ctxt) VALUES (1, 'xxxxx')";
                        //cmd.ExecuteNonQuery();
                        //cmd.CommandText = "INSERT INTO TempTable(cid, ctxt) VALUES (1, 'xxxxx')";
                        //cmd.ExecuteNonQuery();
    
                        // This will automatically create ID, then same data can be inserted many times
                        cmd.CommandText = "CREATE TABLE TempTable(cid int identity primary key, ctxt nvarchar(20))";
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "INSERT INTO TempTable(ctxt) VALUES ('xxxxx')";
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "INSERT INTO TempTable(ctxt) VALUES ('xxxxx')";
                        cmd.ExecuteNonQuery();
    
                        con.Close();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex);
                    }
                }

    Creating and Modifying Identifier Columns
    http://msdn.microsoft.com/en-us/library/ms191131.aspx

    //Michael
    This posting is provided "AS IS" with no warranties.
    • Marked as answer by uahmed Thursday, February 18, 2010 1:18 AM
    Wednesday, February 17, 2010 8:34 AM
  • But I have data type Varchar in my Id column and its work if the data type is integer
    Thursday, February 18, 2010 2:02 AM