Add or disable a constraint (FK/CHECK/UNIQUE)

Oracle

-- Add a foreign key
ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customer(id);
-- CHECK
ALTER TABLE product ADD CONSTRAINT chk_price CHECK (price >= 0);
-- Disable / enable
ALTER TABLE orders DISABLE CONSTRAINT fk_customer;
ALTER TABLE orders ENABLE CONSTRAINT fk_customer;

Oracle constraints can be temporarily turned off with DISABLE/ENABLE (useful for bulk loads). ENABLE VALIDATE checks existing rows; NOVALIDATE does not.

PostgreSQL

-- Foreign key and CHECK
ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customer(id);
ALTER TABLE product ADD CONSTRAINT chk_price CHECK (price >= 0);

-- Instead of disabling the FK: NOT VALID + validate later
ALTER TABLE orders ADD CONSTRAINT fk2 FOREIGN KEY (c_id) REFERENCES customer(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT fk2;
ALTER TABLE orders DROP CONSTRAINT fk2;

PostgreSQL has no constraint DISABLE. To reduce locking on a large table, add the constraint with NOT VALID and VALIDATE it later. ALTER TABLE ... DISABLE TRIGGER ALL also disables the FK's internal triggers and requires superuser; data inserted while they are off is never validated, and re-enabling does not check retroactively (risk of silent integrity loss). Instead, DROP the constraint, re-add it with NOT VALID, then run VALIDATE CONSTRAINT.

MySQL

-- Foreign key and CHECK
ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customer(id);
ALTER TABLE product ADD CONSTRAINT chk_price CHECK (price >= 0);

-- Temporarily disable FK checks for the session
SET FOREIGN_KEY_CHECKS = 0;
-- ... bulk operation ...
SET FOREIGN_KEY_CHECKS = 1;

Requires InnoDB. Instead of disabling a single constraint, the session-level SET FOREIGN_KEY_CHECKS=0 is used. CHECK constraints are actually enforced starting with MySQL 8.0.16 (they were ignored before).

SQL Server

-- Foreign key and CHECK
ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customer(id);
ALTER TABLE product ADD CONSTRAINT chk_price CHECK (price >= 0);

-- Disable / re-enable (validate with WITH CHECK)
ALTER TABLE orders NOCHECK CONSTRAINT fk_customer;
ALTER TABLE orders WITH CHECK CHECK CONSTRAINT fk_customer;

In SQL Server, FK/CHECK constraints are disabled with NOCHECK CONSTRAINT. If WITH CHECK CHECK CONSTRAINT is not used when re-enabling, the constraint stays 'not trusted' and the query optimizer cannot use it.

MongoDB

// Constraint-like checks with schema validation rules
db.runCommand({ collMod: "product",
  validator: { $jsonSchema: { required: ["price"], properties: { price: { bsonType: "number", minimum: 0 } } } },
  validationLevel: "strict"
});
// Unique index for uniqueness
db.customer.createIndex({ email: 1 }, { unique: true });

There are no foreign keys (enforced in the application layer). The UNIQUE equivalent is a unique index. Instead of CHECK, use $jsonSchema validation; validationAction: 'warn' achieves the effect of disabling it.

Redis

# Not applicable: Redis has no FK/CHECK/UNIQUE constraints. Uniqueness is enforced at the application level with SET or
# SETNX/conditional writes.
SET customer:email:[email protected] 1 NX

There is no concept of relational integrity or constraints. Uniqueness is managed on the application side with SET ... NX (write only if absent) or by checking a separate SET/HASH.

Elasticsearch

# Not applicable: Elasticsearch has no FK/CHECK. Field types are enforced with the mapping,
# and uniqueness with a fixed _id.
PUT product/_doc/ABC-123
{ "price": 100 }

Relations and constraints do not exist in the engine. Type checking is done with the mapping. For a UNIQUE effect, use the natural key as the _id (writes to the same _id overwrite) or reject conflicts with op_type=create.

Open in DBAtlas →