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.
- Determine the database objects to which a security constraint is assigned.
- Find the tables and indexes that have the security constraint. See Finding Tables and Indexes with a Security Constraint.
- Find views that have the security constraint. See Finding Views that Include a Security Constraint.
- DROP the indexes identified in step 1a.
- 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 ;
- REPLACE all views identified in step 1b to remove the constraint column.
- Identify the users and profiles to which the constraint is assigned. See Finding Users or Profiles with an Assigned Constraint.
- Remove the security CONSTRAINT object assignments from:
- All users to whom the constraint is assigned, using the MODIFY USER statement. See Changing or Dropping Security Constraints in a MODIFY USER Statement.
- All profiles to which the constraint is assigned, using the MODIFY PROFILE statement. See Changing or Dropping Security Constraints in a Profile.
- After removing all assignments for a security constraint, you can either:
- Use the ALTER CONSTRAINT statement to change the constraint object.If you do not specify one of the available options in the ALTER CONSTRAINT statement, the CONSTRAINT retains the existing values for the option.
- Use the DROP CONSTRAINT statement to drop the constraint object.
For information about the ALTER CONSTRAINT and DROP CONSTRAINT statements, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
- Use the ALTER CONSTRAINT statement to change the constraint object.
- 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.