Chapter 2 SQL command alter table (2)

Time:2022-1-6

Chapter 2 SQL command alter table (2)

Remove column restrictions

DROP COLUMNYou can delete multiple column definitions specified as comma separated lists. Each listed column name must be followed by itsRESTORYorCASCADE(if not specified, the default isRESTRICE)And%DELDATAor%NODELDATE(if not specified, the default is%NODELDATA)Options.

By default, deleting a column definition does not delete any data stored in the column from the data map.
To delete both column definitions and data, specify%DELDATAOptions.

Deleting a column definition does not delete the corresponding column level privileges.
For example, grant users permission to insert, update, or delete data on this column.
This will have the following consequences:

  • If you delete a column and then add another column with the same name, users and roles will have the same privileges on the new column as the old column.
  • After deleting a column, it is not possible to revoke the object privileges of the column.

For these reasons, it is generally recommended to use before deleting a column definitionREVOKECommand to revoke column level privileges from a column.

RESTRICTKeyword (or no keyword): if a column appears in an index, or is defined in a foreign key constraint or other unique constraint, the column cannot be deleted.
Try for this columnDROP COLUMNFailed withSQLCODE -322Wrong.
Default toRESTRICT

CASCADEKeyword: if the column appears in the index, the index will be deleted.
There may be multiple indexes.
If the column appears in a foreign key, the foreign key is deleted.
There may be multiple foreign keys.

If listed inCOMPUTECODEorCOMPUTEONCHANGEClause, the column cannot be deleted.
Trying to do this will result inSQLCODE -400Wrong.

Add constraints

You can add constraints to a comma separated list of fields.
For example, you can addUNIQUE (FName,SurName)Constraint, which is in two fieldsFNameandSurNameEstablish a unique constraint on the combined value of.
Similarly, you can add a primary key constraint or a foreign key constraint to a comma separated list of fields.

Constraints can be named or unnamed.
If unnamed, SQL generates the constraint name using the table name.
For example,MYTABLE_Unique1orMYTABLE_PKEY1

The following example creates two unnamed constraints and adds a unique constraint and a primary key constraint to a comma separated list of fields:

 ALTER TABLE SQLUser.MyStudents 
    ADD UNIQUE (FName,SurName),PRIMARY KEY (Fname,Surname)  
  • Field must exist to be used in a constraint.
    Specifies that fields that do not exist will be generatedSQLCODE -31Wrong.
  • Cannot be used in constraintsRowIdField. appointRowId(ID)Fields are generatedSQLCODE-31Wrong.
  • Flow fields cannot be used in constraints. The specified stream field generatesSQLCODE-400Error:“invalid index attribute”
  • A constraint can only be applied to a field once. Specifying the same constraint on a field twice generatesSQLCODE-400Error:“index name conflict”

By using the optionalCONSTRAINTIdentifier keyword clause, you can create a naming constraint. The naming constraint must be a valid identifier; Constraint names are not case sensitive. This provides a name for the constraint for future use. This is shown in the following example:

 ALTER TABLE SQLUser.MyStudents 
    ADD CONSTRAINT UnqFullName UNIQUE (FName,SurName)  

You can specify multiple constraints as comma separated lists; The constraint name is applied to the first constraint, and other constraints receive the default name.

Constraint names must be unique for tables. Specifying the same constraint name twice for a field generatesSQLCODE -400Error:“index name conflict”

Add primary key limit

The primary key value is required and unique. Therefore, adding a primary key constraint to an existing field or combination of fields makes each of these fields mandatory. If you add a primary key constraint to an existing field list, the combined values of these fields must be unique. If an existing field allows null values, you cannot add a primary key constraint to the field. If a field (or field list) contains non unique values, you cannot add a primary key constraint to the field (or field list).

If you add a primary key constraint to an existing field, the field may also be automatically defined asIDKeyIndexes. This depends on whether the data exists and the configuration settings established in one of the following ways:

  • SQL SET OPTION PKEY_IS_IDKEYsentence.
  • System wide$SYSTEM.SQL.Util.SetOption()Method configuration optionsDDLPKeyNotIDKey。 To determine the current settings, call$SYSTEM.SQL.CurrentSettings(), it shows throughDDLThe primary key is created instead ofIDKey; The default value is1
  • Enter the management portal and select system management, configuration, SQL and object settings, SQL.
    View passedDDLThe primary key of the created table is defined as the current setting of the ID key.
    • If the check box is not selected (the default setting), the primary key will not become a key in the class definitionIDKeyIndexes. Use notIDKEYThe efficiency of primary key access record is much lower; However, this type of primary key value can be modified.
    • If this check box is selected, whenDDLWhen a primary key constraint is specified and the field does not contain data, the primary key index is also defined asIDKeyIndexes. If the field contains data, it is not definedIDKeyIndexes. If the primary key is defined asIDKeyIndex, the data access efficiency is higher, but once the primary key value is set, it can never be modified.

