在 SQL 中更改表添加列语法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3448342/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Alter table add column syntax in SQL
提问by lakshmi
I'm trying to alter the table named company, but it will display the error
我正在尝试更改名为 company 的表，但它会显示错误
syntax error at or near "("
LINE 2: ADD( company_access_level short NOT NULL,
My syntax is
我的语法是
ALTER TABLE company
ADD company_access_level short NOT NULL,
workgroup_level short NOT NULL,
Company Logon URL character varying NOT NULL,
Company Logoff URL character varying NOT NULL
Thanks
谢谢
回答by Martin Smith
I just tried this fixed syntax in postgressql and it worked. There is no shortdatatype though so you'll have to use something else (maybe smallint?) If your table contains data this script will fail for the reasons in John's answer.
我刚刚在 postgressql 中尝试了这个固定的语法，它奏效了。short但是没有数据类型，因此您必须使用其他东西（也许是smallint？）如果您的表包含数据，则此脚本将因约翰回答中的原因而失败。
ALTER TABLE company
ADD company_access_level int NOT NULL,
ADD workgroup_level int NOT NULL,
ADD "Company Logon URL" character varying NOT NULL,
ADD "Company Logoff URL" character varying NOT NULL
回答by John Pickup
Also, if your table has data in it then you can't add NOT NULL columns (and for some RDBMSs you can't add NOT NULL columns even when there is no data present in the table).
此外，如果您的表中有数据，则不能添加 NOT NULL 列（对于某些 RDBMS，即使表中没有数据，您也不能添加 NOT NULL 列）。
Either provide a default value or allow the column to be NULLable. You can always populate the new columns with data and modify the columns to be NOT NULL afterwards.
要么提供默认值，要么允许该列为 NULLable。您始终可以使用数据填充新列，然后将列修改为 NOT NULL。
回答by Paul Smith
Sorry for opening such an old question but the advice in one of the answers that you could not add a NOT NULL cost me quite a bit of trouble. You CANadd a NOT NULL column to a table with data, the only restriction is that you must also provide a default value. Tested with Sybase, Postgres and MySQL. So the example above becomes:
很抱歉打开这样一个老问题，但其中一个答案中的建议您不能添加 NOT NULL 给我带来了很多麻烦。您CANNOT NULL列添加到数据表中，唯一的限制是，你还必须提供一个默认值。使用 Sybase、Postgres 和 MySQL 进行测试。所以上面的例子变成了：
ALTER TABLE company
ADD company_access_level int default 'not set' NOT NULL ,
ADD workgroup_level int default 0 NOT NULL,
ADD "Company Logon URL" character varying default 'not set' NOT NULL,
ADD "Company Logoff URL" character varying default 'not set' NOT NULL
回答by Malik A. Rumi
As someone who came here with the same question, I'm not sure what to make of these answers. I tried them all, and always got a syntax error 'near' one term or another. After going back to the official docs, I realized that what was missing was an additional keyword, such as SET or TYPE. Examples:
作为带着同样问题来到这里的人，我不知道如何理解这些答案。我尝试了所有这些，但总是在“接近”一个或另一个术语时出现语法错误。回到官方文档后，我意识到缺少的是一个额外的关键字，例如 SET 或 TYPE。例子：
First this
首先这个
zuri=# ALTER TABLE newarts
ALTER COLUMN sunsetdate DATE NULL;
ERROR: syntax error at or near "DATE"
LINE 2: ALTER COLUMN sunsetdate DATE NULL;
zuri=# ALTER TABLE newarts
ALTER COLUMN sunsetdate DATE NULL;
ERROR: syntax error at or near "DATE"
LINE 2: ALTER COLUMN sunsetdate DATE NULL;
Then this:
然后这个：
zuri=# ALTER TABLE newarts
ALTER COLUMN sunsetdate TYPE DATE NULL;
ERROR: syntax error at or near "NULL"
LINE 2: ALTER COLUMN sunsetdate TYPE DATE NULL;
zuri=# ALTER TABLE newarts
ALTER COLUMN sunsetdate TYPE DATE NULL;
ERROR: syntax error at or near "NULL"
LINE 2: ALTER COLUMN sunsetdate TYPE DATE NULL;
Yes, there is still an error there but once I specified the meaning of DATE with the keyword TYPE the error was resolved and I moved on to another one. I had the same experience with the addition of SET (see the examples on the same page of the official docs I already cited).
是的，那里仍然有一个错误，但是一旦我用关键字 TYPE 指定了 DATE 的含义，错误就解决了，我转到了另一个。我在添加 SET 方面也有同样的经验（请参阅我已经引用的官方文档同一页面上的示例）。
As to the specific issue of NOT NULL, (especially as it relates to my issue of dates) I read this answerand it seemed to work - I didn't get an error message -
至于 NOT NULL 的具体问题（尤其是与我的日期问题有关），我阅读了这个答案，它似乎有效——我没有收到错误消息——
zuri=# update lili_code set sunsetdate=NULL; UPDATE 0
But then I read
但后来我读了
On successful completion, an UPDATE command returns a command tag of the form
成功完成后，UPDATE 命令将返回以下形式的命令标记
UPDATE count
The count is the number of rows updated. If count is 0, no rows matched the condition (this is not considered an error).
计数是更新的行数。如果计数为 0，则没有与条件匹配的行（这不被视为错误）。
Which is also in the official docs, here.
这也在官方文档中，here。
Finally, I turned to PGAdminIII, where I found that NOT NULL is a simple check box. Uncheck it, problem solved. I'm sure there is a way to make this work at the command line with psql, I just haven't found it.
最后，我转向 PGAdminIII，在那里我发现 NOT NULL 是一个简单的复选框。取消勾选，问题解决。我确定有一种方法可以使用 psql 在命令行中完成这项工作，只是我还没有找到。
I think some of the variation may also be due to the differences between ALTER and UPDATE (see this SO answerand my cheeky comment) as well as between ADDing new structures (as in the OP's question) and modifying data that is already there (as in mine). The moral of the story, read the official documentation. Don't scan it. Read it. And if you want to know more about NULL and NOT NULL, read this.
我认为一些变化也可能是由于 ALTER 和 UPDATE 之间的差异（参见这个 SO答案和我厚颜无耻的评论）以及添加新结构（如 OP 的问题）和修改已经存在的数据（如在我的）。故事的寓意，请阅读官方文档。不要扫描它。阅读。如果您想了解更多关于 NULL 和 NOT NULL 的信息，请阅读此。
