如何编写Alter Table并添加新列？ [英] How write Alter Table and add new columns?

问题描述

我有一个表，表中有3列A，B，C，其中也有行。列A是主键。

I have a table that has 3 columns A,B,C which has rows also. Column A is the primary key.

现在，根据新要求，我需要添加新列D，E和F。

Now as per new requirement I need to add new column D, E and F.

我还需要从A列中删除先前的主键，并为D列添加新的主键。

Also i need to remove the previous primary key from column A and add a new primary key for column D.

E列和F为NULL。

Column E and F is NULL.

请帮助我创建alter table语句。

Please help me to create alter table statement.

推荐答案

您需要什么是一个多步骤的过程。添加列，删除现有的主键约束，最后添加新的约束。

What you require is a multi-step process. Adding the columns, dropping the existing primary key constraint and finally adding a new one.

这里最困难的事情是添加列D。因为您希望它是新的主键，它必须为非空。如果表中已有数据，则需要处理以下错误：

The most difficult thing here is adding column D. Because you want it to be the new primary key it will have to be NOT NULL. If your table has existing data you will need to handle this error:

SQL> alter table your_table
  2     add ( d number not null
  3           , e date
  4           , f number )
  5  /
alter table your_table
            *
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column


SQL>

因此，第1步是添加带有D可选的新列；然后使用任何键值填充它：

So, step 1 is add the new columns with D optional; then populate it with whatever key values:

SQL> alter table your_table
  2     add ( d number
  3           , e date
  4           , f number )
  5  /

Table altered.

SQL> update your_table
  2      set d = rownum
  3  /

1 row updated.

SQL>

现在我们可以将D列设为必填项：

Now we can make column D mandatory:

SQL> alter table your_table
  2     modify d not null
  3  /

Table altered.

SQL>

最后，我们可以将主键列从A更改为D：

Finally, we can change the primary key column from A to D:

SQL> alter table your_table
  2      drop primary key
  3  /

Table altered.

SQL> alter table your_table
  2     add constraint yt_pk primary key (d)
  3  /

Table altered.

SQL>

---

对于某些更改，我们想添加一个列默认值。在这种情况下，可以一步完成：

---

For some alterations we want to add a column with a default value. In this scenario it is possible to do so in one step:

alter table your_table
add new_col varchar2(1) default 'N' not null;

在Oracle的更高版本中，用相同的值填充新列实际上非常高效，比上面概述的多步骤方法快得多。

In later versions of Oracle this is actually an extremely efficient of populating the new column with the same value, considerably faster than the multi-step approach outlined above.

如果不清楚上面的语法是Oracle。我希望SQL Server与之类似。

In case it's not clear the above syntax is Oracle. I expect SQL Server will be something similar.

这篇关于如何编写Alter Table并添加新列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！