inserting an item into a table if it doesn’t exist SQLite3-CodePudding

I am trying to insert a (hard coded) new user into a database if the username does not already appear. In the code below, I am trying to have sql check if the user already exists and if so select (or do nothing), otherwise, insert the new user into the table. I have tried coding this a number of ways, but no matter what I do, the table doesn’t populate! Any tips or ideas would be super helpful thank you so much. For reference, I am using the sqllite3 extension for the language C, and I am using the sql_exec function to make the query.

Attempt 1:

IF EXISTS ( SELECT * PERSON WHERE USERNAME='name') BEGIN SELECT 1 END ELSE BEGIN INSERT INTO PERSON (USERNAME, PASSWORD, STATUS, SIGNATURE) VALUES('name', 'password', 'ONLINE', '12345') END;

Attempt 2:

IF NOT EXISTS ( INSERT INTO PERSON (USERNAME, PASSWORD, STATUS, SIGNATURE) VALUES('name', 'password', 'ONLINE', '12345') ) END ;

Attempt 3:

WHERE NOT EXISTS ( INSERT INTO PERSON (USERNAME, PASSWORD, STATUS, SIGNATURE) VALUES('name', 'password', 'ONLINE', '12345') )

Attempt 4:

WHERE NOT EXISTS ( INSERT INTO PERSON (USERNAME, PASSWORD, STATUS, SIGNATURE) VALUES('name', 'password', 'ONLINE', '12345') );

Attempt 5:

I F NOT EXISTS ( SELECT 1 WHERE USERNAME='kat') BEGIN INSERT INTO PERSON (USERNAME, PASSWORD, STATUS, SIGNATURE) VALUES('name', 'password', 'ONLINE', '12345') END;

Thank you!!!!!

CodePudding user response：

You simply have to add a primary key on your table for the USERNAME field and then to execute the following request with the ON CONFLICT clause:

INSERT OR IGNORE INTO PERSON (USERNAME, PASSWORD, STATUS, SIGNATURE) 
VALUES('name', 'password', 'ONLINE', '12345')

See: https://sqlite.org/lang_conflict.html

CodePudding user response：

Make Username field as Primary Key or Unique Key so that the username will not be inserted if it is duplicated.

INSERT INTO PERSON (USERNAME, PASSWORD, STATUS, SIGNATURE) 
VALUES('name', 'password', 'ONLINE', '12345')