create-table with auto increment column?

Question

Racket's sql module provides create-table as abstraction for CREATE TABLE sql statement.

How should I provide specification of column (ie. id) with AUTO INCREMENT ?

When I try

(create-table numbers
  #:columns [n integer #:not-null #:auto-increment] [t text]
  #:constraints (primary-key n))

then it shows error:

create-table: unexpected term
  parsing context: 
    while parsing ColumnDef
    while parsing CreateTableInner in: #:auto-increment

Is it possible to specify auto increment column within create-table?

Answer 1

As far as I know the create-table in the sql package doesn't handle auto-increment. Instead consider using query-exec directly. Here is a snippet from Rosetta Code.

#lang at-exp racket

(require db)
(define postal (sqlite3-connect #:database "/tmp/postal.db" #:mode 'create))

(define (add! name street city state zip)
  (query-exec postal
    @~a{INSERT INTO addresses (name, street, city, state, zip)
        VALUES (?, ?, ?, ?, ?)}
    name street city state zip))

(unless (table-exists? postal "addresses")
  (query-exec postal
    @~a{CREATE TABLE addresses(
          id INTEGER PRIMARY KEY,
          name   TEXT NOT NULL,
          street TEXT NOT NULL,
          city   TEXT NOT NULL,
          state  TEXT NOT NULL,
          zip    TEXT NOT NULL)}))

See more here: https://rosettacode.org/wiki/Table_creation/Postal_addresses#Racket