SQLite add Primary Key

I created a table in Sqlite by using the CREATE TABLE AS syntax to create a table based on a SELECT statement. Now this table has no primary key but I would like to add one.

Executing ALTER TABLE table_name ADD PRIMARY KEY(col1, col2,...) gives a syntax error "near PRIMARY"

Is there a way to add a primary key either during table creation or afterwards in Sqlite?

By "during creation" I mean during creation with CREATE TABLE AS.

164957 次浏览
小开

You can do it like this:

CREATE TABLE mytable (
field1 text,
field2 text,
field3 integer,
PRIMARY KEY (field1, field2)
);
小开
最佳答案

在创建 SQLite 表之后，不能以任何重要的方式修改它们。接受的建议解决方案是创建一个具有正确要求的新表，并将数据复制到其中，然后删除旧表。

这是关于这个的官方文件: http://sqlite.org/faq.html#q11

小开

只要你使用 CREATE TABLE，如果你在 single field上创建主键，你可以使用:

CREATE TABLE mytable (
field1 TEXT,
field2 INTEGER PRIMARY KEY,
field3 BLOB,
);

With CREATE TABLE, you can also always use the following approach to create a primary key on one or 多个字段:

CREATE TABLE mytable (
field1 TEXT,
field2 INTEGER,
field3 BLOB,
PRIMARY KEY (field2, field1)
);

参考资料: http://www.sqlite.org/lang_createtable.html

这个答案不涉及表更改。

小开

我使用 CREATETABLE AS 语法合并多个列，遇到了同样的问题。下面是我为了加速这个过程而编写的一个 AppleScript。

set databasePath to "~/Documents/Databases/example.db"
set tableOne to "separate" -- Table from which you are pulling data
set tableTwo to "merged" -- Table you are creating
set {tempCol, tempColEntry, permColEntry} to \{\{}, {}, {}}
set permCol to {"id integer primary key"}


-- Columns are created from single items  AND from the last item of a list
-- \{\{"a", "b", "c"}, "d", "e"} Columns "a" and "b" will be merged into a new column "c".  tableTwo will have columns "c", "d", "e"


set nonCoal to {"City", "Contact", "Names", {"Address 1", "Address", "address one", "Address1", "Text4", "Address 1"}, {"E-Mail", "E-Mail Address", "Email", "Email Address", "EmailAddress", "Email"}, {"Zip", "Zip Code", "ZipCode", "Zip"}, {"Telephone", "BusinessPhone", "Phone", "Work Phone", "Telephone"}, {"St", "State", "State"}, {"Salutation", "Mr/Ms", "Mr/s", "Salutations", "Sautation", "Salutation"}}


-- Build the COALESCE statements
repeat with h from 1 to count of nonCoal
set aColumn to item h of nonCoal
if class of aColumn is not list then
if (count of words of aColumn) > 1 then set aColumn to quote & aColumn & quote
set end of tempCol to aColumn
set end of permCol to aColumn
else
set coalEntry to {}
repeat with i from 1 to count of aColumn
set coalCol to item i of aColumn as string
if (count of words of coalCol) > 1 then set coalCol to quote & coalCol & quote
if i = 1 then
set end of coalEntry to "TRIM(COALESCE(" & coalCol & ", '') || \" \" || "
else if i < ((count of aColumn) - 1) then
set end of coalEntry to "COALESCE(" & coalCol & ", '') || \" \" || "
else if i = ((count of aColumn) - 1) then
set as_Col to item (i + 1) of aColumn as string
if (count of words of as_Col) > 1 then set as_Col to quote & as_Col & quote
set end of coalEntry to ("COALESCE(" & coalCol & ", '')) AS " & as_Col) & ""
set end of permCol to as_Col
end if
end repeat
set end of tempCol to (coalEntry as string)
end if
end repeat


-- Since there are ", '' within the COALESCE statement, you can't use "TID" and "as string" to convert tempCol and permCol for entry into sqlite3. I rebuild the lists in the next block.
repeat with j from 1 to count of tempCol
if j < (count of tempCol) then
set end of tempColEntry to item j of tempCol & ", "
set end of permColEntry to item j of permCol & ", "
else
set end of tempColEntry to item j of tempCol
set end of permColEntry to item j of permCol
end if
end repeat
set end of permColEntry to ", " & item (j + 1) of permCol
set permColEntry to (permColEntry as string)
set tempColEntry to (tempColEntry as string)


