CREATE TABLE

CREATE TABLE statement is used to create a new table in a schema. Creating a table involves naming the table and defining its columns and each column's data type.
To create a table you specify one or more of the following attributes:

The name of the table. A table name cannot start with sys_ . An optional clause of IF NOT EXISTS can be used to create a new table if the table does not exist. If table already exists then create table statement is ignored.
The schema to which table belongs. It may be the default sdbn schema or database, temp database or any attached database.
The name of each column, its data type, and a column constraint. The currently supported constraints are PRIMARY KEY, UNIQUE, NOT NULL, CHECK constraint.
The primary key of the table is not required. A primary key is a column or a group of columns that uniquely identifies a row in the table. In case the primary key consists of multiple columns, you need to use table constraint instead of PRIMARY KEY column constraint.

Syntax

Following is the basic syntax of CREATE TABLE statement.

CREATE TABLE SchemaName.TableName(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   ..... columnN datatype
   ..... Keys (if any)
   ..... Constraints (if any)
);

Example

The following is an example which creates a Products table with Id as the primary key and NOT NULL are the constraints showing that these fields cannot be NULL while creating records in this table, which means you must provide value when inserting new record. The table uses column constraint of Primary Key

CREATE TABLE Products(
   Id 		  INTEGER PRIMARY KEY     NOT NULL,
   Name    TEXT    NOT NULL,
   Price      REAL     NOT NULL
);

Key

There are Primary key, foreign keys, including compound key and composite key, unique key which can be defined for each table.

CREATE TABLE ContactPage (
	Id INTEGER,
	PageId INTEGER,
	GroupId INTEGER,
	Message TEXT,
	PRIMARY KEY (Id, PageId),
	FOREIGN KEY (PageId) REFERENCES Pages (PageId) 
	ON DELETE CASCADE ON UPDATE NO ACTION,
	FOREIGN KEY ([ GroupId ]) REFERENCES PageGroups (GroupId) 
	ON DELETE CASCADE ON UPDATE NO ACTION
);

The above example uses compound primary key or table level primary key constraint, where instead of one column a group of columns define primary key and these columns are used to identify unique rows.

Self Foreign Key Example

CREATE TABLE IF NOT EXISTS Departments
(
 DepartmentId Integer Primary Key AutoIncrement
,DepartmentName TEXT
);

CREATE TABLE IF NOT EXISTS EmployeeMaster
(
EmployeeId Integer Primary Key AutoIncrement,
FirstName Text,
LastName Text,
DepartmentId Integer,
ReportsTo Integer,
FOREIGN KEY (DepartmentId) REFERENCES Departments (DepartmentId),
FOREIGN KEY (ReportsTo) REFERENCES EmployeeMaster(EmployeeId)
);

Above code not only allows to create foreign key against another table but also creates self foreign key on ReportsTo which refers to EmployeeId column in the same table. These type of foreign keys are also known as self-referencing or recursive foreign keys. To View all the foreign keys use the following system command.

SYSCMD Foreign_Key_List('EmployeeMaster');

On Conflict Clause

ON CONFLICT [ROLLBACK or ABORT or FAIL or IGNORE or REPLACE]
The way it works is that when INSERT and UPDATE commands are execute the ON CONFLICT are replaced by OR like SQL Syntax. It is similar to INSERT ON CONFLICT IGNORE which is executed as INSERT OR IGNORE. The ON CONFLICT clause should be used when creating the table. There are five conflict resolution algorithms: ROLLBACK, ABORT, FAIL, IGNORE, and REPLACE. The default conflict resolution algorithm is ABORT.

ABORT
On constraint violation ABORT SQL statement and rolls back any changes made by the current SQL statement, but changes made by prior SQL statements within the same transaction are preserved and the transaction remains active. This is the default behavior.

ROLLBACK
On constraint violation ROLLBACK will abort the SQL statement with constraint violation and rolls back the active transaction. If there are no active transactions (BEGIN TRANSACTION) and only implied transaction is active then the ROLLBACK will work same as the ABORT.

FAIL
On constraint violation FAIL resolution will abort only the current SQL statement. FAIL resolution does not roll back prior changes nor does it end the transaction. If an UPDATE statement encountered a violation on the 2nd row that it attempts to update, then the first row changes are still valid but changes to row 2 and on wards does no happen.

IGNORE
The IGNORE constraint violation simply skips the one row that contains the constraint violation and continues processing subsequent rows of the SQL statement. Other rows before and after the row that contained the constraint violation are inserted or updated normally and NO error is returned when the IGNORE conflict violation occurs.

REPLACE
On UNIQUE or PRIMARY KEY constraint violation REPLACE algorithm deletes existing row, before inserting or updating the row and the statements executes normally. If a NOT NULL constraint violation occurs, the REPLACE conflict resolution replaces the NULL value with the default value for that column, when the column has no default value, then it works the same as ABORT. On any other CHECK constraint violations REPLACE works again like ABORT. When the REPLACE conflict resolution strategy deletes rows in order to satisfy a constraint, delete triggers executes only if recursive triggers are enabled.

C# Example Code

The code is written in c# Console Application

