how to return primary key when insert statement violates unique constraint
-
Let me clarify: Let's say I have a table called address with fields: id, street, streetnumber, busnumber, placeid. id is the primary key of that table. Now I insert a row in that table with data in the following fields: street, streetnumber, busnumber, placeid. id is generated by a sequence, but that part doesn't matter. The unique constraint is about those 4 fields. Suppose I want to insert 2 people who live at the same address, but while this is unknown to me. Instead of getting a unique constraint warning, I would like to get the id of the record that matches the data I'm trying to insert. I've thought about doing it in the same stored procedure, but I don't know that much about errorhandling inside a stored procedure. I am trying to avoid to having to handle it in my application. Could you help me out with that?
Nico Haegens wrote:
I want to insert 2 people who live at the same address
Get rid of the unique constraint if it doesn't support the application properly. Why not check the table first, before trying to insert the record?
You'll never get very far if all you do is follow instructions.
-
Nico Haegens wrote:
I want to insert 2 people who live at the same address
Get rid of the unique constraint if it doesn't support the application properly. Why not check the table first, before trying to insert the record?
You'll never get very far if all you do is follow instructions.
My point is to link the two people to the same address, instead of making 2 duplicate addresses. Checking the table every time I make an insert, makes the process slower than if I were to do it after, cause then I only need to check the database twice when the exception occurs instead of twice every time I do the insert.
-
My point is to link the two people to the same address, instead of making 2 duplicate addresses. Checking the table every time I make an insert, makes the process slower than if I were to do it after, cause then I only need to check the database twice when the exception occurs instead of twice every time I do the insert.
We did that at one place I worked. We had a table of addresses and checked for the address each time. It wasn't slow. You could probably make a hash to speed it up.
You'll never get very far if all you do is follow instructions.
-
As title says, I would like to know how to return the primary key(the column is always called id) when I perform an insert statement and the database complains about an unique constraint being violated. All my insert statements are in stored procedures. Would like that same stored procedure to return the value of that primary key column of the row that is being violated. I find that a cleaner solution than responding to an exception in my application by searching the table for the unique values. This would be ok if it were one or a few tables, but not when you got many. PS: using MSSQL 2012
Nico Haegens wrote:
I would like to know how to return the primary key(the column is always called id) ...All my insert statements are in stored procedures
Write a lot of code - duplicating every constraint that you have. In every stored procedure that does an insert. So if you have a unique name constraint in your customer table then your insert for the customer first checks to see that the name is unique. If it isn't then it throws an error. If it is then it proceeds to doing the actual insert. Course you might want to create a table transaction as well since someone could insert a different row with the name as well. Naturally this isn't efficient. Nor performant. Applications should be coded to prevent this in the first place rather than relying on the database to provide error notification (versus error prevention.)
-
Nico Haegens wrote:
I would like to know how to return the primary key(the column is always called id) ...All my insert statements are in stored procedures
Write a lot of code - duplicating every constraint that you have. In every stored procedure that does an insert. So if you have a unique name constraint in your customer table then your insert for the customer first checks to see that the name is unique. If it isn't then it throws an error. If it is then it proceeds to doing the actual insert. Course you might want to create a table transaction as well since someone could insert a different row with the name as well. Naturally this isn't efficient. Nor performant. Applications should be coded to prevent this in the first place rather than relying on the database to provide error notification (versus error prevention.)
Here is one of the situations I have: I insert a person and an address the person provided. Addresses are in a seperate table. So brief table layout: person table: firstname, lastname, addressid address table: id, street, streetnbr, busnbr, placeid. There is a unique constraint on the combination of all 4 fields that aren't the id. Person A comes along to one of my client's employees and gives his info and a certain address. Person B comes along to another one of my client's employees, gives his info and the same address as person A. here is what currently happens in my app: app receives sqlexception with the error saying there was a unique constraint problem and I respond to it in app by doing a sql statement that selects the record that matches the unique address. Here is what I would like to happen: stored procedure for Address tries to insert the address, finds out it already exists(throws exception in db) cause of the unique constraint and instead of supplying the id of the newly inserted record, it supplies the id of the existing record. Imo, this is far faster than what I have now, as the problem is handled before it leaves the stored procedure. Problem is, I know nothing about error handling in SQL Server. Here is my current sql statement for my SP:
ALTER procedure [dbo].[AddressInsert](@street varchar(255), @streetnumber varchar(255), @busnumber varchar(255), @placeid bigint, @newid bigint output) as begin
set @newid = next value for dbo.baseidseq
insert into [Address](baseid, street, streetnumber, busnumber, placeid) values(@newid, @street, @streetnumber, @busnumber, @placeid) endWhat statement(s) do I add to return the id of the row that matches the parameters provided? I assume I have to add something like select * from address where street = @street, etc. But what else?
-
Here is one of the situations I have: I insert a person and an address the person provided. Addresses are in a seperate table. So brief table layout: person table: firstname, lastname, addressid address table: id, street, streetnbr, busnbr, placeid. There is a unique constraint on the combination of all 4 fields that aren't the id. Person A comes along to one of my client's employees and gives his info and a certain address. Person B comes along to another one of my client's employees, gives his info and the same address as person A. here is what currently happens in my app: app receives sqlexception with the error saying there was a unique constraint problem and I respond to it in app by doing a sql statement that selects the record that matches the unique address. Here is what I would like to happen: stored procedure for Address tries to insert the address, finds out it already exists(throws exception in db) cause of the unique constraint and instead of supplying the id of the newly inserted record, it supplies the id of the existing record. Imo, this is far faster than what I have now, as the problem is handled before it leaves the stored procedure. Problem is, I know nothing about error handling in SQL Server. Here is my current sql statement for my SP:
ALTER procedure [dbo].[AddressInsert](@street varchar(255), @streetnumber varchar(255), @busnumber varchar(255), @placeid bigint, @newid bigint output) as begin
set @newid = next value for dbo.baseidseq
insert into [Address](baseid, street, streetnumber, busnumber, placeid) values(@newid, @street, @streetnumber, @busnumber, @placeid) endWhat statement(s) do I add to return the id of the row that matches the parameters provided? I assume I have to add something like select * from address where street = @street, etc. But what else?
Hi Nico Sorry that this is not an answer, but more out of curiosity.. Lets say a man joins the company. He gets an address ID. His wife who lives at the same address joins the company. With your app, you want to assign the same address ID to his wife. They get divorced. The man moves to another address, and tells the company his new address. You update that address ID with the new address and then his wife will have moved back in with him since they share and address ID?
-
Hi Nico Sorry that this is not an answer, but more out of curiosity.. Lets say a man joins the company. He gets an address ID. His wife who lives at the same address joins the company. With your app, you want to assign the same address ID to his wife. They get divorced. The man moves to another address, and tells the company his new address. You update that address ID with the new address and then his wife will have moved back in with him since they share and address ID?
-
Not a VW bug I hope. :)
-
Here is one of the situations I have: I insert a person and an address the person provided. Addresses are in a seperate table. So brief table layout: person table: firstname, lastname, addressid address table: id, street, streetnbr, busnbr, placeid. There is a unique constraint on the combination of all 4 fields that aren't the id. Person A comes along to one of my client's employees and gives his info and a certain address. Person B comes along to another one of my client's employees, gives his info and the same address as person A. here is what currently happens in my app: app receives sqlexception with the error saying there was a unique constraint problem and I respond to it in app by doing a sql statement that selects the record that matches the unique address. Here is what I would like to happen: stored procedure for Address tries to insert the address, finds out it already exists(throws exception in db) cause of the unique constraint and instead of supplying the id of the newly inserted record, it supplies the id of the existing record. Imo, this is far faster than what I have now, as the problem is handled before it leaves the stored procedure. Problem is, I know nothing about error handling in SQL Server. Here is my current sql statement for my SP:
ALTER procedure [dbo].[AddressInsert](@street varchar(255), @streetnumber varchar(255), @busnumber varchar(255), @placeid bigint, @newid bigint output) as begin
set @newid = next value for dbo.baseidseq
insert into [Address](baseid, street, streetnumber, busnumber, placeid) values(@newid, @street, @streetnumber, @busnumber, @placeid) endWhat statement(s) do I add to return the id of the row that matches the parameters provided? I assume I have to add something like select * from address where street = @street, etc. But what else?
Nico Haegens wrote:
Person A comes along to one of my client's employees and gives his info and a certain address.
Person B comes along to another one of my client's employees, gives his info and the same address as person A.
here is what currently happens in my app:What you have described is a hypothetical implementation scenario. However what is the exact business case where this happens? Not what might happen but what does the actual people of this company using the application do?
Nico Haegens wrote:
Problem is,
You don't have a problem until there is a business case. For example given your description a 'customer' could in fact have the very same address as a different 'customer' and your current implementation would prevent you from entering it (regardless of why.) That can happen because a person might be legally incorporated as different companies yet work out of the same office (one person after all.) But as a legal entity the address in not in fact the 'same' despite being the same physical location.
-
As title says, I would like to know how to return the primary key(the column is always called id) when I perform an insert statement and the database complains about an unique constraint being violated. All my insert statements are in stored procedures. Would like that same stored procedure to return the value of that primary key column of the row that is being violated. I find that a cleaner solution than responding to an exception in my application by searching the table for the unique values. This would be ok if it were one or a few tables, but not when you got many. PS: using MSSQL 2012
Welcome to the "address" can of worms, there is another relating to phone numbers and names (marry/divorce) and surname/first name designation (try working in Asia). The technical answer to your problem is that you MUST include a search for existing address in your insert procedure. Defining the business case is going to drive you nuts, toss it back to the business/BA, their job is to tell you what you want. It can be entertaining watching the reaction when you start asking some of the questions you have received here.
Never underestimate the power of human stupidity RAH
-
As title says, I would like to know how to return the primary key(the column is always called id) when I perform an insert statement and the database complains about an unique constraint being violated. All my insert statements are in stored procedures. Would like that same stored procedure to return the value of that primary key column of the row that is being violated. I find that a cleaner solution than responding to an exception in my application by searching the table for the unique values. This would be ok if it were one or a few tables, but not when you got many. PS: using MSSQL 2012
Figured it out myself: use try catch clause and check for error_number()=2627
declare @newid bigint = next value for dbo.baseidseq
BEGIN TRANSACTION
begin try
insert into [Address](id, street, streetnumber, busnumber, placeid)
values(@newid, @street, @streetnumber, @busnumber, @placeid)end try
begin catch
if ERROR_NUMBER()=2627
begin
set @newid = (select baseid from [Address]
where street like @street and streetnumber like @streetnumber and busnumber like @busnumber and placeid=@placeid)
end
else
rollback transaction
END catch
if @@TRANCOUNT>0
COMMIT TRANSACTION
select @newid -
Figured it out myself: use try catch clause and check for error_number()=2627
declare @newid bigint = next value for dbo.baseidseq
BEGIN TRANSACTION
begin try
insert into [Address](id, street, streetnumber, busnumber, placeid)
values(@newid, @street, @streetnumber, @busnumber, @placeid)end try
begin catch
if ERROR_NUMBER()=2627
begin
set @newid = (select baseid from [Address]
where street like @street and streetnumber like @streetnumber and busnumber like @busnumber and placeid=@placeid)
end
else
rollback transaction
END catch
if @@TRANCOUNT>0
COMMIT TRANSACTION
select @newidDo some research into the MERGE keyword, it caters for this requirement (I think as I have not used it). Programming by error - one of my pet peeves, if this turned up in any of our code reviews the dev would be castigated.
Never underestimate the power of human stupidity RAH