Tag: create table

An Introduction to Strict Tables in SQLite

Posted on by Ian

SQLite is widely known for its simplicity, flexibility, and lightweight architecture. One feature that sets it apart from most other SQL databases is its dynamic typing system, which allows columns in a table to store data of any type, regardless of their declared type.

While some developers welcome this departure from the traditional SQL approach, others find it extremely problematic, due to its non-enforcement of data types, which could potentially lead to data integrity issues.

Continue reading →

Fix “Column names in each table must be unique…” in SQL Server (Error 2705)

Posted on by Ian

If you’re getting an error that reads something like “Column names in each table must be unique. Column name ‘c1’ in table ‘t1’ is specified more than once” in SQL Server, it appears that you’re trying to create a table with duplicate column names, or you’re trying to add a column to a table that already has a column of that name.

Column names must be unique within each table.

Continue reading →

CREATE TABLE IF NOT EXISTS Equivalent in SQL Server

Posted on by Ian

In SQL, we can use the CREATE TABLE IF NOT EXISTS statement to create a table only if it doesn’t exist. The benefit of doing this is that we won’t get an error if there’s already a table with the same name.

But SQL Server doesn’t support this syntax – at least not in the current version of SQL Server at the time of writing (SQL Server 2022) .

So with SQL Server, we need to do a bit of extra work.

Continue reading →

Oracle Adds Support for IF EXISTS and IF NOT EXISTS Syntax Modifiers

Posted on by Ian

Many RDBMSs implement IF EXISTS and IF NOT EXISTS syntax modifiers that can be used with DDL object creation, modification, and deletion, such as CREATE TABLE and DROP TABLE statements, to name just a couple.

These syntax modifiers allow us to run such statements without getting an error in the event that the object already exists (if we’re trying to create it or modify it) or doesn’t exist (if we’re trying to drop it).

Continue reading →

3 Ways to Create a Table if it Doesn’t Already Exist in Oracle

Posted on by Ian

The very useful CREATE TABLE IF NOT EXISTS syntax was finally introduced in Oracle Database – Oracle Database 23c to be precise. This syntax allows us to run a CREATE TABLE statement without getting an error if the table already exists.

Earlier versions of Oracle don’t support the IF NOT EXISTS clause, and so if we want to avoid any nasty errors resulting from trying to create a table that already exists, we need to do a bit of extra work.

Continue reading →

4 Ways to Check if a Table Exists Before Dropping it in SQL Server (T-SQL)

Posted on by Ian

Dropping a table in SQL easy. You simply use DROP TABLE myTable where myTable is the name of the table you want to drop. You can use this method to drop a table in SQL Server via T-SQL script.

But you’ll get an error if the table doesn’t actually exist. That is, unless you check for the existence of the table first.

Below are four ways of using T-SQL to check whether the table exists before dropping it.

Continue reading →