Menu
I have checked the documentation provided by Oracle and found a way to modify a constraint without dropping the table. Problem is, it errors out at modify as it does not recognize the keyword.
Using EMS SQL Manager for PostgreSQL.
Alter table public.public_insurer_credit MODIFY CONSTRAINT public_insurer_credit_fk1 deferrable, initially deferred; I was able to work around it by dropping the constraint using :
ALTER TABLE "public"."public_insurer_credit" DROP CONSTRAINT "public_insurer_credit_fk1" RESTRICT; ALTER TABLE "public"."public_insurer_credit" ADD CONSTRAINT "public_insurer_credit_fk1" FOREIGN KEY ("branch_id", "order_id", "public_insurer_id") REFERENCES "public"."order_public_insurer"("branch_id", "order_id", "public_insurer_id") ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED; 
675
To modify these properties, the constraint must be dropped and recreated. The column definition for a constraint cannot be modified, e.g. add new columns, drop existing columns, or change the order of columns. To make these types of changes, the constraint must be dropped and recreated.
An existing constraint cannot be modified. To define another column, or set of columns, as the primary key, the existing primary key definition must first be dropped, and then re-created.
The syntax to modify a column in a table in PostgreSQL (using the ALTER TABLE statement) is: ALTER TABLE table_name ALTER COLUMN column_name TYPE column_definition; table_name. The name of the table to modify.
alter table t drop constraint ck ; alter table t add constraint ck check (n < 0) enable novalidate; The enable novalidate clause will force inserts or updates to have the constraint enforced, but won't force a full table scan against the table to verify all rows comply.
There is no ALTER command for constraints in Postgres. The easiest way to accomplish this is to drop the constraint and re-add it with the desired parameters. Of course any change of the constraint will be run against the current table data.
BEGIN; ALTER TABLE t1 DROP CONSTRAINT ... ALTER TABLE t1 ADD CONSTRAINT ... COMMIT; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
