ADD Column Clause

Use the ADD Column clause to add a column to a table, and to define constraints on the new column. This clause can also associate a security policy with a table that has no security policy, or can specify a security label for the new column, if the table already has a security policy.

This syntax fragment is part of the ALTER TABLE statement.

(1)
ADD Column Clause

1  ADD
1  ( + , %New Column )
1 %New Column
New Column

1  new_column  %Data Type1
1+ 
1? 2 %DEFAULT Clause3
1? 2 %Single-Column Constraint Format4
2?  BEFORE column5
2?  %Add Column SECURED WITH label clause6
Element Description Restrictions Syntax
column Name of column before which new_column is to be placed Must already exist in the table Identifier
new_column Name of column that you are adding You cannot add a serial column if the table contains data Identifier
The following restrictions apply to the ADD clause:
  • You cannot add a serial column to a table that contains data.
  • You cannot add columns beyond the maximum row size of 32,767 bytes.

Restrictions on adding columns of type IDSSECURITYLABEL

The following restrictions affect the use of the ADD Column clause to add a column of the IDSSECURITYLABEL data type to support a label-based access control (LBAC) security policy:
  • If the table has no security policy, a user who holds the DBSECADM role must also include the ADD SECURITY POLICY keywords to specify an LBAC security policy, as in this example:
    CREATE TABLE tA
       (Col_1 BIGINT
        Col_2 INTERVAL(YEAR TO MONTH);  
        Col_3 VARCHAR (255));  
     . . .
    ALTER TABLE tA
      ADD (Col_4 IDSSECURITYLABEL DEFAULT 'label4'), --to add this column 
      ADD SECURITY POLICY Watchdog;   --a security policy is also required
                                  -- and 'label4' must be a Watchdog label
    
  • Only a user who holds the DBSECADM role can add a column of type IDSSECURITYLABEL.
  • A table can have at most one column of type IDSSECURITYLABEL.
  • The IDSSECURITYLABEL column cannot have SECURED WITH column protection.
  • The IDSSECURITYLABEL column has an implicit NOT NULL constraint by default. If no label name for the default security label is specified in the DEFAULT clause, the default value for this column is the security label for write access that is held by the current user.
  • The IDSSECURITYLABEL column cannot have any explicit single-column constraints, and it cannot be part of multiple-column referential or check constraints.

For the ALTER TABLE syntax to add or drop row-level LBAC protection for the table, see SECURITY POLICY Clause.

For the ALTER TABLE syntax to add column-level protection for a new column in a table already protected by an LBAC security policy, see Add column SECURED WITH label clause.

For the ALTER TABLE syntax to add or drop column-level LBAC protection for an existing column, see Modify Column Security clause.