using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("SchemaName=db;uri=@memory;"))
{
	cnn.DatabaseFileMode = DatabaseFileMode.OpenOrCreate;
	cnn.DatabaseMode = DatabaseMode.ReadWrite;
	cnn.Open();
	Console.WriteLine(cnn.State);

	using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
	{
		try
		{
			// Default is ON CONFLICT ABORT when non is specified.
			// Database error can be caught after the second insert statement 
			cmd.CommandText = "CREATE TABLE IF NOT EXISTS testtable0(KeyId Text PRIMARY KEY, TextValue Text);";
			cmd.ExecuteNonQuery();
			
			cmd.CommandText = "INSERT INTO testtable0 VALUES('A1', 'Hello1');";
			cmd.ExecuteNonQuery();
			if (!cmd.GetLastError().Equals("SQLDatabase_OK"))
				Console.WriteLine(cmd.GetLastError());

			// Following should produce an error since duplicate A1 in not allowed.
			cmd.CommandText = "INSERT INTO testtable0 VALUES('A1', 'Hello2');";
			cmd.ExecuteNonQuery();
			if (!cmd.GetLastError().Equals("SQLDatabase_OK"))
				Console.WriteLine(cmd.GetLastError());

			cmd.CommandText = "SELECT COUNT(*) FROM testtable0";
			Console.WriteLine(cmd.ExecuteScalar());

		}
		catch (SqlDatabaseException ex)
		{
			Console.WriteLine("ex.Message testtable0 : " + ex.Message);
		}

		try {
			// Specifically defining ON CONFLICT clause
			// Syntax 1 : "CREATE TABLE IF NOT EXISTS testtable1(KeyId Text , TextValue Text , PRIMARY KEY(KeyId) ON CONFLICT ABORT);"
			// Syntax 2 : "CREATE TABLE IF NOT EXISTS testtable1(KeyId Text PRIMARY KEY ON CONFLICT ABORT, TextValue Text);"
			// Syntax 3 : Produce ABORT on duplicate and IGNORE when KeyId is NULL 
			// CREATE TABLE IF NOT EXISTS testtable1(KeyId Text PRIMARY KEY ON CONFLICT ABORT NOT NULL ON CONFLICT IGNORE, TextValue Text);
			cmd.CommandText = "CREATE TABLE IF NOT EXISTS testtable1(KeyId Text PRIMARY KEY ON CONFLICT ABORT, TextValue Text);";
			cmd.ExecuteNonQuery();

			cmd.CommandText = "INSERT INTO testtable1 VALUES('A1', 'Hello1');";
			cmd.ExecuteNonQuery();
			if (!cmd.GetLastError().Equals("SQLDatabase_OK"))
				Console.WriteLine(cmd.GetLastError());

			cmd.CommandText = "INSERT INTO testtable1 VALUES('A1', 'Hello2');";
			cmd.ExecuteNonQuery();
			if (!cmd.GetLastError().Equals("SQLDatabase_OK")) //Error
				Console.WriteLine(cmd.GetLastError());

			cmd.CommandText = "SELECT COUNT(*) FROM testtable1";
			Console.WriteLine(cmd.ExecuteScalar());

		} catch(SqlDatabaseException ex)
		{
			Console.WriteLine("ex.Message testtable1 : " + ex.Message);
		}

		try
		{
			//NO Error will be in cmd.GetLastError() as no error will be raised due to IGNORE in ON CONFLICT IGNORE
			//PRIMARY KEY(IntKeyId) ON CONFLICT ABORT
			//PRIMARY KEY(IntKeyId) ON CONFLICT FAIL
			//PRIMARY KEY(IntKeyId) ON CONFLICT IGNORE
			//PRIMARY KEY(IntKeyId) ON CONFLICT ROLLBACK
			cmd.CommandText = "CREATE TABLE IF NOT EXISTS testtable2 (IntKeyId Integer , TextValue Text, PRIMARY KEY(IntKeyId) ON CONFLICT IGNORE);";
			cmd.ExecuteNonQuery();

			cmd.CommandText = "INSERT INTO testtable2 VALUES(1, 'Hello1');";
			cmd.ExecuteNonQuery();
			if (!cmd.GetLastError().Equals("SQLDatabase_OK"))
				Console.WriteLine(cmd.GetLastError());

			cmd.CommandText = "INSERT INTO testtable2 VALUES(1, 'Hello2');";
			cmd.ExecuteNonQuery();
			if (!cmd.GetLastError().Equals("SQLDatabase_OK"))
				Console.WriteLine(cmd.GetLastError());

			cmd.CommandText = "SELECT COUNT(*) FROM testtable2";
			Console.WriteLine(cmd.ExecuteScalar());
			

		}
		catch (SqlDatabaseException ex)
		{
			Console.WriteLine("ex.Message testtable2 : " + ex.Message);
		}

		try
		{
			// PRIMARY KEY(IntKeyId) ON CONFLICT REPLACE
			// Replaces row like update statement
			cmd.CommandText = "CREATE TABLE IF NOT EXISTS testtable3 (IntKeyId Integer , TextValue Text, PRIMARY KEY(IntKeyId) ON CONFLICT REPLACE);";
			cmd.ExecuteNonQuery();

			cmd.CommandText = "INSERT INTO testtable3 VALUES(1, 'Hello1');";
			cmd.ExecuteNonQuery();
			if (!cmd.GetLastError().Equals("SQLDatabase_OK"))
				Console.WriteLine(cmd.GetLastError());

			cmd.CommandText = "INSERT INTO testtable3 VALUES(1, 'Hello2');";
			cmd.ExecuteNonQuery();
			if (!cmd.GetLastError().Equals("SQLDatabase_OK"))
				Console.WriteLine(cmd.GetLastError());

			cmd.CommandText = "SELECT COUNT(*) FROM testtable3";
			Console.WriteLine(cmd.ExecuteScalar());

			//Returns Hello2 due to REPLACE
			cmd.CommandText = "SELECT TextValue FROM testtable3 WHERE IntKeyId = 1";
			Console.WriteLine(cmd.ExecuteScalar());

		}
		catch (SqlDatabaseException ex)
		{
			Console.WriteLine("ex.Message testtable3 : " + ex.Message);
		}
	}
}