Alters a constraint.
1 – Environment
You can use the ALTER CONSTRAINT statement:
o In interactive SQL
o Embedded in host language programs
o As part of a procedure in an SQL module or other compound
statement
o In dynamic SQL as a statement to be dynamically executed
2 – Format
(B)0[m[1;4mALTER[m [1;4mCONSTRAINT[m[1m <constraint-name> qqqqqqqqqk[m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj[m
[1m [m [1mmqqwqqwqq> [1;4mCOMMENT[m[1m IS[m [1mwq> '<text-literal>' qwqqwqqwqq> [m
[1m [m [1m x x[m [1m mqqqqqqq / <qqqqqqqqqqj [m [1mx[m [1mx[m [1m [m
[1mx[m [1mtqq> constraint-attributes qqqqqqqqqqqqqqu[m [1mx[m
[1mx[m [1mmqq> [1;4mRENAME[m[1m [1;4mTO[m[1m <new-constraint-name> qqqqj[m [1mx[m
[1mmqqqqqqqqqqqqqqqqqqqq <qqqqqqqqqqqqqqqqqqqqqqqqj[m
(B)0[m[1mconstraint-attributes = [m
[1m [m
[1mqwq> [1;4mDEFERRABLE[m[1m qqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwq> [m
[1m x mq> [1;4mINITIALLY[m[1m wq> [1;4mIMMEDIATE[m[1m qqwj x [m
[1m x mq> [1;4mDEFERRED[m[1m qqqj x [m
[1m tq> [1;4mNOT[m[1m [1;4mDEFERRABLE[m[1m qqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqu [m
[1m x mq> [1;4mINITIALLY[m[1m [1;4mIMMEDIATE[m[1m qqj x [m
[1m tq> [1;4mINITIALLY[m[1m [1;4mIMMEDIATE[m[1m qqqqwqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqu [m
[1m x tq> [1;4mDEFERRABLE[m[1m qqqqqu x [m
[1m x mq> [1;4mNOT[m[1m [1;4mDEFERRABLE[m[1m qj x [m
[1m mq> [1;4mINITIALLY[m[1m [1;4mDEFERRED[m[1m qqqqqwqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqj [m
[1m mq> [1;4mDEFERRABLE[m[1m qqqqqj [m
3 – Arguments
3.1 – COMMENT_IS
Adds a comment about the constraint. SQL displays the text of the
comment when it executes a SHOW CONSTRAINTS statement. Enclose
the comment in single quotation marks ( ') and separate multiple
lines in a comment with a slash mark (/).
3.2 – constraint-attributes
See the ALTER TABLE statement.
3.3 – constraint-name
The name of the table whose definition you want to change.
3.4 – RENAME_TO
Changes the name of the constraint being altered. See the the
RENAME statement for further discussion. If the new name is the
name of a synonym then an error will be raised.
The RENAME TO clause requires synonyms be enabled for this
database. Refer to the ALTER DATABASE statement SYNONYMS ARE
ENABLED clause. Note that these synonyms may be deleted if they
are no longer used by database definitions or applications.
4 – Example
This example shows how ALTER CONSTRAINT can be used to change the
constraint attributes and add a comment to a constraint.
SQL> set dialect 'sql99';
SQL> attach 'file db$:mf_personnel';
SQL>
SQL> create table PERSON
cont> (last_name char(20)
cont> constraint MUST_HAVE_LAST_NAME
cont> not null
cont> deferrable,
cont> first_name char(20),
cont> birthday date
cont> constraint MUST_BE_IN_PAST
cont> check (birthday < current_date)
cont> not deferrable,
cont> constraint ALL_UNIQUE
cont> unique (last_name, first_name, birthday)
cont> deferrable initially immediate
cont> );
SQL>
SQL> show table (constraint) PERSON
Information for table PERSON
Table constraints for PERSON:
ALL_UNIQUE
Unique constraint
Null values are considered distinct
Table constraint for PERSON
Evaluated on each VERB
Source:
UNIQUE (last_name, first_name, birthday)
MUST_BE_IN_PAST
Check constraint
Column constraint for PERSON.BIRTHDAY
Evaluated on UPDATE, NOT DEFERRABLE
Source:
CHECK (birthday < current_date)
MUST_HAVE_LAST_NAME
Not Null constraint
Column constraint for PERSON.LAST_NAME
Evaluated on COMMIT
Source:
PERSON.LAST_NAME NOT null
Constraints referencing table PERSON:
No constraints found
SQL>
SQL> alter constraint ALL_UNIQUE
cont> deferrable initially deferred;
SQL>
SQL> alter constraint MUST_HAVE_LAST_NAME
cont> comment is 'We must assume all persons have a name'
cont> not deferrable;
SQL>
SQL> alter constraint MUST_BE_IN_PAST
cont> deferrable initially immediate;
SQL>
SQL> show table (constraint) PERSON
Information for table PERSON
Table constraints for PERSON:
ALL_UNIQUE
Unique constraint
Null values are considered distinct
Table constraint for PERSON
Evaluated on COMMIT
Source:
UNIQUE (last_name, first_name, birthday)
MUST_BE_IN_PAST
Check constraint
Column constraint for PERSON.BIRTHDAY
Evaluated on each VERB
Source:
CHECK (birthday < current_date)
MUST_HAVE_LAST_NAME
Not Null constraint
Column constraint for PERSON.LAST_NAME
Evaluated on UPDATE, NOT DEFERRABLE
Comment: We must assume all persons have a name
Source:
PERSON.LAST_NAME NOT null
Constraints referencing table PERSON:
No constraints found
SQL>
SQL> commit;