Solved

Cannot INSERT VALUES into a Primary Key column, without IDENTITY

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now