IF NOT EXISTS

In the previous section, we looked at creating tables. The implicit assumption we made that the tables we are creating do not already exist in the database.

You will likely be working with large databases with multiple tables in one database. It is a good practice to use IF NOT EXISTS whenever you are creating tables in order to not overwrite on an existing table with a similar name you are choosing to create.

Using IF NOT EXISTS will only create the table is the name of the table does not exist on the specific database. The syntax for using IF NOT EXISTS is given in an example that follows. For this example, let us use the same tables we created in the previous section.

CREATE TABLE IF NOT EXISTS products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(8,2) NOT NULL,
    cost DECIMAL(8,2),
    stock_quantity INT DEFAULT 0,
    supplier_id INT,
    launch_date DATE,
    weight_kg DECIMAL(5,2),
    is_discontinued BOOLEAN DEFAULT FALSE,
    rating DECIMAL(2,1)
);

Attempting to create a table like that already exists will return a warning like the one below:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(8,2) NOT NULL,
    cost DECIMAL(8,2),
    stock_quantity INT DEFAULT 0,
    supplier_id INT,
    launch_date DATE,
    weight_kg DECIMAL(5,2),
    is_discontinued BOOLEAN DEFAULT FALSE,
    rating DECIMAL(2,1)
);

NOTICE:  relation "products" already exists, skipping
CREATE TABLE

Some database solutions may return a different result output or simply not create the table if it exists. It is a very useful condition and one that you should always use to avoid overwriting your or other peoples tables.