This is the first day of my participation in the September Challenge

ALTER TABLE ALTER TABLE

Delete column limit

DROP COLUMN removes multiple COLUMN definitions specified as comma-separated lists. Each listed column name must be followed by its RESTORY or CASCADE(default to RESTRICE if not specified) and %DELDATA or %NODELDATE(default to %NODELDATA if not specified) options.

By default, deleting a column definition does not remove any data stored in that column from the data map. To delete both the column definition and the data, specify the %DELDATA option.

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

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

For these reasons, it is usually recommended to REVOKE column-level privileges from a column using REVOKE before deleting a column definition.

RESTRICT Key (or no key): This column cannot be dropped if it is listed in an index or if it is defined in a foreign key constraint or other unique constraint. Failed to DROP COLUMN for this COLUMN with SQLCODE -322 error. The default value is RESTRICT.

CASCADE keyword: If the list is in the index, the index will be dropped. There may be multiple indexes. If listed in a foreign key, the foreign key is removed. There may be multiple foreign keys.

If a column is used in a COMPUTECODE or COMPUTEONCHANGE clause, it cannot be deleted. Attempting to do so results in a SQLCODE -400 error.

Add constraints

You can add constraints to a comma-separated list of fields. For example, you can add a UNIQUE (FName,SurName) constraint that establishes a UNIQUE constraint on the combined value of two fields FName and SurName. Similarly, you can add primary or foreign key constraints to a comma-separated list of fields.

Constraints may or may not be named. If not named, SQL will use the table name to generate the constraint name. For example, MYTABLE_Unique1 or MYTABLE_PKEY1.

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

 ALTER TABLE SQLUser.MyStudents 
    ADD UNIQUE (FName,SurName),PRIMARY KEY (Fname,Surname)  
Copy the code
  • Fields must exist to be used in a constraint.

Specifying a field that does not exist generates a SQLCODE -31 error.

  • Cannot be used in constraintsRowIdField. The specifiedRowId(ID)The field will generateSQLCODE-31Error.
  • You cannot use flow fields in a constraint. Specifies that the stream field is generatedSQLCODE-400Error:"Invalid index attribute"
  • A constraint can only be applied once to a field. Specifying the same constraint on a field twice generatesSQLCODE-400Error:"The index name conflict".

You can create a named CONSTRAINT by using the optional CONSTRAINT identifier keyword clause. Naming constraints must be valid identifiers; Constraint names are case insensitive. 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)  
Copy the code

Multiple constraints can be specified as comma-separated lists; The constraint name is applied to the first constraint, and the other constraints receive the default name.

The constraint name must be unique to the table. Specifying the same constraint name for a field twice generates a SQLcode-400 error: “Index name conflict”.

Add primary key restrictions

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 those fields a required field. If you add a primary key constraint to a list of existing fields, the combined values of those fields must be unique. You cannot add a primary key constraint to an existing field that allows null values. A primary key constraint cannot be added to a field (or list of fields) that contains non-unique values.

If you add a primary key constraint to an existing field, that field may also be automatically defined as an IDKey index. This depends on whether the data exists and the configuration Settings set up in one of the following ways:

  • SQL SET OPTION PKEY_IS_IDKEYStatements.
  • 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.
  • Go to the UMP and choose System Management, Configuration, SQL and Object Settings, SQL.

View the current Settings for defining primary keys as ID keys for tables created through DDL.

  • If this check box is not selected (the default), the primary key will not be in the class definitionIDKeyThe index. Use is notIDKEYPrimary key access records are much less efficient; However, the primary key value of this type can be modified.
  • If this check box is selected, theDDLWhen a primary key constraint is specified and the field contains no data, the primary key index is also defined asIDKeyThe index. Undefined if the field contains dataIDKeyThe index. If the primary key is defined asIDKeyIndex, which makes data access more efficient, but the primary key value, once set, can never be changed.

If CREATE TABLE defines a bitmap index and then uses ALTER TABLE to add a primary key that is also the IDKey, the system automatically deletes the bitmap index.

Add a primary key if it already exists

Only one primary key can be defined. By default, IRIS refuses to define a primary key when it already exists, or refuses to define the same primary key twice and issues a SQLCODE-307 error. Even if the second definition of the primary key is the same as the first, an SQLCODE-307 error is issued. To determine the current configuration, please call $SYSTEM. SQL. CurrentSettings (), the function display when there is key allows through DDL to create primary key Settings. The default is 0(no), which is the recommended configuration setting. If this option is set to 1(yes), ALTER TABLE ADD PRIMARY KEY will cause IRIS to remove the PRIMARY KEY index from the class definition and then recreate the index using the specified PRIMARY KEY field.

This option (and other similar create, change, and delete options) can be set system-wide by selecting the ignore redundant DDL statement check box in the administrative portal, system administration, configuration, SQL, and object Settings.

However, even if this option is set to allow the creation of a primary key if it already exists, the primary key index cannot be recreated if it is also an IDKEY index and the table contains data. Attempting to do so generates a SQLCODE-307 error.

Add foreign key restrictions

By default, you cannot have two foreign keys with the same name. Doing so generates a SQLCODE-311 error. To determine the current Settings, please call $SYSTEM. SQL. CurrentSettings (), it will show “when there are foreign keys allow foreign key constraints DDL” Settings. The default is 0(no), which is the recommended setting for this option. If the value is 1(yes), you can add a foreign key through DDL even if it already has the same name.

