Solved

Cannot INSERT VALUES into a Primary Key column, without IDENTITY

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

920 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

12 Experts available now in Live!

Get 1:1 Help Now