在平面上改变列

我如何改变在平面柱？ 这是 Postgresql

ALTER TABLE books_book ALTER COLUMN publication_date DROP NOT NULL;

我相信在 sqlite 中根本没有 ALTER 列，只支持 ALTERTABLE。

知道吗? 谢谢！

135624 次浏览
小开
最佳答案

There's no ALTER COLUMN in sqlite.

I believe your only option is to:

  • Rename the table to a temporary name
  • Create a new table without the NOT NULL constraint
  • Copy the content of the old table to the new one
  • Remove the old table

This other Stackoverflow answer explains the process in details

小开

While it is true that the is no ALTER COLUMN, if you only want to rename the column, drop the NOT NULL constraint, or change the data type, you can use the following set of dangerous commands:

PRAGMA writable_schema = 1;
UPDATE SQLITE_MASTER SET SQL = 'CREATE TABLE BOOKS ( title TEXT NOT NULL, publication_date TEXT)' WHERE NAME = 'BOOKS';
PRAGMA writable_schema = 0;

You will need to either close and reopen your connection or vacuum the database to reload the changes into the schema.

For example:

Y:\> **sqlite3 booktest**
SQLite version 3.7.4
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> **create table BOOKS ( title TEXT NOT NULL, publication_date TEXT NOT
NULL);**
sqlite> **insert into BOOKS VALUES ("NULLTEST",null);**
Error: BOOKS.publication_date may not be NULL
sqlite> **PRAGMA writable_schema = 1;**
sqlite> **UPDATE SQLITE_MASTER SET SQL = 'CREATE TABLE BOOKS ( title TEXT NOT
NULL, publication_date TEXT)' WHERE NAME = 'BOOKS';**
sqlite> **PRAGMA writable_schema = 0;**
sqlite> **.q**


Y:\> **sqlite3 booktest**
SQLite version 3.7.4
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> **insert into BOOKS VALUES ("NULLTEST",null);**
sqlite> **.q**

REFERENCES FOLLOW:

pragma writable_schema
When this pragma is on, the SQLITE_MASTER tables in which database can be changed using ordinary UPDATE, INSERT, and DELETE statements. Warning: misuse of this pragma can easily result in a corrupt database file.

[alter table](From http://www.sqlite.org/lang_altertable.html)
SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table.

ALTER TABLE SYNTAX

小开

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table. But you can alter table column datatype or other property by the following steps.

  1. BEGIN TRANSACTION;
  2. CREATE TEMPORARY TABLE t1_backup(a,b);
  3. INSERT INTO t1_backup SELECT a,b FROM t1;
  4. DROP TABLE t1;
  5. CREATE TABLE t1(a,b);
  6. INSERT INTO t1 SELECT a,b FROM t1_backup;
  7. DROP TABLE t1_backup;
  8. COMMIT

For more detail you can refer the link.

小开

  1. CREATE TABLE temp_Table(x,y[,etc]);

  2. INSERT INTO temp_Table SELECT * FROM Table;

  3. DROP TABLE Table;

  4. ALTER TABLE temp_Table RENAME TO Table;

Thanks for helping me to find a definitive method!

小开

ALTER COLUMN does not exist in SQLite.

Only Supported alter operations:

  • Alter Table Name
  • Alter Table Column Name
  • Add New Column
  • Drop Column

Alex Jasmin's answer shows possible way

Reference:


提交答案