--- In firebird-support@yahoogroups.com, "Stefan Renzewitz" wrote:

> --- In firebird-support@yahoogroups.com, "Adam" wrote:
> > You seem a little confused about the role of the queries etc.
> > Firebird will raise an exception if your primary key is not
> > unique. In other words, you must be supplying a idEmployeur that
> > is already in the table (a duplicate), and that is against the
> > whole concept of a primary key. Your application should catch the
> > exception and react accordingly. It would be very confusing logic
> > to have the database quietly eat any duplicate data, you would not
> > then know where your record went. Instead, you now have a
> > mechanism where the database informs you that the query operation
> > failed, and why it failed. Use it to your advantage.
> >
> > In fact, the way you would do input validation in the before
> > insert trigger would be to raise a custom exception to prevent the
> > insert from succeeding.
>
> Interesting topic as I'm in a similiar situation. I'm importing
> chess games into my database, but I want to insert only new and
> unique positions / moves.
>
> I have been looking for the most efficient way to achieve this. At
> the moment I'm using ExecuteScalar() for each position to see if it
> already exists and only if not I will add it. However, just this
> check takes most of the time of my import - almost independly
> whether the database is empty or has already 10.000 entries.
>
> I too was wondering whether I should make this exist test at all. My
> train of thought: The database is doing exactly the same test before
> it inserts the row anyway so I'm doing redundant work and thus waste
> time. However, I disliked the idea to always try to add a new entry
> and catch the error if it already exists. Usually raised errors are
> quite time consuming and in general I believed so far this is a bad
> programming habit. I will give this a try though.
>
> Stefan

Hi Stefan!

I haven't got a clue how you store things, but as far as I know any
field can consist of one out of 13 values (either it is empty or it is
populated by one out of the six kinds of black and white officers (or
whatever it is called in English, I've never played chess with an
Englishspeaking person). 13 isn't a very selective number, so I'd
rather multiply things and store them line A..H separately. 13 to the
power of 8 should be 815730721 and then the inserts would be simply:

INSERT INTO ChessMove(OldA, OldB ... OldH, NewA ... NewH)
VALUES ...
WHERE NOT EXISTS(Select * FROM ChessMove
WHERE OldA = ... AND NewH = ...)

With 16 indexes this should be a pretty quick operation,
Set