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

"""
* Assignment: Database Create Table
* Complexity: easy
* Lines of code: 5 lines
* Time: 5 min

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

Polish:
    1. Napisz zapytanie SQL aby stworzyć tabelę:
       a. tabela: `contacts`
       b. kolumna: `id`, integer, primary key, autoincrement
       c. kolumna: `firstname`, text
       d. kolumna: `lastname`, text
       e. kolumna: `birthdate`, date, default null
       f. 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 / '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

"""

"""
* Assignment: Database Create Index
* Complexity: easy
* Lines of code: 2 lines
* Time: 3 min

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

Polish:
    1. Napisz zapytanie SQL aby stworzyć indeks:
       a. nazwa: `contacts_lastname`
       b. tabela: `contacts`
       c. kolumna: `lastname`
       d. 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 / '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

"""