首页 文章

SQLite添加主键

提问于
浏览 次
90

我使用 CREATE TABLE AS 语法在Sqlite中创建了一个表，以基于 SELECT 语句创建表 . 现在这个表没有主键，但我想添加一个 .

执行 ALTER TABLE table_name ADD PRIMARY KEY(col1, col2,...) 会出现语法错误"near PRIMARY"

有没有办法在表创建期间或之后在Sqlite中添加主键？

编辑："during creation"我的意思是在创作期间使用 CREATE TABLE AS .

sqlite

10 回答

  • 0

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

    这是关于此的官方文档：http://sqlite.org/faq.html#q11

    回复于
  • 5

    只要您使用 CREATE TABLE ，如果您在 single field 上创建主键，则可以使用：

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

    使用 CREATE TABLE ，您还可以始终使用以下方法在一个或 multiple fields 上创建主键：

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

    参考：http://www.sqlite.org/lang_createtable.html

    这个答案没有解决表格的变化 .

    回复于
  • 6

    我之后尝试通过直接更改sqlite_master表来添加主键 . 这个技巧似乎有效 . 当然这是一个黑客的解决方案 .

    简而言之：在表上创建一个常规（唯一）索引，然后使模式可写并将索引的名称更改为sqlite保留的形式以标识主键索引（即sqlite_autoindex_XXX_1，其中XXX是表名）并将sql字符串设置为NULL . 最后更改表定义本身 . 一个pittfal：在重新打开数据库之前，sqlite看不到索引名称的更改 . 这似乎是一个错误，但不是一个严重的错误（即使没有重新打开数据库，你仍然可以使用它） .

    假设表格如下：

    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
    回复于
  • 0

    根据关于表创建的sqlite docs，使用create table as select生成一个没有约束且没有主键的新表 .

    但是，文档还说主键和唯一索引在逻辑上是等价的（see constraints section）：

    在大多数情况下，通过在数据库中创建唯一索引来实现UNIQUE和PRIMARY KEY约束 . （例外情况是WITHOUT ROWID表上的INTEGER PRIMARY KEY和PRIMARY KEY . ）因此，以下模式在逻辑上是等价的：CREATE TABLE t1（a，b UNIQUE）;

    CREATE TABLE t1（a，b PRIMARY KEY）;

    CREATE TABLE t1（a，b）;
    创建独特的索引t1b ON t1（b）;

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

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

    回复于
  • 3

    你可以这样做：

    CREATE TABLE mytable (
field1 text,
field2 text,
field3 integer,
PRIMARY KEY (field1, field2)
);
    回复于
  • 0

    Introduction

    这是基于Android 's java and it' s的一个很好的例子，更改数据库而不会烦扰您的应用程序粉丝/客户 . 这是基于SQLite FAQ页面的想法http://sqlite.org/faq.html#q11

    The problem

    我没有注意到我需要设置row_number或record_id来删除收据中的单个购买项目，同时项目条形码编号欺骗我，使其成为删除该项目的关键 . 我在表receipt_barcode中保存收据明细 . 如果我使用条目条形码作为密钥，则在没有record_id的情况下离开它可能意味着删除收据中相同项目的所有记录 .

    Notice

    请理解这是我在撰写本文时正在处理的代码的复制粘贴 . 仅使用它作为示例，随机复制粘贴不会帮助您 . 首先根据您的需要进行修改

    另外，请不要忘记阅读代码中的注释 .

    The Code

    使用此方法在类中检查是否缺少要添加的列 . 我们这样做只是为了不重复更改表receipt_barcode的过程 . 只需将其作为课程的一部分提及 . 在下一步中，您将看到我们将如何使用它 .

    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;
}

    然后，以下代码用于创建表receipt_barcode，如果它已经没有为您的应用的第一次用户退出 . 请注意代码中的"IF NOT EXISTS" . 它很重要 .

    //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();
            }
    回复于
  • 10

    我使用CREATE TABLE 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"
    回复于
  • 0

    我想添加一个索引列可以获得几乎相同的效果 .

    回复于
  • 30

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

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

INSERT INTO mytable 
SELECT field1, field2 
FROM anothertable;
    回复于
  • 114
    sqlite>  create table t(id int, col2 varchar(32), col3 varchar(8));
sqlite>  insert into t values(1, 'he', 'ha');
sqlite>
sqlite>  create table t2(id int primary key, col2 varchar(32), col3 varchar(8));
sqlite>  insert into t2 select * from t;
sqlite> .schema
CREATE TABLE t(id int, col2 varchar(32), col3 varchar(8));
CREATE TABLE t2(id int 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 int primary key, col2 varchar(32), col3 varchar(8));
sqlite>
    回复于

相关问题