?
Solved

identity column: set identity_insert  on

Posted on 2003-03-28
10
Medium Priority
?
1,554 Views
Last Modified: 2012-05-04
Hello,

I have a table with a column is set as identity. now the application(c++ program, use  ODBC) will insert vaules into the column, so I enable the identity_insert.

However, I found that the SQL server does not check the value inserted into that column, even the vaule exsits, this is a violation to the identity property. any way to prevent this? i.e. let SQL server check if the value exsits, if exsits, simply insert another value decided by the identity.  Is it possible?


Thanks,


Gossip
0
Comment
Question by:Gossip
[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
  • 5
  • 4
10 Comments
 
LVL 13

Expert Comment

by:ispaleny
ID: 8224835
Add a primary key constraint.
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8224840
alter table YourTable add primary key (YourIdentCol)
0
 

Author Comment

by:Gossip
ID: 8224919
I have set it as primary key, and I aslo enable identity insert.  but if you insert an exsit value into the identity column, it reports: key violation.

I want SQL Sever to check if the value I inserted exsits. if exsits, the sqL server simply ignore the value I inserted, but use its own identity value.

Is it possible?

Thanks,

Gossip
 
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 13

Expert Comment

by:ispaleny
ID: 8224953
Automatically or for one manual insert only?
0
 

Author Comment

by:Gossip
ID: 8225060
Hi ispaleny,

I want to manully insert a value into an identity column. but I want to SQL server to check this value: if it is exsiting in the column. if it DOES exsit, then I want SQL server automatically determine a value for me.

Is this possible for SQL server?

I was told informix has a data type serial can do this.

Thanks,

Gossip
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8225452
You must create a "instead of insert" trigger.
0
 

Author Comment

by:Gossip
ID: 8225724
thanks, ispaleny. but how do I write a trigger and but it in the database? I am new to SQL server.

Thanks,

Gossip
0
 

Author Comment

by:Gossip
ID: 8225907
thanks, ispaleny. but how do I write a trigger and but it in the database? I am new to SQL server.

Thanks,

Gossip
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8225983
See you on Monday. Have a nice weekend.
0
 
LVL 1

Accepted Solution

by:
JWT earned 60 total points
ID: 8230792
In order to do what you need you are going to have to turn insert_identity off.  This switch automatically increments the number in the insert column whenever you do an insert.  It is there so you don't have to write any extra code to keep referential integrity when you do inserts.

Having said that, I will show you a way to jury rig via triggers your table to do what you want.  But, I don't think this is really what you want to do.  I may be worng, but bear with me here.

It sounds like your PK on this table has some real world meaning.  It is better in to long run to just let the PK be just a PK, and nothing else.  Take the columns with real world meaning, and give it a UNIQUE constraint.

Then use your application to test to see if the row you want to change is new.  If it is do an insert to the table.  If not then do an update.

Your SELECT Statement will look something like this (I don't do C++ ADO very well):

NewPK = Conn.Exec "SELECT NewPrimaryKey FROM NowNormalizedTable WHERE OnceWasAPrimaryKey = " & ValueIWantToFind

then get up a branching structure:

If ISNull ( NewPK ) Then {
    'Do an Insert
Else
    'do an update where NewPrimaryKey = NewPK
}

If this is not the case, then I don't know why you would ever get a non unique key on an insert, but here is how you get around it.  Remember that to do this you have to turn off identity_insert.  I would make this field have a unique constraint insted.

In QA, open up the database, and run something like this

CREATE TRIGGER INSTEAD_OF_IDENTITY_INSERT
ON tableInQuestion
INSTEAD OF insert
AS

IF UPDATE (PrimaryKey) BEGIN
  IF PrimaryKey IS IN (SELECT PrimaryKey FROM tableInQuestion)
    PRINT 'Primary key exists already - Invalid insert'
    ROLLBACK TRANSACTION
  END  
END

Good Luck
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

764 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