INSERT IF NOT EXISTS

[serhan_shbeita]

Hi,

What is the best way in MAXDB to insert new row into a table if the row does not exist?

Let's say the table structure:

ID (PK)

FNAME

LNAME

I want for each insert to check if the FANEM and LNAME does not exist in the table, if they already exist do nothing, if not INSERT.

Thanks

[lbreddemann · Accepted solution]

> What is the best way in MAXDB to insert new row into a table if the row does not exist?

>

> Let's say the table structure:

> ID (PK)

> FNAME

> LNAME

>

> I want for each insert to check if the FANEM and LNAME does not exist in the table, if they already exist do nothing, if not INSERT.

Hmm... ok the first step seems to be obvious:

create a unique constraint for the combination of FNAME and LNAME (I assume you meant it that way. Or do you want to have each FNAME unique AND LNAME unique?).

This will guarantee that no double entries can be entered at all.

Now to your "behavioural" requirement:

As I understand it you want to write an INSERT statement in your application code, but you don't want to get a "UNIQUE KEY VIOLATION" error, but instead MaxDB should just don't do anything.

Actually this is a not too clever approach, since your application wouldn't notice now whether the insert had been successful or not.

Based on your current data design, there is no single build-in command in MaxDB that would allow this to be done.

For duplicates on the primary key (ID in your case) you could simple use the INSERT ... IGNORE DUPLICATES clause (check the documentation on this!).

But this does not work for secondary keys.

That leaves three alternatives:

1) (Best one): Handle the duplicate secondary key error in your application.

By that you get full control over all records and know precisely which inserts failed.

2) Change your data design.

There is no need to have a surrogate primary key here.

Just use (FNAME,LNAME) as your primary key and you can use the IGNORE DUPLICATES feature.

Also your primary key suddenly is meaningful.

3) Write a stored procedure that performs the insert and ignores the duplicate secondary key error but raises all other errors up.

regards

Lars

[serhan_shbeita · Accepted solution]

Hi

Thanks for your reply.

How the constraint creation syntax? Can you provide an example for that?

I think that will be enough, since If I tried to insert an existing row, a constraint violatino exception shoul dbe thrown, right!?

Then I will catch this excpetion, eat it and continue to the next insert.

Thanks

Edited by: Serhan Shbeita on Dec 17, 2009 10:28 AM

[lbreddemann · Accepted solution]

> Thanks for your reply.

> How the constraint creation syntax? Can you provide an example for that?

How about reading the documentation yourself?

There are examples and tutorials in it!

> I think that will be enough, since If I tried to insert an existing row, a constraint violatino exception shoul dbe thrown, right!?

> Then I will catch this excpetion, eat it and continue to the next insert.

Ok - short hint: you can as well just create a unique index on the fields (that's how unique constraints work anyhow):

create unique index I_UQ_NAMES on NAMES (FNAME, LNAME)

regards,

Lars