MariaDB Insert If Not Exists [Complete tutorial with 7 Examples]

by
MariaDB Insert If Not Exists Else Update

In this MariaDB tutorial, we will discuss how to use the MariaDB Insert If Not Exists condition and look at several examples related to it. There are lists of the topic that comes under discussion:

  • MariaDB Insert If Not Exists
  • MariaDB Insert If Not Exists Else Update
  • MariaDB Insert Into If Not Exists
  • MariaDB Add Column If Not Exists
  • MariaDB Add Constraint If Not Exists
  • MariaDB Add Index If Not Exists
  • MariaDB Add Unique Constraints If Not Exists
  • MariaDB Add Unique Index If Not Exists

MariaDB Insert If Not Exists

Here we will understand how to insert rows in the table if it does not exists in MariaDB.

It is because when we have a column of the PRIMARY KEY or UNIQUE KEY constraint then it will throw an error each time when we will insert a new row with duplicate values for these columns.

If we want to do still insertion in the table when the data does not exist then we need to create a work-around solution with the statements provided by MySQL. There are three ways we can use to “insert if not exist” in MySQL:

  • Using the INSERT IGNORE statement
  • On the DUPLICATE KEY UPDATE clause
  • Using the REPLACE statement

The MariaDB INSERT IGNORE command is typically used to turn an error that MySQL throws when you use a regular INSERT statement into a warning, allowing your query to continue running undisturbed.

First, let’s have a look at the STATES_OF_USA table by the following query:

SELECT * FROM STATES_OF_USA;

The MariaDB SELECT statement retrieves all records from the STATES_OF_USA table.

MariaDB insert if not exists example
MariaDB SELECT statement for STATES_OF_USA

Here is an example of the INSERT IGNORE statement to insert a row if not exist in the query with the help of the following query:

EXAMPLE:

INSERT IGNORE INTO STATES_OF_USA(STATE_ID,STATE_NAME,STATE_SHORTFORM,STATE_POPULATION)
VALUES(2,'ALASKA','AK','MEDIUM');

In this query, with the help of the INSERT IGNORE statement, MariaDB will insert a row if the values do not exist in the table. And it will produce a warning of the PRIMARY KEY constraint for duplicate key values.

Read: MariaDB Between 

MariaDB Insert If Not Exists Else Update

In this section, we will understand how to insert it if not exists, for that we will use the ON DUPLICATE KEY UPDATE clause in MariaDB and which is explained with the help of an illustrated example.

The MariaDB ON DUPLICATE KEY UPDATE clause is used to update the rows with new values when a duplicate value is found in the UNIQUE KEY or PRIMARY KEY constraint.

Here is an illustrated example of the ON DUPLICATE KEY UPDATE clause which will be used at the end of a query for the INSERT clause as shown below:

EXAMPLE:

INSERT IGNORE INTO STATES_OF_USA(STATE_ID,STATE_NAME,STATE_SHORTFORM,STATE_POPULATION)
VALUES(2,'ALASKA','AK','MEDIUM')
ON DUPLICATE KEY UPDATE 
STATE_ID=2,
STATE_NAME='ALASKA',
STATE_SHORTFORM='AK',
STATE_POPULATION='MEDIUM';

First, it will execute the regular insert query above in the MariaDB but when duplicate values are found then MariaDB will perform an update instead of insertion by using the ON DUPLICATE KEY UPDATE clause.

Read: MariaDB Median

MariaDB Insert Into If Not Exists

Over here we will understand how to insert into if no exists in the MariaDB query. And it will be explained with the help of an illustrated example.

The MariaDB INSERT INTO EXISTS explains that if a subquery returns any rows at all then the NOT EXISTS subquery is FALSE. But if the subquery in the NOT EXISTS clause is TRUE then it will not return any row at all. Here is the syntax which is given below:

SYNTAX:

INSERT INTO TABLE_NAME(COLUMN_1,COLUMN_2,COLUMN_N)
SELECT * FROM (SELECT VALUE_1,VALUE_2,VALUE_N) AS TEMP_NAME
WHERE NOT EXISTS [CONDITION_SUBQUERY];

The syntax explanation:

  • TABLE_NAME: It is the name of the table from which we want to insert a new record.
  • COLUMN_1, COLUMN_2, COLUMN_N: It is a list of your columns in your table_name.
  • CONDITION_SUBQUERY: It’s a sub-query that includes a SELECT statement to find the row that meets a specific condition.

Let’s have a look at the illustrated example of the insert if not exist by the following query:

EXAMPLE:

INSERT INTO STATES_OF_USA (STATE_ID,STATE_NAME,STATE_SHORTFORM,STATE_POPULATION)
SELECT * FROM (SELECT 3 AS STATE_ID, 'ARIZONA' AS STATE_NAME,'AZ' AS STATE_SHORTFORM,'LOW' AS STATE_POPULATION) AS temp
WHERE NOT EXISTS (
SELECT STATE_NAME FROM STATES_OF_USA WHERE STATE_NAME = 'ARIZONA'
) 
LIMIT 1;

SELECT * FROM STATES_OF_USA;

We are picking the record from table STATES_OF_USA in the outer query with the NOT EXISTS clause. FALSE will be produced if the row doesn’t really exist in the table. The statement will INSERT the row because there is a ‘NOT‘ keyword before the EXISTS keyword.

Read: MariaDB Not Between

MariaDB Add Column If Not Exists

Here we will understand and learn how to add the column with the MariaDB IF EXISTS clause in the query and which is explained with the help of an illustrated example.

In MariaDB, the ALTER TABLE statement is used to add, drop/ delete, modify and rename the column_name in the table. And the IF NOT EXISTS clause is only used when we need to add or modify or insert records into the table.

