How to avoid duplicate values in a table ?

[LuisGarciaMartinez]

Hello,

For a table like :

CREATE TABLE player(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE
);

How to avoid duplicate "name" value with INSERT :

INSERT INTO player(name)
    SELECT :player_name WHERE :player_name IS NOT NULL;

Luis

[lovasoa]

Hello! What is the desired behavior when someone tries to insert a duplicate name?

With the "unique" constraint you already have, you should already be seeing an error message when trying to insert a duplicate.

If you want, you can use a select statement with the redirect component and a where clause to redirect the user before the insert statement on duplicates

[lovasoa]

@LuisGarciaMartinez If you need help with anything else or need advice, don't hesitate to ask here :)

[LuisGarciaMartinez]

Hello,
I find this solution :

SELECT
    'form' as component,
    'New player' as title,
    'Insert' as validate;

SELECT 'Player name' as label, 'player_name' as name;

SELECT 'alert' as 'component',
    'Warning' as title,
    'The player ''' || :player_name || ''' already exist' as description
    WHERE :player_name IS NOT NULL
    AND EXISTS (SELECT 1 FROM player WHERE name = :player_name);

INSERT INTO player(name)
    SELECT :player_name 
        WHERE :player_name IS NOT NULL
        AND NOT EXISTS (SELECT 1 FROM player WHERE name = :player_name);

SELECT 'list' as component;
SELECT 
    name as title,
    'group.sql?id!'|| id as link
    FROM player;

[lovasoa]

That looks good. I think you could even simplify it to

SELECT
    'form' as component,
    'New player' as title,
    'Insert' as validate;

SELECT 'Player name' AS name;

SELECT 'alert' as 'component',
    'Warning' as title,
    'The player ''' || name || ''' already exist' as description
FROM player WHERE name = :"Player name";

INSERT OR IGNORE INTO player(name) VALUES (:"Player name");

SELECT 'list' as component;
SELECT 
    name as title,
    'group.sql?id=' ||  id as link
FROM player;

To avoid a page reload to trigger the alert message, you could split this page into two:

• one with the form and the list
• one with the insert and the error message, that uses the redirect component to redirect to the first one after a successful insertion