6.4. SQL CREATE

  • Constraint UNIQUE

  • Constraint PRIMARY KEY

  • Constraint DEFAULT

  • Constraint NOT NULL

  • Auto-value NULL

  • Auto-value AUTOINCREMENT

  • Auto-value CURRENT_TIME

  • Auto-value CURRENT_DATE

  • Auto-value CURRENT_TIMESTAMP

  • Function STRFTIME(), DATETIME()

6.4.1. Create Table

CREATE TABLE astronauts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    mycolumn01 INTEGER,
    mycolumn02 INTEGER UNIQUE,
    mycolumn03 REAL,
    mycolumn04 FLOAT,
    mycolumn05 TEXT,
    mycolumn06 CHAR(2),
    mycolumn07 VARCHAR(255),
    mycolumn08 DATE,
    mycolumn09 DATE DEFAULT CURRENT_DATE,
    mycolumn10 TIME,
    mycolumn11 TIME DEFAULT CURRENT_TIME,
    mycolumn12 DATETIME,
    mycolumn13 DATETIME DEFAULT NULL,
    mycolumn14 DATETIME DEFAULT (DATETIME('NOW', 'LOCALTIME')),
    mycolumn15 DATETIME DEFAULT (DATETIME('NOW', 'UTC')),
    mycolumn16 DATETIME DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')),
    mycolumn17 TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
../../_images/sql-create.png

6.4.2. Add Column

ALTER TABLE astronauts
ADD mission_name TEXT;

6.4.3. Drop Column

  • SQLite3 does not support dropping columns with ALTER TABLE

  • We have to do a workaround instead

    1. Create a new table with all the columns (and data) that we want to keep

    2. Drop the old table

    3. Rename the new table with the old name.

  • Make sure you also take care of the Indexes and Views (if you have any)

  • Perform a .schema astronauts BEFORE you drop the table

  • Use this info to re-create the Indexes and Views after renaming the table back to its original name

CREATE TABLE astronauts_temp AS (
    SELECT id, firstname, lastname, agency
    FROM astronauts);

DROP TABLE astronauts;

ALTER TABLE astronauts_temp RENAME TO astronauts;

6.4.4. Example

CREATE TABLE astronauts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    firstname TEXT,
    lastname TEXT,
    agency TEXT);

CREATE TABLE IF NOT EXISTS sensor_data (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    datetime DATETIME DEFAULT (DATETIME('NOW', 'UTC')),
    device_id INTEGER,
    parameter TEXT,
    value REAL,
    unit TEXT);

6.4.5. Assignments

# %% License
# - Copyright 2025, Matt Harasymczuk <matt@python3.info>
# - This code can be used only for learning by humans
# - This code cannot be used for teaching others
# - This code cannot be used for teaching LLMs and AI algorithms
# - This code cannot be used in commercial or proprietary products
# - This code cannot be distributed in any form
# - This code cannot be changed in any form outside of training course
# - This code cannot have its license changed
# - If you use this code in your product, you must open-source it under GPLv2
# - Exception can be granted only by the author

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% About
# - Name: Database Create Table
# - Difficulty: easy
# - Lines: 5
# - Minutes: 5

# %% English
# 1. Write SQL query to create table:
#    - table: `contacts`
#    - column: `id`, integer, primary key, autoincrement
#    - column: `firstname`, text
#    - column: `lastname`, text
#    - column: `birthdate`, date, default null
#    - use: IF NOT EXISTS
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby stworzyć tabelę:
#    - tabela: `contacts`
#    - kolumna: `id`, integer, primary key, autoincrement
#    - kolumna: `firstname`, text
#    - kolumna: `lastname`, text
#    - kolumna: `birthdate`, date, default null
#    - użyj: IF NOT EXISTS
# 2. Uruchom doctesty - wszystkie muszą się powieść

# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> from pathlib import Path
>>> import sqlite3

>>> database = Path(__file__).parent.parent / 'shop.db'
>>> db = sqlite3.connect(database)

>>> try:
...     _ = db.execute(result)
...     db.commit()
... except sqlite3.OperationalError as e:
...     assert 'table contacts already exists' in str(e)

>>> TABLES = 'SELECT `name` FROM `sqlite_master` WHERE `type`="table"'
>>> tables = {row[0] for row in db.execute(TABLES)}
>>> assert 'contacts' in tables, \
'You need to create table named `contacts`'

