如何将 Derby 的列从 PRIMARY KEY 更改为 IDENTITY
创建表的 SQL 是：
CREATE TABLE myTable(id INTEGER NOT NULL PRIMARY KEY, ...)
相反，我需要它是：
CREATE TABLE myTable(id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), ...)
如上所述在 Derby 文档中。所以我的问题是在初始创建语句之后我需要创建的更改语句是什么？换句话说：
CREATE TABLE myTable(id INTEGER NOT NULL PRIMARY KEY, ...)
ALTER TABLE myTable ...
非常感谢您的帮助！
The SQL for the creation of the table is:
CREATE TABLE myTable(id INTEGER NOT NULL PRIMARY KEY, ...)
Instead I need it to be:
CREATE TABLE myTable(id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), ...)
as described in the Derby documentation. So my question is what would be the alter statement I would need to create AFTER the initial create statement? In other words:
CREATE TABLE myTable(id INTEGER NOT NULL PRIMARY KEY, ...)
ALTER TABLE myTable ...
Thank you very much for the assistance!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
查看文档这似乎是不可能的。您可以更改类型长度（甚至不能更改类型本身）、默认值、可空性和下一个生成的值，但即使最后一个选项也要求列已定义为 IDENTITY。 2009 年的帖子 说您甚至无法添加
IDENTITY列。一项测试证实了这一点直到今天都是正确的。所以看来只有一种解决办法：你必须更换桌子。像这样的事情：
这确实是一个不幸的解决方案，因为如果您已经有其他表引用
表的 id列，因为这意味着进一步的工作。我尝试弄乱 系统表 但它们似乎已被读取-仅（并且有充分的理由）。
Looking at the documentation this seems impossible. You can change the type length (not even the type itself), the default, nullability and the next generated value but even the last option requires the column to already be defined as
IDENTITY. A thread from 2009 says that you can't even add anIDENTITYcolumn. A test confirms this is true to this day.So it seems there is only one solution: You have to replace the table. Something like this:
It's really an unfortunate solution because if you already have other tables referencing the
idcolumn of your table as that would mean further work.I tried messing with the system tables but they seem to be read-only (and for good reason).
从 10.12.1.1 版本。现在，诸如以下命令
现在可以工作：对现有数据库的命令，
GENERATED BY DEFAULT也是如此。看来此更改要求底层数据库至少采用 10.11 格式。Looks like this issue in Derby has been fixed as of the 10.12.1.1 release. Now commands such as:
to an existing database now work, as does
GENERATED BY DEFAULT. Looks like the change requires the underlying database to be at least in 10.11 format.一种技术是：(a) 创建一个新表，其中根据需要定义新列，所有其他列与以前一样，(b) 运行 INSERT INTO ... SELECT ... 语句来复制所有数据从现有表到新表，(c) RENAME TABLE 将旧表重命名为其他名称，(d) RENAME TABLE 将新表重命名为正确的表名，最后 (e) DROP TABLE 旧表。
One technique is to: (a) create a new table with the new column defined as you desire, and all other columns as they were before, (b) run an INSERT INTO ... SELECT ... statement to copy all the data from the existing table to the new table, (c) RENAME TABLE to rename the old table to some other name, (d) RENAME TABLE to rename the new table to the correct tablename, and then finally (e) DROP TABLE the old table.