-- Create the new table with an "id integer primary key" column
set createTable to "create table " & tableTwo & " (" & permColEntry & "); "
do shell script "sqlite3 " & databasePath & space & quoted form of createTable


-- Create a temporary table and then populate the permanent table
set createTemp to "create temp table placeholder as select " & tempColEntry & " from " & tableOne & ";  " & "insert into " & tableTwo & " select Null, * from placeholder;"
do shell script "sqlite3 " & databasePath & space & quoted form of createTemp


--export the new table as a .csv file
do shell script "sqlite3 -header -column -csv " & databasePath & " \"select * from " & tableTwo & " ; \"> ~/" & tableTwo & ".csv"
小开

之后，我试图通过直接更改 sqlite _ master 表来添加主键。 这招好像管用。 It is a hack solution of course.

In short: create a regular (unique) index on the table, then make the schema writable and change the name of the index to the form reserved by sqlite to identify a primary key index, (i.e. sqlite_autoindex_XXX_1, where XXX is the table name) and set the sql string to NULL. At last change the table definition itself. 一个隐患是: sqlite 在重新打开数据库之前不会看到索引名称的更改。这看起来像是一个 bug，但并不严重(即使不重新打开数据库，您仍然可以使用它)。

假设这个表看起来像:

CREATE TABLE tab1(i INTEGER, j INTEGER, t TEXT);

然后我做了以下几件事:

BEGIN;
CREATE INDEX pk_tab1 ON tab1(i,j);
pragma writable_schema=1;
UPDATE sqlite_master SET name='sqlite_autoindex_tab1_1',sql=null WHERE name='pk_tab1';
UPDATE sqlite_master SET sql='CREATE TABLE tab1(i integer,j integer,t text,primary key(i,j))' WHERE name='tab1';
COMMIT;

一些测试(在 sqlite shell 中) :

sqlite> explain query plan select * from tab1 order by i,j;
0|0|0|SCAN TABLE tab1 USING INDEX sqlite_autoindex_tab1_1
sqlite> drop index sqlite_autoindex_tab1_1;
Error: index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped
小开

简介

这是基于 Android 的 java，它是一个很好的例子，可以在不打扰应用程序的粉丝/客户的情况下更改数据库。这是基于 SQLite FAQ 页面的想法 Http://sqlite.org/faq.html#q11

The problem

我没有注意到我需要设置一个 row _ number 或 record _ id 来删除收据中的一个购买项目，同时条形码编号愚弄了我，让我以为它是删除该项目的关键。我将收据的详细信息保存在接收条形码表中。如果我使用商品条形码作为密钥，那么不使用 record _ id 可能意味着删除收据中同一商品的所有记录。

通知

请理解，这是一个复制粘贴我的代码，我在写这篇文章的时候工作。仅仅作为一个例子，随意复制粘贴不会帮助你。首先根据你的需要修改它

也请不要忘记阅读代码中的注释。

The Code

在类中使用此方法检查第1个要添加的列是否丢失。我们这样做只是为了避免重复修改表 Receip_ barcode 的过程。 Just mention it as part of your class. In the next step you'll see how we'll use it.

public boolean is_column_exists(SQLiteDatabase mDatabase , String table_name,
String     column_name) {
//checks if table_name has column_name
Cursor cursor = mDatabase.rawQuery("pragma table_info("+table_name+")",null);
while (cursor.moveToNext()){
if (cursor.getString(cursor.getColumnIndex("name")).equalsIgnoreCase(column_name)) return true;
}
return false;
}

然后，如果已经为应用程序的第一次用户执行了 没有退出操作，则使用以下代码创建表 Receip_ barcode。并请注意“如果不存在”的代码。这很重要。

//mDatabase should be defined as a Class member (global variable)
//for ease of access :
//SQLiteDatabse mDatabase=SQLiteDatabase.openOrCreateDatabase(dbfile_path, null);
creation_query = " CREATE TABLE if not exists receipt_barcode ( ";
creation_query += "\n record_id        INTEGER PRIMARY KEY AUTOINCREMENT,";
creation_query += "\n rcpt_id INT( 11 )       NOT NULL,";
creation_query += "\n barcode VARCHAR( 255 )  NOT NULL ,";
creation_query += "\n barcode_price VARCHAR( 255 )  DEFAULT (0),";
creation_query += "\n PRIMARY KEY ( record_id ) );";
mDatabase.execSQL(creation_query);


