Altering or Dropping CONSTRAINT Objects - Advanced SQL Engine - Teradata Database

Security Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
ied1556235912841.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1100
lifecycle
previous
Product Category
Teradata Vantage™

The process for altering or dropping a CONSTRAINT object requires that you first remove all constraint assignments.

Modifying the name:value pairs in a CONSTRAINT object after a table that contains a corresponding constraint column is archived can have unintended effects on COPY and RESTORE operations. Such modifications may compromise security, depending on which parameters are changed. See Archive, Copy, and Restore Requirements.
  1. Determine the database objects to which a security constraint is assigned.
    1. Find the tables and indexes that have the security constraint. See Finding Tables and Indexes with a Security Constraint.
    2. Find views that have the security constraint. See Finding Views that Include a Security Constraint.
  2. DROP the indexes identified in step 1a.
  3. Remove the constraint columns from all tables identified in step 1a using the ALTER TABLE statement. For example:
    ALTER TABLE  table_name 
       DROP  constraint_column_name  ;
  4. REPLACE all views identified in step 1b to remove the constraint column.
  5. Identify the users and profiles to which the constraint is assigned. See Finding Users or Profiles with an Assigned Constraint.
  6. Remove the security CONSTRAINT object assignments from:
  7. After removing all assignments for a security constraint, you can either:
    • Use the ALTER CONSTRAINT statement to change the constraint object, for example:
      ALTER CONSTRAINT  constraint_name  AS
         [VALUES  value_name:integer_code  … [,value_name:integer_code]) ]
          [FUNCTION {ADD|DROP|REPLACE}  function_specification] ;
      If you do not specify one of the available options in the ALTER CONSTRAINT statement, the CONSTRAINT retains the existing values for the option.

      where:

      Syntax Element Description
      constraint_name The name of the CONSTRAINT object being altered.
      • VALUES value_name:Integer_code...
      • [,value_name:integer_code ]
      If the statement includes the VALUES option, the new set of name:value pairs replaces the old set.
      FUNCTION {ADD | DROP | REPLACE} function_specification Specifies the UDF being altered. For example:
      If the statement includes the FUNCTION option, you must specify either the ADD, DROP, or REPLACE:
      • The ADD option is valid only if the CONSTRAINT object does not contain the specified function type. You must enter the full function specification, for example:

        FUNCTION ADD

        INSERT SYSLIB. insert_udf_name

      • The DROP option does not affect the specified UDF, it only removes the UDF from the CONSTRAINT object. You only need to specify the SQL operation for the function, for example: INSERT.
      • The REPLACE option substitutes the specified UDF in place of the existing UDF of the same type in the CONSTRAINT object. You must enter the full function specification.
      You can alter only one function per ALTER CONSTRAINT statement.
    • Use the DROP CONSTRAINT statement to drop the constraint object, for example:
      DROP CONSTRAINT  constraint_name  ;
  8. After altering or dropping a constraint, reverse the actions done in steps 2 through 6 to reapply security constraints to tables, views, users and profiles.