Here is the syntax of the MariaDB add column if not exist by the following query:

SYNTAX:

ALTER TABLE YOUR_TABLE_NAME IF NOT EXISTS COLUMN_NAME DATA TYPE;

Here is the illustrated example of the MariaDB ALTER TABLE statement to add a column in the query with the IF EXISTS clause which is shown below:

EXAMPLE:

ALTER TABLE STATES_OF_USA ADD IF NOT EXISTS USA_STATE_NAME VARCHAR(30);

In this query, we use the ALTER TABLE statement to add a new column_name called USA_STATE_NAME with data type VARCHAR(30) for the column_name in the STATES_OF_USA table.

Read: How to load files into MariaDB

MariaDB Add Constraint If Not Exists

Here we’ll understand how to add a constraint if not exists by using the MariaDB ALTER TABLE statement and which is explained with the help of an illustrated example.

In MariaDB, the CONSTRAINTS provides restriction on the data of the table. if the statement ignores the CONSTRAINTS work then it will throw an error. There are four types of constraints in the table: PRIMARY KEY, FOREIGN KEY, UNIQUE KEY, and CHECK.

Here is the definition of the CONSTRAINTS in detail below:

  • PRIMARY KEY: It sets the column for referencing rows. And the value should not be null and unique.
  • FOREIGN KEY: It sets columns as a reference of PRIMARY KEY to another table.
  • UNIQUE: It requires a value in the column and should occur once in the table.
  • CHECK: It should be checked whether the data meets the given condition or not.

Here is the illustrated example to add constraints if not exist in the table by using the ALTER TABLE statement in the following query:

EXAMPLE:

ALTER TABLE EMPLOYEE ADD CONSTRAINT EMPLOYEE_LASTNAME UNIQUE IF NOT EXISTS (LAST_NAME);

In this section, we have created the constraint as UNIQUE KEY on the STATE_NAME column of the STATES_OF_USA table by using the ALTER TABLE statement. If the constraints don’t exist still we will use the IF NOT EXISTS clause for adding the UNIQUE KEY constraint for the STATE_NAME table by using the ALTER TABLE statement.

We also need to provide a constraint_name as EMPLOYEE_LASTNAME for the UNIQUE KEY of the LAST_NAME column in the EMPLOYEE table by using the ALTER TABLE statement.

Read: What is MariaDB Sharding

MariaDB Add Index If Not Exists

In this section, we will understand how to add an index with the IF NOT EXISTS clause in the query by using the MariaDB CREATE INDEX statement. And it is explained with the help of syntax and an illustrated example.

The MariaDB index is an efficient method for retrieving records faster. For each value that occurs in the indexed columns, an index generates an entry. Here is the syntax of the ADD INDEX IF NOT EXISTS by using the CREATE INDEX statement in the below following query:

SYNTAX:

CREATE INDEX IF NOT EXISTS YOUR_INDEX_NAME 
ON YOUR_TABLE_NAME (COLUMN_NAME);

Let’s have a look at the example by using the CREATE INDEX statement to add an index if not exist in the following query:

EXAMPLE:

CREATE INDEX IF NOT EXISTS NAME_OF_STATES
ON STATES_OF_USA (STATE_NAME);

In the preceding query, we have created an index called NAME_OF_STAES by using the IF NOT EXISTS clause on the STATE_NAME column from the STATES_OF_USA table. So, it will first check if the index_name as NAME_OF_STATES has been created or not. If it is created it will show us a warning.

If we want to check whether the new index has been created or not then we can use the SHOW INDEXES statement for the new index_name of the STATES_OF USA table.

Read: MariaDB Reserved Words

MariaDB Add Unique Constraints If Not Exists

In this sub-topic, we will add the UNIQUE KEY constraint with the IF NOT EXISTS clause in the ALTER TABLE statement and which is explained with the help of an illustrated example.

In MariaDB, the UNIQUE KEY constraint is used to add value to the column and it should occur once in the column/s. Here is an illustrated to add unique constraints if not exist by the following query:

EXAMPLE:

ALTER TABLE EMPLOYEE ADD CONSTRAINT EMPLOYEE_FIRSTNAME IF NOT EXISTS UNIQUE(FIRST_NAME);

In the preceding query, we have created a constraint name called EMPLOYEE_FIRSTNAME on the FIRST_NAME column by using the UNIQUE KEY constraint of the EMPLOYEE table. And it is done by using the ALTER TABLE statement.

Read: MariaDB Date_Format

MariaDB Add Unique Index If Not Exists

Here we will understand how to add a unique index name in the MariaDB if not exists by using the CREATE UNIQUE INDEX statement and which is explained with the help of an illustrated example.

EXAMPLE:

CREATE UNIQUE INDEX IF NOT EXISTS STATES
ON STATES_OF_USA (STATE_POPULATION);

In this query, we have created a unique index called STATES on the STATE_POPULATION column from the STATES_OF_USA table by using the CREATE UNIQUE INDEX statement.

If we want to check if the unique index is created or not, then we need to use the SHOW INDEXES statement.

You may also like to read the following MariaDB tutorials.

We addressed the MariaDB Insert If Not Exists condition in this MariaDB tutorial, as well as several sample instances connected to it. There are lists of the topic that comes under discussion:

  • MariaDB Insert If Not Exists
  • MariaDB Insert If Not Exists Else Update
  • MariaDB Insert Into If Not Exists
  • MariaDB Add Column If Not Exists
  • MariaDB Add Constraint If Not Exists
  • MariaDB Add Index If Not Exists
  • MariaDB Add Unique Constraints If Not Exists
  • MariaDB Add Unique Index If Not Exists