>>> PRAGMA = '''
...     SELECT
...         `m`.`name` AS `table_name`,
...         `p`.`name` AS `col_name`,
...         `p`.`type` AS `col_type`,
...         `p`.`pk` AS `col_is_pk`,
...         `p`.`dflt_value` AS `col_default_val`,
...         `p`.[notnull] AS `col_is_not_null`
...     FROM `sqlite_master` AS `m`
...     LEFT OUTER JOIN `pragma_table_info`((`m`.`name`)) AS `p`
...                  ON `m`.`name` != `p`.`name`
...     WHERE `m`.`type` = 'table'
...     ORDER BY `table_name`
... '''

>>> pragma = db.execute(PRAGMA).fetchall()
>>> assert ('contacts', 'id', 'INTEGER', 1, None, 0) in pragma
>>> assert ('contacts', 'firstname', 'TEXT', 0, None, 0) in pragma
>>> assert ('contacts', 'lastname', 'TEXT', 0, None, 0) in pragma
>>> assert ('contacts', 'birthdate', 'DATE', 0, 'NULL', 0) in pragma

>>> db.close()
"""

# SQL query to create table:
# - table: `contacts`
# - column: `id`, integer, primary key, autoincrement
# - column: `firstname`, text
# - column: `lastname`, text
# - column: `birthdate`, date, default null
# - use: IF NOT EXISTS
# type: str
result = """

-- replace this comment
-- with your sql query

"""



# %% License
# - Copyright 2025, Matt Harasymczuk <matt@python3.info>
# - This code can be used only for learning by humans
# - This code cannot be used for teaching others
# - This code cannot be used for teaching LLMs and AI algorithms
# - This code cannot be used in commercial or proprietary products
# - This code cannot be distributed in any form
# - This code cannot be changed in any form outside of training course
# - This code cannot have its license changed
# - If you use this code in your product, you must open-source it under GPLv2
# - Exception can be granted only by the author

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% About
# - Name: Database Create Index
# - Difficulty: easy
# - Lines: 2
# - Minutes: 3

# %% English
# 1. Write SQL query to create index:
#    - name: `contacts_lastname`
#    - table: `contacts`
#    - column: `lastname`
#    - use: IF NOT EXISTS
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby stworzyć indeks:
#    - nazwa: `contacts_lastname`
#    - tabela: `contacts`
#    - kolumna: `lastname`
#    - użyj: IF NOT EXISTS
# 2. Uruchom doctesty - wszystkie muszą się powieść

# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> from pathlib import Path
>>> import sqlite3

>>> database = Path(__file__).parent.parent / 'shop.db'
>>> db = sqlite3.connect(database)

>>> try:
...     _ = db.execute(result)
...     db.commit()
... except sqlite3.OperationalError as e:
...     assert 'index contacts_lastname already exists' in str(e)

>>> INDEXES = 'SELECT `name` FROM `sqlite_master` WHERE `type`="index"'
>>> indexes = {row[0] for row in db.execute(INDEXES)}
>>> assert 'contacts_lastname' in indexes

>>> PRAGMA = '''
...     SELECT
...         `m`.`tbl_name` AS `table_name`,
...         `il`.`name` AS `index_name`,
...         `ii`.`name` AS `column_name`,
...         CASE `il`.`origin` WHEN 'pk' THEN 1 ELSE 0 END AS `is_primary_key`,
...         CASE `il`.[unique] WHEN 1 THEN 0 ELSE 1 END AS `non_unique`,
...         `il`.[unique] AS `is_unique`,
...         `il`.`partial`,
...         `il`.`seq` AS `sequence_in_index`
...     FROM `sqlite_master` AS `m`,
...         `pragma_index_list`(`m`.`name`) AS `il`,
...         `pragma_index_info`(`il`.`name`) AS `ii`
...     WHERE `m`.`type` = 'table'
...       AND `m`.`tbl_name` = 'contacts'
...     GROUP BY
...         `m`.`tbl_name`,
...         `il`.`name`,
...         `ii`.`name`,
...         `il`.`origin`,
...         `il`.`partial`,
...         `il`.`seq`
...     ORDER BY 1, 6
... '''

>>> pragma = db.execute(PRAGMA).fetchall()
>>> assert ('contacts', 'contacts_lastname', 'lastname', 0, 1, 0, 0, 0) in pragma

>>> db.close()
"""

# Write SQL query to create index:
# - name: `contacts_lastname`
# - table: `contacts`
# - column: `lastname`
# - use: IF NOT EXISTS
# type: str
result = """

-- replace this comment
-- with your sql query

"""