0
INSERT INTO Locality(versionNo,localityName, localityType, constiCode, deleteFlag,
    createdOn, lastUpdate,updatedBy) 
SELECT 1, localityName, Localitytype, constituencyCode + '' + regionCode, 1,
    CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, CURRENT_USER
FROM dbo.MasterTable.

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'localityCode', table 'GovMaster.dbo.Locality';
column does not allow nulls. INSERT fails.

I want to insert but the LocalityCode is primary key and should not be null. But I get that error above. The localityCode is primary key and it can not be null. And i want the localityCode to be auto generated, it should do an autoincrement on insert.

user2271980
  • 11
  • 6
  • 3
    The error message states that you're trying to put a null value into the `localityCode` column. Either fix your code to not supply null values for that column or update your DB schema to allow nulls there. – Ben N Aug 03 '15 at 14:51
  • tags mismatch: you are dealing with mysql or microsoft sql server? also have a look to the [ask howto](http://stackoverflow.com/help/how-to-ask) about guidelines on writing good questions – Paolo Aug 03 '15 at 14:53
  • Why not Postgres? We don't know _what_ this guy is using. – Tim Biegeleisen Aug 03 '15 at 14:53
  • localityCode is a primary key and supposed not to take null values, but I have set the primary key to is-identity to allow autoincrement. But still getting that error. @BenN – user2271980 Aug 03 '15 at 14:54
  • Yes microsoft sql server @Paolo – user2271980 Aug 03 '15 at 14:54
  • so modify the question and put correct tags. also add some relevant detail to the question because it is almost empty... – Paolo Aug 03 '15 at 14:55
  • Could you do a CREATE TABLE script on the table in question? It might have the Is-Identity property set correctly but missing the Increment and Seed. – TTeeple Aug 03 '15 at 15:03

3 Answers3

1

You're not inserting a value for localityCode, it's missing from your field list:

INSERT INTO Locality(versionNo,localityName, localityType, constiCode, deleteFlag,
    createdOn, lastUpdate,updatedBy)

When you leave a field off the INSERT list, it gets a default NULL value. If it were an auto-incrementing ID field, it would be correct to exclude it, but it is not in your case. You either need to supply a value, or change the schema to allow NULL values for that field, or make it an auto-incrementing field.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
Hart CO
  • 34,064
  • 6
  • 48
  • 63
0

Just cutting to the chase, the reason you are getting that error is because:

Problem:

  • You have a column name localityCode defined as a NOT NULL in your Locality table and it does not appear to be an auto-incrementing field.
  • Secondly, you are not passing any value in your insert statement below, hence it's passing a null value by default to your localityCode column.

Solution:

  • Ideally, you would want to pass some non-null values to your localityCode explicitly in your insert statement.
  • The other option would be change your null property for your localityCode column to accept NULL values.
  • Alternatively, if you want to set the localityCode column as an auto-increment field, you may want to look into this: Adding an identity to an existing column

Hope this helps!

Community
  • 1
  • 1
FutbolFan
  • 13,235
  • 3
  • 23
  • 35
  • The localityCode is primary key and it can not be null. And i want the localityCode to be auto generated, it should do an autoincrement. @Futbolfan – user2271980 Aug 03 '15 at 15:06
  • @user2271980 The answer given by Hart & Co. appears to be spot on. Please have a look at that. – Tim Biegeleisen Aug 03 '15 at 15:06
  • @user2271980 I think you have two options: either recreate the table and set the `localityCode` identity property on OR add a new column and set that as your primary key with identity property and drop this existing column. – FutbolFan Aug 03 '15 at 15:14
0

Thank you guys.

I dropped the existing column and recreated it with localityCode INT IDENTITY(1,1) and alter the column to primary key after that.

:-)

user2271980
  • 11
  • 6
  • Ideally, in SO you would want to post your own answer as a solution only if nobody had provided any direction. If someone has taken their `valuable` time to respond to your question correctly, it is a common courtesy in SO to either accept their solution as the accepted answer or upvote (or both in most cases). – FutbolFan Aug 03 '15 at 15:36
  • Please pardon me, i'm quite not a common user on this platform. I will do so next time. Some features are not going through due to my current status. @FutbolFan. Thanks – user2271980 Aug 03 '15 at 15:55
  • I think @HartCO deserves a big credit as well for pointing you to the right direction. And, I can definitely understand that your reputation level has some restrictions at the moment. – FutbolFan Aug 03 '15 at 15:57