oracle 中的修改列 - 如何在设置为可为空之前检查列是否可为空?

2021-12-24 00:00:00 database schema oracle plsql

我试图替一位同事做一些 Oracle 工作，但遇到了一个障碍.在尝试编写脚本以将列修改为可为空时，我遇到了可爱的 ORA-01451 错误:

I'm trying to fill in for a colleague in doing some Oracle work, and ran into a snag. In attempting to write a script to modify a column to nullable, I ran into the lovely ORA-01451 error:

ORA-01451: column to be modified to NULL cannot be modified to NULL

发生这种情况是因为该列已经为 NULL.我们有几个需要更新的数据库，所以在我错误的假设中，我认为将它设置为 NULL 应该可以全面确保每个人都是最新的，无论他们是否手动将此列设置为可空.但是，这显然会导致某些已经将该列设为可为空的人出错.

This is happening because the column is already NULL. We have several databases that need to be udpated, so in my faulty assumption I figured setting it to NULL should work across the board to make sure everybody was up to date, regardless of whether they had manually set this column to nullable or not. However, this apparently causes an error for some folks who already have the column as nullable.

如何检查一列是否已经可以为空以避免错误?可以实现这个想法的东西:

How does one check if a column is already nullable so as to avoid the error? Something that would accomplish this idea:

IF( MyTable.MyColumn IS NOT NULLABLE)
   ALTER TABLE MyTable MODIFY(MyColumn  NULL);

推荐答案

您可以在 PL/SQL 中执行此操作:

You could do this in PL/SQL:

declare
  l_nullable user_tab_columns.nullable%type;
begin
  select nullable into l_nullable
  from user_tab_columns
  where table_name = 'MYTABLE'
  and   column_name = 'MYCOLUMN';

  if l_nullable = 'N' then
    execute immediate 'alter table mytable modify (mycolumn null)';
  end if;
end;

相关文章