• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1585
  • Last Modified:

identity column: set identity_insert on

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
Gossip
Asked:
Gossip
  • 5
  • 4
1 Solution
 
ispalenyCommented:
Add a primary key constraint.
0
 
ispalenyCommented:
alter table YourTable add primary key (YourIdentCol)
0
 
GossipAuthor Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ispalenyCommented:
Automatically or for one manual insert only?
0
 
GossipAuthor Commented:
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
 
ispalenyCommented:
You must create a "instead of insert" trigger.
0
 
GossipAuthor Commented:
thanks, ispaleny. but how do I write a trigger and but it in the database? I am new to SQL server.

Thanks,

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

Thanks,

Gossip
0
 
ispalenyCommented:
See you on Monday. Have a nice weekend.
0
 
JWTCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now