Error when setting Composite Primary Key in SQLite3

Question

Id like to set the Primary key of a tabe Im building as the composite key of 3 integers. Im doing this within a Ruby script like so:

  ...Ruby Script
  .............

  .............
  #Begin database operations
  begin
  db = SQLite3::Database.new ("#{filename.split('.')[0]}" + ".db")

  db.execute ("CREATE TABLE IF NOT EXISTS AIRED (programCode INTEGER , fromDate INTEGER, 
  toDate INTEGER, PRIMARY KEY(programCode,fromDate , toDate), program TEXT, channel  TEXT, 
   weekday TEXT, startTime TEXT, endTime TEXT, duration TEXT)")

  rescue SQLite3::Exception => e
  puts "Exception occured"
  puts e.message
  puts e.backtrace

  ensure
  db.close if db

  end

   .........

When I run this script I get the following error at Terminal

      Exception occured
      near "program": syntax error
      /Users/AM/.rvm/gems/ruby-2.0.0-p195/gems/sqlite3-1.3.7/lib/sqlite3/database.rb:91:in `initialize'
      /Users/AM/.rvm/gems/ruby-2.0.0-p195/gems/sqlite3-1.3.7/lib/sqlite3/database.rb:91:in `new'
     /Users/AM/.rvm/gems/ruby-2.0.0-p195/gems/sqlite3-1.3.7/lib/sqlite3/database.rb:91:in `prepare'
     /Users/AM/.rvm/gems/ruby-2.0.0-p195/gems/sqlite3-1.3.7/lib/sqlite3/database.rb:134:in `execute'
      csv_parser.rb:60:in `<main>'

I looked at this SO post to see how a composite key is made:

What am I doing wrong?

Thanks

The [documentation](http://www.sqlite.org/lang_createtable.html) suggests that constraints (like primary keys) have to be listed after all columns — Andomar, Jul 14 '13 at 11:50

score 1 · Accepted Answer · answered Jul 14 '13 at 13:11

You can't put a table constraint before the end of the columns definitions like that. You need to write this

db.execute <<__SQL__
CREATE TABLE IF NOT EXISTS AIRED (
  programCode INTEGER,
  fromDate    INTEGER,
  toDate      INTEGER,
  program     TEXT,
  channel     TEXT,
  weekday     TEXT,
  startTime   TEXT,
  endTime     TEXT,
  duration    TEXT,
  PRIMARY KEY (programCode, fromDate, toDate)
)
__SQL__

Error when setting Composite Primary Key in SQLite3

1 Answers1