SQL constraints can be used to enforce data integrity and boost speed. The optimizer can make queries easier to understand by using constraints. Constraints can help make data more predictable and accessible. In this page, you’ll find a list of ALTER commands that can be used to change or remove table constraints.
Statement-1:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column, …) DISABLE NOVALIDATE;
The above command is useful to add a primary key constraint on an existing table. DISABLE NOVALIDATE is the same as DISABLE. The constraint is not checked and is not necessarily true.
Statement-2:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name FOREIGN KEY (column, …)
REFERENCES table_name(column, …) DISABLE NOVALIDATE RELY;
The above command is useful to add a foreign key constraint on an existing table. DISABLE NOVALIDATE is the same as DISABLE. The constraint is not checked and is not necessarily true. Specify RELY to activate an existing constraint in NOVALIDATE mode for query rewrite in an unenforced query rewrite integrity mode.
In some circumstances, you’ll already know that the conditions for a constraint are true, so you won’t need to validate or enforce it. However, if you want to increase query optimization and efficiency, you might want to keep the constraint in place. It’s called a belief or RELY constraint when you utilize a constraint in this fashion, and the constraint must be in the RELY state. The RELY state allows you to specify that a constraint is considered to be true.
It’s worth noting that the RELY state only impacts unvalidated constraints.
Statement-3:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column, …) DISABLE NOVALIDATE;
The above command is useful to add a unique constraint on an existing table. Disable novalidate does not check that all existing data in the table conforms to the constraint.
Statement-4:
ALTER TABLE table_name
CHANGE COLUMN column_name column_name data_type
CONSTRAINT constraint_name NOT NULL ENABLE;
The above statement helps in changing the existing column name and adding a NOT NULL constraint to it. Since ENABLE is the default setting, leaving it unchecked has the same result. If you select DISABLE, the constraint will be disabled and will not be active.
Statement-5:
ALTER TABLE table_name
CHANGE COLUMN column_name column_name data_type
CONSTRAINT constraint_name DEFAULT default_value ENABLE;
The above statement helps in changing the existing column name and adding a DEFAULT constraint to it. Since ENABLE is the default setting, leaving it unchecked has the same result. If you select DISABLE, the constraint will be disabled and will not be active.
Statement-6:
ALTER TABLE table_name
CHANGE COLUMN column_name column_name data_type
CONSTRAINT constraint_name CHECK check_expression ENABLE;
The above statement helps in changing the existing column name and adding a CHECK constraint to it. Since ENABLE is the default setting, leaving it unchecked has the same result. If you select DISABLE, the constraint will be disabled and will not be active.
Statement-7:
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
The above command is used to drop the specific constraint.
Note:
ENABLE VALIDATE is the same as ENABLE. The constraint is checked and is guaranteed to hold for all rows.
ENABLE NOVALIDATE means the constraint is checked for new or modified rows, but existing data may violate the constraint.
DISABLE NOVALIDATE is the same as DISABLE. The constraint is not checked so data may violate the constraint.
DISABLE VALIDATE means the constraint is not checked but disallows any modification of the constrained columns.
I hope you found this post to be informative.
Please enter your email address to receive notifications of new postings.
Big Data and SQL 