IfCREATE TABLEDefines the bitmap area index, and then usesALTER TABLEAdding is alsoIDKeyThe system will automatically delete the bitmap area index.

Add primary key when it already exists

Only one primary key can be defined. By default, when the primary key already exists, iris refuses to define the primary key, or refuses to define the same primary key twice, and sends a messageSQLCODE-307Wrong. An sqlcode-307 error is issued even if the second definition of the primary key is the same as the first definition. To determine the current configuration, call$SYSTEM.SQL.CurrentSettings(), this function shows that the key is allowed to pass through when it existsDDLCreate primary key settings. The default value is 0 (no), which is the recommended configuration setting. If this option is set to1(yes),ALTER TABLE ADD PRIMARY KEYWill cause iris to delete the primary key index from the class definition and then recreate the index with the specified primary key field.

In the management portal, system management, configuration, SQL and object settings, ignore redundancy by checkingDDLStatement check box to set this option (and other similar create, change, and delete options) on a system wide basis.

However, even if this option is set to allow the creation of a primary key when the primary key already exists, if the primary key index is the sameIDKEYIf the index table contains data, you cannot recreate the primary key index. Attempting to do so will generateSQLCODE-307Wrong.

Add foreign key restrictions

By default, you cannot have two foreign keys with the same name. Doing so will generateSQLCODE-311Wrong. To determine the current settings, call$SYSTEM.SQL.CurrentSettings(), it will display “allow when foreign keys exist”DDLAdd foreign key constraint settings. The default value is0(no), this is the recommended setting for this option. by1(yes), you can add a foreign key through DDL even if a foreign key with the same name already exists.

In the management portal, system management, configuration, SQL, and object settings, you can set this option (and other similar creation, change, and deletion options) system wide by selecting the ignore redundant DDL statements check box.

The table definition should not have two foreign keys with different names. These two foreign keys refer to the same field – public field and perform contradictory reference operations. According to ANSI standards, if two foreign keys (for example,ON DELETE CASCADEandON DELETE SET NULL), SQL does not issue errors. On the contrary, whenDELETEorUPDATESQL issues an error when the operation encounters these conflicting foreign key definitions.

Adding foreign keys that specify non-existent foreign key fields generatesSQLCODE-31Wrong.

An added foreign key that references a parent key table that does not exist is generatedSQLCODE-310Wrong. An added foreign key that references a field that does not exist in the existing parent key table is generatedSQLCODE-316Wrong. If no parent key field is specified, the default isIDField.

At issueADDBefore using a foreign key, the user must have permission for the referenced table or the columns of the referenced tableREFERENCESPrivileges. If executed through dynamic SQL or xdbcALTER TABLE, you needREFERENCESjurisdiction.

An added foreign key that references a field (or combination of fields) that can take a non unique value is generatedSQLCODE-314Error and pass%msgProvide more details.

NO ACTIONIt is the only reference operation supported by the shard table.

When data already exists in the table,ADDForeign keys will be constrained.
To change this default constraint behavior, refer toSET optionImperativeCOMPILEMODE=NOCHECKOptions.

When defined for a single fieldADD FOREIGN KEYConstraints and foreign keys reference tablesidkey时, IRIS将外键中的属性转换为引用属性。
此转换受以下限制:

  • 该表不能包含任何数据。
  • 外键上的属性不能是持久类(也就是说,它不能已经是引用属性)。
  • 外键字段与引用的idkey字段的数据类型和数据类型参数必须相同。
  • 外键字段不能是IDENTITY字段。

减少约束限制

默认情况下,如果外键约束引用唯一键约束或主键约束,则不能删除该约束。这样做会导致SQLCODE -317错误。要更改此默认外键约束行为,请参考SEToption命令的COMPILEMODE=NOCHECK选项。

删除主键约束的效果取决于主键也是ID键设置的设置(如上所述):

  • 如果PrimaryKey索引不是IDKey索引,则删除PRIMARY KEY约束将删除索引定义。
  • 如果PrimaryKey索引也是IDKey索引,并且表中没有数据,则删除PRIMARY KEY约束将删除整个索引定义。
  • 如果PrimaryKey索引也是IDKey索引,并且表中有数据,则删除PRIMARYKEY约束只会从IDKey索引定义中删除PRIMARYKEY限定符。

不存在时删除约束

默认情况下,IRIS拒绝在没有该约束的字段上删除字段约束的尝试,并发出SQLCODE-315错误。要确定当前设置,请调用$SYSTEM.SQL.CurrentSettings(),它显示允许DDL丢弃不存在的约束设置。默认值为0(否),这是推荐设置。如果此选项设置为1(是),ALTER TABLE DROP CONSTRAINT将导致 IRIS不执行任何操作,也不发出错误消息。

在管理门户、系统管理、配置、SQL和对象设置中,通过选中忽略冗余DDL语句复选框,可以在系统范围内设置此选项(以及其他类似的创建、更改和删除选项)。

示例

以下示例使用嵌入式SQL程序创建表,填充两行,然后更改表定义。

