SQLite dodaje klucz podstawowy

Utworzyłem tabelę w Sqlite, używając CREATE TABLE ASskładni do utworzenia tabeli na podstawie SELECTinstrukcji. Teraz ta tabela nie ma klucza podstawowego, ale chciałbym go dodać.

Wykonywanie ALTER TABLE table_name ADD PRIMARY KEY(col1, col2,...)powoduje błąd składni „blisko PRIMARY”

Czy istnieje sposób na dodanie klucza podstawowego podczas tworzenia tabeli lub później w Sqlite?

Przez „podczas tworzenia” mam na myśli tworzenie z CREATE TABLE AS.

Nie możesz modyfikować tabel SQLite w żaden znaczący sposób po ich utworzeniu. Zaakceptowanym sugerowanym rozwiązaniem jest utworzenie nowej tabeli z odpowiednimi wymaganiami i skopiowanie do niej danych, a następnie usunięcie starej tabeli.

tutaj jest oficjalna dokumentacja na ten temat: http://sqlite.org/faq.html#q11

Tak długo, jak używasz CREATE TABLE, jeśli tworzysz klucz podstawowy w pojedynczym polu , możesz użyć:

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

W przypadku programu CREATE TABLEzawsze możesz użyć następującego podejścia, aby utworzyć klucz podstawowy w jednym lub wielu polach :

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

Źródła: http://www.sqlite.org/lang_createtable.html

Ta odpowiedź nie dotyczy zmiany tabeli.

Próbowałem później dodać klucz podstawowy, zmieniając bezpośrednio tabelę sqlite_master. Ta sztuczka wydaje się działać. Jest to oczywiście rozwiązanie hakerskie.

W skrócie: utwórz zwykły (unikalny) indeks w tabeli, następnie udostępnij schemat do zapisu i zmień nazwę indeksu na postać zarezerwowaną przez sqlite, aby zidentyfikować indeks klucza podstawowego (np. Sqlite_autoindex_XXX_1, gdzie XXX to nazwa tabeli) i ustaw ciąg sql na NULL. W końcu zmień samą definicję tabeli. Jeden błąd: sqlite nie widzi zmiany nazwy indeksu, dopóki baza danych nie zostanie ponownie otwarta. Wygląda na to, że jest to błąd, ale nie poważny (nawet bez ponownego otwarcia bazy danych nadal możesz z niej korzystać).

Załóżmy, że tabela wygląda następująco:

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

Następnie wykonałem następujące czynności:

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;

Niektóre testy (w powłoce sqlite):

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    

Zgodnie z dokumentacją sqlite dotyczącą tworzenia tabeli, użycie funkcji create table as select powoduje utworzenie nowej tabeli bez ograniczeń i bez klucza podstawowego.

Jednak dokumentacja mówi również, że klucze podstawowe i unikalne indeksy są logicznie równoważne ( zobacz sekcję ograniczenia ):

W większości przypadków ograniczenia UNIQUE i PRIMARY KEY są implementowane przez utworzenie unikalnego indeksu w bazie danych. (Wyjątkami są INTEGER PRIMARY KEY i PRIMARY KEY w tabelach BEZ ROWID.) W związku z tym poniższe schematy są logicznie równoważne:

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

Tak więc, nawet jeśli nie możesz zmienić definicji tabeli za pomocą składni zmiany języka SQL, możesz uzyskać ten sam efekt klucza podstawowego poprzez użycie unikalnego indeksu.

Ponadto każda tabela (z wyjątkiem tabel utworzonych bez składni identyfikatora wiersza) ma wewnętrzną kolumnę z liczbą całkowitą znaną jako „identyfikator wiersza”. Zgodnie z dokumentacją możesz użyć tej wewnętrznej kolumny do pobierania / modyfikowania tabel rekordów.

Możesz to zrobić w ten sposób:

CREATE TABLE mytable (
field1 text,
field2 text,
field3 integer,
PRIMARY KEY (field1, field2)
);

Wprowadzenie

Jest to oparte na Javie Androida i jest dobrym przykładem na zmianę bazy danych bez irytowania fanów / klientów aplikacji. Jest to oparte na pomyśle strony SQLite FAQ http://sqlite.org/faq.html#q11

Problem

Nie zauważyłem, że muszę ustawić row_number lub record_id, aby usunąć pojedynczy zakupiony przedmiot z paragonu, a jednocześnie numer kodu kreskowego przedmiotu skłonił mnie do myślenia o uczynieniu go kluczem do usunięcia tego przedmiotu. Zapisuję szczegóły paragonu w tabeli Receiver_barcode. Pozostawienie go bez parametru record_id może oznaczać usunięcie wszystkich zapisów tego samego towaru w paragonie, jeśli użyłem kodu kreskowego towaru jako klucza.

Ogłoszenie

Proszę zrozumieć, że jest to kopiowanie i wklejanie mojego kodu, nad którym pracuję w momencie pisania tego tekstu. Użyj tego tylko jako przykładu, losowe kopiowanie i wklejanie nie pomoże. Zmodyfikuj to najpierw zgodnie ze swoimi potrzebami

Nie zapomnij również przeczytać komentarzy w kodzie.

Kod

Użyj tej metody w swojej klasie, aby najpierw sprawdzić, czy brakuje kolumny, którą chcesz dodać. Robimy to tylko po to, aby nie powtarzać procesu zmiany tabeli Receiver_barcode. Po prostu wspomnij o tym jako część swojej klasy. W następnym kroku zobaczysz, jak go wykorzystamy.

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

Następnie poniższy kod jest używany do tworzenia tabeli Receiver_barcode, jeśli NIE została ona już zamknięta przez użytkowników aplikacji po raz pierwszy. I proszę zwrócić uwagę na „JEŚLI NIE ISTNIEJE” w kodzie. Ma znaczenie.

//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();
            }

Miałem ten sam problem i najlepszym rozwiązaniem, jakie znalazłem, jest najpierw utworzenie tabeli definiującej klucz podstawowy, a następnie użycie instrukcji insert into.

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

INSERT INTO mytable 
SELECT field1, field2 
FROM anothertable;

Użyłem składni CREATE TABLE AS do scalenia kilku kolumn i napotkałem ten sam problem. Oto AppleScript, który napisałem, aby przyspieszyć proces.

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"

Myślę, że dodanie indeksu do tej kolumny może dać prawie taki sam efekt.

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>

Użyj narzędzia takiego jak przeglądarka DB dla SQLite, pozwala na dodanie PK, AI poprzez proste kliknięcie prawym przyciskiem myszy na tabeli -> modyfikuj.