Solved

Cannot INSERT VALUES into a Primary Key column, without IDENTITY

Posted on 2013-12-17
5
1,333 Views
Last Modified: 2013-12-17
Dear Experts,

I am trying to edit a Survey application through SQL, instead of using the application GUI.  My goal is to add two new lines into an existing table sur_item_answer, preferably without changing anything else, using code to document the changes done by me, and reuse the code later on a different installation of the same survey application.  

The existing table:

CREATE TABLE [dbo].[sur_item_answer](
      [answer_id] [int] NOT NULL,
      [item_id] [int] NOT NULL,
      [answer_text] [nvarchar](255) NULL,
      [answer_value] [int] NULL,
      [order_number] [int] NOT NULL,
      [random_order_number] [int] NULL,
      [default_yn] [char](1) NOT NULL,
      [js_text] [nvarchar](255) NULL,
      [column_type_id] [int] NOT NULL CONSTRAINT [DF_sur_item_answer_column_type_id]  DEFAULT ((0)),
      [column_text] [nvarchar](1500) NULL,
      [validation_ids] [nvarchar](255) NULL,
      [alias] [nvarchar](255) NULL,
 CONSTRAINT [pk_sur_item_answer] PRIMARY KEY CLUSTERED
(
      [answer_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


I can manually give a value for answer_id. But since there are already close to 20 000 answer_ids, I would like to find a method that does not rely on me finding the proper id, and since this number changes continously, old code would never work.

I have tried to submit values using different methods.

1) I have left the PK column empty, hoping for an automatic increment, but the server does not accept that, and I get following error message:

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

Is this related to answer_id not being an IDENTITY?

2) I can find the proper value for the next answer_id by
SELECT MAX(answer_id)+1 FROM [dbo].[sur_item_answer]
I have tried to use it as a subquery

INSERT [dbo].[sur_item_answer] (answer_id, item_id, answer_text, answer_value, order_number, random_order_number, default_yn, column_type_id)
  VALUES ((SELECT MAX(answer_id)+1 FROM [dbo].sur_item_answer]),1977,'Kjønnsuttrykk',14,12,0,'N',0)

but then I do do get following error message:
Msg 1046, Level 15, State 1, Line 2
Subqueries are not allowed in this context. Only scalar expressions are allowed.

Is there a other way to write the query so that it does insert the proper new value to answer_id column?

As you can see, I am not an expert, and I rely on you to enlighten me, if I am on the wrong track.

Sincerely yours,
tangofil
0
Comment
Question by:tangofil
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 11

Accepted Solution

by:
Angelp1ay earned 500 total points
ID: 39723717
> Is this related to answer_id not being an IDENTITY?
Yes. IDENTITY is a method to define how a column can be filled automatically.

You can swap your INSERT VALUES for an INSERT SELECT:
INSERT [dbo].[sur_item_answer] (answer_id, item_id, answer_text, answer_value, order_number, random_order_number, default_yn, column_type_id)
SELECT
    (SELECT MAX(answer_id) + 1 FROM [dbo].sur_item_answer),
    1977,
    'Kjønnsuttrykk',
    14,
    12,
    0,
    'N',
    0

Open in new window


Note: This subquery approach may not be very safe wrt concurrency.
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39723760
I think you would be better off adding an identity column.

This is the documentation for the SET IDENTITY_INSERT call (allowing you to insert specific keys into an identity column):
- http://technet.microsoft.com/en-us/library/ms188059.aspx

TEST BEFORE YOU USE ON YOUR PRODUCTION DATA!

Column Swap
I would think you can get away with adding a new column with identity and copying the existing ids across:
- Rename your existing column
- Add a new column 'answer_id' with identity set
- Set identity insert on
- Insert your existing values from old col to new
- Set identity insert off
- Delete the old column

Table Swap
...if that doesn't work or if you prefer you can rename your table, create a new table that's identical except for the extra identity spec and then pull the data across:
- Rename table
- Create new table with old name
- Set identity insert on
- INSERT INTO tname2 FROM tname1
- Set identity insert off
- Delete the old table
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39723776
>1) I have left the PK column empty, hoping for an automatic increment,
In that case, you need to define the column as identity.

Replace this
[answer_id] [int] NOT NULL,

Open in new window

with this
[answer_id] [int] identity(1,1) NOT NULL,

Open in new window

and then do not insert a value into answer_id, as it will auto-incriment on its own when new rows are inserted.

Also, the below unique constraint means if there is already a row with answer_id = 5, and an attempt is made to insert a row with the same answer_id = 5, it will fail.
 CONSTRAINT [pk_sur_item_answer] PRIMARY KEY CLUSTERED ( [answer_id] ASC)

Open in new window

0
 

Author Closing Comment

by:tangofil
ID: 39723810
Concurrency is luckily not a problem, because I am the only one adding items to this table.
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39723814
Glad I could help :)
Have a nice day!
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql select total by week ending 3 45
T-SQL Query - Group By Year 3 55
SQL- GROUP BY 4 53
When to use a Unique Index? A Clustered Index? 5 76
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question