Crystal-db how to insert to database when there is an id column

Help & Support
1

I have id column as primary key and auto increment.
I couldn’t insert value into database.

db.exec “insert into mytable values (?,?,?)”, args: args

How can i solve this problem?

(I’m using SQLite)

2

Hi! Could you provide reproducible code and what’s the error you are getting?

3

My table is id: primary key auto increment unique, name: Text

args = [] of DB::Any
args << env.params.json["name"].as(Float64)
db.exec "insert into names values (?,?)", args: args

Error 500 at POST / - UNIQUE constraint failed: names.id

how can i define first ? as primary key auto increment and unique?

4

I think the core issue here is your SQL just isn’t correct for the behavior you want. I.e. there is only 1 value in args array, which is generating something basically like insert into names (id, name) values (123, null); So given you’re manually telling it what the ID column should be, it errors since its the PK and has to be unique. Try doing like:

name = env.params.json["name"].as(String)
db.exec "insert into names (name) values (?)", name

Also I was assuming it’s a typo in that name isn’t a Float64?

5

I tried that before but i am getting this error. Basically it’s forcing me to add id column in args and sql command.

Error 500 at POST / - UNIQUE constraint failed: names.id

6

i just removed unique from id column and now i am getting

table names has 2 columns but 1 values were supplied
7

Can you share your full code along with the schema of this table?

Because doing this works fine:

create table names
(
    id   integer not null
        constraint key_name
            primary key autoincrement,
    name text
);

require "sqlite3"

DB.open "sqlite3://./data.db" do |db|
  name = "Fred"
  db.exec "insert into names (name) values (?)", name
end

Running that three times i get three records with IDs 1, 2, and 3; each with the name column as Fred. So something else has to be going on.

1 Like
8

SQLite’s implementation of autoincrement, among many other things, has some quirks — it’s not the same as its counterpart in MySQL or the SERIAL type in Postgres. Basically, it only works if it’s an INTEGER type and has PRIMARY KEY set on the column. So if you set id INTEGER PRIMARY KEY in the CREATE TABLE statement, that’s all you need:

sqlite> create table things(id integer primary key, name);
sqlite> insert into things(name) values ('name 1');
sqlite> insert into things(name) values ('name 2');
sqlite> select * from things;
1|name 1
2|name 2
1 Like