//This is where the important part comes in regarding the question in this page:


//adding the missing primary key record_id in table receipt_barcode for older versions
if (!is_column_exists(mDatabase, "receipt_barcode","record_id")){
mDatabase.beginTransaction();
try{
Log.e("record_id", "creating");




creation_query="CREATE TEMPORARY TABLE t1_backup(";
creation_query+="record_id INTEGER        PRIMARY KEY AUTOINCREMENT,";
creation_query+="rcpt_id INT( 11 )       NOT NULL,";
creation_query+="barcode VARCHAR( 255 )  NOT NULL ,";
creation_query+="barcode_price VARCHAR( 255 )  NOT NULL DEFAULT (0) );";
mDatabase.execSQL(creation_query);


creation_query="INSERT INTO t1_backup(rcpt_id,barcode,barcode_price) SELECT rcpt_id,barcode,barcode_price  FROM receipt_barcode;";
mDatabase.execSQL(creation_query);


creation_query="DROP TABLE receipt_barcode;";
mDatabase.execSQL(creation_query);


creation_query="CREATE TABLE receipt_barcode (";
creation_query+="record_id INTEGER        PRIMARY KEY AUTOINCREMENT,";
creation_query+="rcpt_id INT( 11 )       NOT NULL,";
creation_query+="barcode VARCHAR( 255 )  NOT NULL ,";
creation_query+="barcode_price VARCHAR( 255 )  NOT NULL DEFAULT (0) );";
mDatabase.execSQL(creation_query);


creation_query="INSERT INTO receipt_barcode(record_id,rcpt_id,barcode,barcode_price) SELECT record_id,rcpt_id,barcode,barcode_price  FROM t1_backup;";
mDatabase.execSQL(creation_query);


creation_query="DROP TABLE t1_backup;";
mDatabase.execSQL(creation_query);




mdb.setTransactionSuccessful();
} catch (Exception exception ){
Log.e("table receipt_bracode", "Table receipt_barcode did not get a primary key (record_id");
exception.printStackTrace();
} finally {
mDatabase.endTransaction();
}
小开

我遇到了同样的问题，我发现最好的解决方案是首先创建定义主键的表，然后使用 insert into 语句。

CREATE TABLE mytable (
field1 INTEGER PRIMARY KEY,
field2 TEXT
);


INSERT INTO mytable
SELECT field1, field2
FROM anothertable;
小开

根据 sqlite 医生关于表创建的内容，使用 作为选择创建表生成一个没有约束和主键的新表。

然而，文档还说主键和唯一索引在逻辑上是等价的(见约束条款) :

在大多数情况下，UNIQUE 和 PRIMARYKEY 约束是通过在数据库中创建唯一索引来实现的。(在没有 ROWID 表的情况下，INTEGERPRIMARYKEY 和 PRIMARYKEY 是例外。)因此，下列模式在逻辑上是等价的:

CREATE TABLE t1(a, b UNIQUE);


CREATE TABLE t1(a, b PRIMARY KEY);


CREATE TABLE t1(a, b);
CREATE UNIQUE INDEX t1b ON t1(b);

因此，即使不能通过 SQL alter 语法更改表定义，也可以通过使用唯一索引获得相同的主键效果。

此外，任何表(除了那些没有 rowid 语法创建的表)都有一个称为“ rowid”的内部整数列。根据文档，您可以使用这个内部列来检索/修改记录表。

小开 
sqlite>  create table t(id integer, col2 varchar(32), col3 varchar(8));
sqlite>  insert into t values(1, 'he', 'ha');
sqlite>
sqlite>  create table t2(id integer primary key, col2 varchar(32), col3 varchar(8));
sqlite>  insert into t2 select * from t;
sqlite> .schema
CREATE TABLE t(id integer, col2 varchar(32), col3 varchar(8));
CREATE TABLE t2(id integer primary key, col2 varchar(32), col3 varchar(8));
sqlite> drop table t;
sqlite> alter table t2 rename to t;
sqlite> .schema
CREATE TABLE IF NOT EXISTS "t"(id integer primary key, col2 varchar(32), col3 varchar(8));
小开

创建 TABLE 表名( 第一列整数主键, 专栏2 VARCHAR (50) )

插入表名 选择列1，列2 从另一个桌子


提交答案