One of the most common operations in SQL is inserting the data into a database. However, when inserting the data, ensuring that there are no duplicate entries is essential.
This tutorial discusses how to use the SQL to insert the data into a table if the primary key doesn’t exist.
SQL Primary Key
Before we proceed, let us first understand the concept of primary keys.
In SQL, a primary key refers to a column or a set of columns that uniquely identifies each row in a table. The role of a primary key column or columns is to enforce the data integrity and ensure that no duplicate records are added to the table.
To ensure that SQL does not return an error during insertion, it is good to ensure that the value of the primary key column that you insert does not already exist in the table.
SQL Insert If the Key Doesn’t Exist
In SQL, to insert a data into a table if the primary key’s value does not exist, we can use the INSERT INTO statement in conjunction with the ON CONFLICT clause.
The ON CONFLICT clause allows us to specify the action that the SQL engine takes if a duplicate primary key already exists in the table.
We can express the syntax of the clause as shown in the following:
VALUES (value1, value2, value3, ...)
ON CONFLICT (column_name) DO NOTHING;
Let us break down the given syntax:
- table_name – It specifies the target table on which we wish to insert the data.
- column_1, column_2, … – This specifies the table column on which we wish to add the data.
- value1, value2 – It specifies the values that we wish to insert into the table.
- ON CONFLICT (column_name) – It specifies the action that is taken when a conflict occurs in the specified column. In this case, we need to specify the primary key column.
- DO NOTHING – This tells the SQL engine to do nothing if a conflict occurs in the primary key column.
Example Demonstration
To best illustrate how to work with this clause, let us create a table with primary key column as shown in the following:
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER
);
Once we create the table, we can insert the sample data that specifies the value of the primary key column as follows:
VALUES (1, 'John', 20)
ON CONFLICT (id) DO NOTHING;
In the given example, we use the INSERT INTO statement to add the data to the table. We specify the target columns as ID, name, age, and corresponding values.
We also use the ON CONFLICT clause to specify that if a conflict occurs on the ID column, SQL should do nothing.
SQL does not insert a new row if the ID value already exists in the students table. However, SQL inserts a new row with the specified values if the ID value does not exist.
Conclusion
You learned how to use the INSERT INTO statement with the ON CONFLICT clause to ensure that there are no duplicate entries in a table.
