Does SQLite Support Scope_Identity

Does SQLite support SCOPE_IDENTITY?

Check out the FAQ. The sqlite3_last_insert_rowid() function will do it. Careful of triggers though.

how to use scope_identity in sqlite

SQLite does not have a function by the name scope_identity

You are probably looking for 'SELECT last_insert_rowid()'

See also this question: Does SQLite support SCOPE_IDENTITY?

Does Sqlite support weak entities?

You can follow the following links regarding your doubts:

http://www.sqlite.org/foreignkeys.html#fk_composite

How to return the value of AUTO INCREMENT column in SQLite with VB6

Does SQLite support SCOPE_IDENTITY?

Check out the FAQ. The
sqlite3_last_insert_rowid()
function will do it. Careful of
triggers though

Not tested, but you should be able to send both statements in one call. It's been a while since I wrote any VB6. Also this is not SQL injection safe.

Dim oRs as Recordset
dim sSql as String
sSql = "INSERT INTO EventType (EventTypeName) VALUES ('blah'); SELECT last_insert_rowid() FROM EventType"
oRs.Open sSql oConn

Select SCOPE_IDENTITY after insert with SqlCeCommand

SQL Server CE doesn't support multiple queries in single command, try as below

SqlCeCommand  cmd = new SqlCeCommand("insert into my_table (col1) values (@c1)", conn);
//set paremeter values 
//execute insert 
cmd.ExecuteNonQuery();
//now change the sql statment to take identity 
cmd.CommandText = "SELECT @@IDENTITY";
int id = Convert.ToInt32(cmd.ExecuteScalar());

https://stackoverflow.com/a/6480017/2558060

Stop trigger changing scope_identity

The reason for it overwriting scope identity is still not clear, it could possibly be related to the bug mentioned. However a fix was found:

A temporary table was created "temp_wc"

Then at the end of the trigger, identity insert was switched on for that table, and an insert was done, for the ID that we want to keep after the trigger has fired. This method can be thought of as overwriting the overwritten scope identity again.

SET IDENTITY_INSERT ON
INSERT INTO temp_wc VALUES (@ID, 'fix scope identity error')

On INSERT how do I retrieve ROWID or the AUTOINCREMENT value?

for SQLite you can use

SELECT last_insert_rowid()

here is another stackoverflow post with more details

How to retrieve the last autoincremented ID from a SQLite table?

Using 'Auto-Sync' feature of DBML with SQLite

You are adding a "Linq-to-SQL" data model to your project, but you're using it against SQLite - that'll never work of course! Linq-to-SQL only ever supports SQL Server (always has, always will), and thus its SQL statements that it generates are SQL Server T-SQL Statements - nothing else.

If you want to use the Entity Framework with SQLite, you need to use "ADO.NET Entity Data Model" (file with the .EDMX extension) as your data model.

Only that will support third-party database drivers like SQLite and others!

In SQLITE, does specifying the integer type for Primary Keys matter considering that primary keys must have unique values?

The column type INT, INTEGER, WHATEVER (you can specify virtually any column type) has little bearing, it's an indication of what is to be stored in the column. However, it does not set the type as with one exception (to be discussed) of data that can be stored. In short any type (bar the exception) of data can be stored in any column (irrespective of the defined column type).

• see 3.1 Determination of Column Affinity in the link below

SQL does not differentiate between stored values other than the storage class (null,integer,real,text,blob), if stored as an INTEGER then it is an integer bound only by the limitations of it being stored in at most 8 bytes (64 bit signed).

• see 2. Storage Classes and Datatypes in the link below

The exception is the use specifically of INTEGER PRIMARY KEY or INTEGER with the column set as the primary key at the table level. The value stored MUST be an integer otherwise a DATATYPE MISMATCH will occur.

• as per Any column in an SQLite version 3 database, except an INTEGER PRIMARY KEY column, may be used to store a value of any storage class. ( also in 2. Storage Classes and Datatypes)

So ultimately my question is, will declaring the datatype of a primary key to be specifically be one of TINYINT SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT, INT2, INT8 make any difference whatsoever?

Not with the listed types (TINYINT ....) as the types all contain INT they will have a type affinity of INTEGER and the column will NOT be an alias of the rowid column.

If you included INTEGER in the list then YES it will make a difference as the column will then be an alias of the rowid column (i.e. it is INTEGER PRIMARY KEY). The column will also be restricted to being an integer value (the columns using the other listed types will not be restricted to integer values).

You may wish to refer to Datatypes in SQLite

The following SQL demonstrates some of the above:-

DROP TABLE IF EXISTS example;
CREATE TABLE IF NOT EXISTS example (
    rowid_alias_must_be_unique_integer INTEGER PRIMARY KEY, -- INTEGER PRIMARY KEY makes the column an alias of the rowid
    col_text TEXT,
    col_integer INTEGER,
    col_real REAL,
    col_BLOB BLOB,
    col_anyother this_is_a_stupid_column_type  -- will have a type affinitiy of NUMERIC
);

/* INSERTS first row with a negative rowid */
INSERT INTO example VALUES (-100,'MY TEXT', 340000,34.5678,x'f0f1f2f3f4f5f6f7f8f9fafbfcfdfeff',100);
/* All subsequent inserts use the generated rowid */
/* the same value is inserted into all the other columns */
INSERT INTO example (col_text,col_integer,col_real,col_blob,col_anyother) VALUES
    ('MY TEXT','MY TEXT','MY TEXT','MY TEXT','MY TEXT'),
    (100,100,100,100,100),
    (34.5678,34.5678,34.5678,34.5678,34.5678),
    (x'f0f1f2f3f4f5f6f7f8f9fafbfcfdfeff',x'f0f1f2f3f4f5f6f7f8f9fafbfcfdfeff',x'f0f1f2f3f4f5f6f7f8f9fafbfcfdfeff',x'f0f1f2f3f4f5f6f7f8f9fafbfcfdfeff',x'f0f1f2f3f4f5f6f7f8f9fafbfcfdfeff')
;

SELECT 
    *,
    rowid, 
    typeof(rowid_alias_must_be_unique_integer),
    typeof(col_text), 
    typeof(col_integer),
    typeof(col_real),
    typeof(col_blob),
    typeof(col_anyother)
FROM example
;
/* WILL FAIL as rowid alias is not an integer */
INSERT INTO example VALUES('a','a','a','a','a','a');
DROP TABLE IF EXISTS example;

The result of the first SELECT will be :-

• Note that blobs are handled/displayed according to how the tool (Navicat for SQLite) handles the display of blobs.

The last INSERT fails because the value being inserted into the rowid alias is not an integer value e.g. :-

/* WILL FAIL as rowid alias is not an integer */
INSERT INTO example VALUES('a','a','a','a','a','a')
> datatype mismatch
> Time: 0s

• Note that the answer has not dealt with the intricacies of how the column affinity may effect the extraction of data.

---

Related Topics