This option (and other similar create, change, and delete options) can be set system-wide by selecting the ignore redundant DDL statement check box in the administrative portal, system administration, configuration, SQL, and object Settings.

Table definitions should not have two foreign keys with different names that refer to the same field-public field and perform contradictory reference operations. According to ANSI, SQL does not issue an error if you define two foreign keys that perform contradictory reference operations ON the same field (for example, ON DELETE CASCADE and ON DELETE SET NULL). Conversely, SQL issues errors when DELETE or UPDATE operations encounter these conflicting foreign key definitions.

Adding a foreign key that specifies a non-existent foreign key field generates a SQLCODE-31 error.

Adding a foreign key that references a nonexistent parent key table generates a SQLCODE-310 error. Adding a foreign key that references a field that does not exist in the existing parent key table generates a SQLCODE-316 error. If the parent key field is not specified, the default is the ID field.

Before issuing the ADD foreign key, the user must have the REFERENCES privilege on the referenced table or columns of the referenced table. If you perform ALTER TABLE using dynamic SQL or xDBC, you need the REFERENCES permission.

Adding foreign keys that reference fields (or combinations of fields) that can take on non-unique values generates a SQLcode-314 error and provides more details via % MSG.

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

The ADD foreign key is constrained when data already exists in the table. To change this default constraint behavior, refer to the SET option command COMPILEMODE=NOCHECK option.

When the ADD FOREIGN KEY constraint is defined for a single field and the FOREIGN KEY references the IDkey of the reference table, IRIS converts attributes in the FOREIGN KEY to reference attributes. This conversion is subject to the following restrictions:

  • This table cannot contain any data.
  • An attribute on a foreign key cannot be a persistent class (that is, it cannot already be a reference attribute).
  • Foreign key field with reference toidkeyThe data type and data type parameters of the field must be the same.
  • The foreign key field cannot beIDENTITYField.

Reduce constraints

By default, a foreign key constraint cannot be removed if it references a unique key constraint or a primary key constraint. Doing so causes a SQLCODE -317 error. To change this default foreign key constraint behavior, refer to COMPILEMODE=NOCHECK of the SEToption command.

The effect of removing the primary key constraint depends on the setting where the primary key is also set by the ID key (as described above) :

  • ifPrimaryKeyThe index is notIDKeyIndex is deletedPRIMARY KEYThe constraint deletes the index definition.
  • ifPrimaryKeyThe index is alsoIDKeyIndex, and there is no data in the tablePRIMARY KEYConstraint deletes the entire index definition.
  • ifPrimaryKeyThe index is alsoIDKeyIndex, and there is data in the table, deletePRIMARYKEYConstraints will only start fromIDKeyDelete from index definitionPRIMARYKEYQualifiers.

Delete constraint when it does not exist

By default, IRIS rejects attempts to remove a field constraint on a field that does not have the constraint and issues a SQLCODE-315 error. To determine the current Settings, please call $SYSTEM. SQL. CurrentSettings (), it shows that allows DDL does not exist the constraint set. The default is 0(no), which is recommended. If this option is set to 1(yes), the ALTER TABLE DROP CONSTRAINT causes IRIS to do nothing and issue no error messages.

This option (and other similar create, change, and delete options) can be set system-wide by selecting the ignore redundant DDL statement check box in the administrative portal, system administration, configuration, SQL, and object Settings.

The sample

The following example uses an embedded SQL program to create a table, populate two rows, and then change the table definition.

To demonstrate this, run the first two embedded SQL programs in the order shown. (It is necessary to use two embedded SQL programs here, because embedded SQL cannot compile INSERT statements unless the referenced table already exists.)

ClassMethod AlterTable(a)
{
	DO $SYSTEM.Security.Login("_SYSTEM"."SYS")
	&sql(
		DROP TABLE SQLUser.MyStudents
	)
	IF SQLCODE = 0{ WRITE ! ."Dropped table" 
	} ELSE { 
		WRITE "DROP TABLE error SQLCODE=",SQLCODE 
	}
	&sql(
		CREATE TABLE SQLUser.MyStudents 
		(
			FirstName VARCHAR(35) NOT NULL,
			LastName VARCHAR(35) NOT NULL
		)
	)
	IF SQLCODE = 0{ WRITE ! ."Created table" 
	} ELSE { 
		WRITE "CREATE TABLE error SQLCODE=",SQLCODE 
	}
}
Copy the code
ClassMethod AlterTable1(a)
{
	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 
	}
}

Copy the code

The following example uses ALTER TABLE to add the ColorPreference column. Since the column definition specifies the default value, the system fills the existing two rows in the table with the value ‘Blue’ of ColorPreference:

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

Copy the code

The following example uses ALTER TABLE to add two computed columns :FLName and LFName. These columns have no values for existing rows. For any subsequently inserted rows, a value is computed for each column:

ClassMethod AlterTable3(a)
{
	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 ! ."Add two computed columns",! 
	} ELSE { 
		WRITE "SQLCODE error=",SQLCODE 
	}
}
Copy the code

DDL create user.myStudents table

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 ];

}

Copy the code