为了演示这一点,请按显示的顺序运行前两个嵌入式SQL程序。(这里有必要使用两个嵌入式SQL程序,因为除非引用的表已经存在,否则嵌入式SQL无法编译INSERT语句。)

ClassMethod AlterTable()
{
    DO $SYSTEM.Security.Login("_SYSTEM","SYS")
    &sql(
        DROP TABLE SQLUser.MyStudents
    )
    IF SQLCODE = 0 { 
        WRITE !,"已删除表" 
    } ELSE { 
        WRITE "DROP TABLE错误SQLCODE=",SQLCODE 
    }
    &sql(
        CREATE TABLE SQLUser.MyStudents 
        (
            FirstName VARCHAR(35) NOT NULL,
            LastName VARCHAR(35) NOT NULL
        )
    )
    IF SQLCODE = 0 { 
        WRITE !,"已创建表" 
    } ELSE { 
        WRITE "CREATE TABLE错误SQLCODE=",SQLCODE 
    }
}
ClassMethod AlterTable1()
{
    DO $SYSTEM.Security.Login("_SYSTEM","SYS")
    NEW SQLCODE, %msg
    &sql(
        INSERT INTO SQLUser.MyStudents 
        (
            FirstName, LastName
        ) 
        VALUES 
        (
            'Yao','Vanderbilt'
        )
    )
    IF SQLCODE = 0 { 
        WRITE !,"Inserted data in table"
    } ELSE { 
        WRITE !,"SQLCODE=",SQLCODE,": ",%msg 
    }
    &sql(
        INSERT INTO SQLUser.MyStudents 
        (
            FirstName, LastName
        )
        VALUES 
        (
            'Xin','Smith'
        )
    )
    IF SQLCODE = 0 { 
        WRITE !,"Inserted data in table"
    } ELSE { 
        WRITE !,"SQLCODE=",SQLCODE,": ",%msg 
    }
}

下面的示例使用ALTER TABLE添加ColorPreference列。
因为列定义指定了默认值,所以系统会为表中已有的两行填充ColorPreference的值'Blue':

ClassMethod AlterTable2()
{
    NEW SQLCODE,%msg
    &sql(
        ALTER TABLE SQLUser.MyStudents 
        ADD COLUMN ColorPreference VARCHAR(16) NOT NULL DEFAULT 'Blue'
    )
    IF SQLCODE = 0 {
        WRITE !,"添加一列",! 
    } ELSEIF SQLCODE = -306 {
        WRITE !,"SQLCODE=",SQLCODE,": ",%msg 
    } ELSE { 
        WRITE "SQLCODE error=",SQLCODE 
    }
}

下面的示例使用ALTER TABLE添加两个计算列:FLNameLFName
对于已存在的行,这些列没有值。
对于任何随后插入的行,将为每一列计算一个值:

ClassMethod AlterTable3()
{
    NEW SQLCODE,%msg
    &sql(
        ALTER TABLE SQLUser.MyStudents 
            ADD COLUMN FLName VARCHAR(71) COMPUTECODE 
            { 
                SET {FLName}={FirstName}_" "_{LastName}
            } 
            COMPUTEONCHANGE 
            (
                FirstName,LastName
            ),
            COLUMN LFName VARCHAR(71) COMPUTECODE 
            { 
                SET {LFName}={LastName}_ "," _{FirstName}
            } 
            COMPUTEONCHANGE 
            (
                FirstName,LastName
            ) 
        )
    IF SQLCODE=0 {
        WRITE !,"添加两个计算列",! 
    } ELSE { 
        WRITE "SQLCODE error=",SQLCODE 
    }
}

DDL创建的 User.MyStudents 表:

Class User.MyStudents Extends %Persistent [ ClassType = persistent, DdlAllowed, Final, Owner = {yx}, ProcedureBlock, SqlRowIdPrivate, SqlTableName = MyStudents ]
{

Property FirstName As %Library.String(MAXLEN = 35) [ Required, SqlColumnNumber = 2 ];

Property LastName As %Library.String(MAXLEN = 35) [ Required, SqlColumnNumber = 3 ];

Property ColorPreference As %Library.String(MAXLEN = 16) [ InitialExpression = "Blue", Required, SqlColumnNumber = 4 ];

Property FLName As %Library.String(MAXLEN = 71) [ SqlColumnNumber = 5, SqlComputeCode = {   SET {FLName}={FirstName}_" "_{LastName}
}, SqlComputed ];

Property LFName As %Library.String(MAXLEN = 71) [ SqlColumnNumber = 6, SqlComputeCode = {   SET {LFName}={LastName}_ "," _{FirstName}
}, SqlComputed ];

/// Bitmap Extent Index auto-generated by DDL CREATE TABLE statement.  Do not edit the SqlName of this index.
Index DDLBEIndex [ Extent, SqlName = "%%DDLBEIndex", Type = bitmap ];

}

Chapter 2 SQL command alter table (2)

image.png