• Welcome, Guest. Please login.
 
December 13, 2019, 08:09:58 pm

News:

Welcome to the SQLitening support forums!


Without RowID

Started by cj, September 24, 2017, 03:11:51 am

Previous topic - Next topic

cj

Without RowID requires a primary key so why no error as per the documentation?

#INCLUDE "sqlitening.inc"  'withoutrowid.bas
'https://www.sqlite.org/withoutrowid.html
FUNCTION PBMAIN () AS LONG
slOpen "junk.db3","C"
slexe  "drop table if exists t1"
slexe "create table if not exists t1(apples without rowid)"
slexe "insert into t1 values('pink lady')"
DIM sArray() AS STRING
slSelAry "select rowid,* from t1",sArray(),"Q9"
? JOIN$(sArray(),$CR)
END FUNCTION                                   

Bern Ertl

I have never used it, so I'm just reading the SQLite docs on this, but it appears as if your CREATE TABLE statement is malformed.  I think it should be:

"create table if not exists t1(apples) without rowid"

WITHOUT ROWID only processes to exclude the default rowid column if the words are outside of the parenthesis enclosing the table name(s).

cj

Maybe it is working, but shouldn't the rowid column be gone?

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
slSetProcessMods "E2"
slOpen "sample.db3"
slexe  "drop table if exists t1"
slexe  "create table if not exists t1(c1) without rowid"  'primary key missing on table t1
slexe  "create table if not exists t1(c1 without rowid)"  'no error
slexe  "insert into t1 values('apple')"
? viewit("select rowid,c1 from t1")   '1  apple

END FUNCTION
FUNCTION viewit(sql AS STRING) AS STRING
DIM sArray() AS STRING
slselary sql,sArray(),"Q9"
FUNCTION = JOIN$(sArray$(),$CR)
END FUNCTION                   

Bern Ertl

According to the WITHOUT ROWID docs, you need to include a PRIMARY KEY in your table definition if you are using WITHOUT ROWID (after the end parenthesis).

"create table if not exists t1(c1 PRIMARY KEY) without rowid"

cj

Thank you!  That was it.   Without Rowid outside parens and need primary key.