Solved

Cannot INSERT VALUES into a Primary Key column, without IDENTITY

Posted on 2013-12-17
5
1,360 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 66

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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

630 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