如何删除未指定名称的数据库约束？

I totally feel your pain—there’s nothing more frustrating than needing to remove a constraint you didn’t name upfront, especially when the basic ALTER TABLE DROP CONSTRAINT command won’t work without a name. Let’s walk through how to target each common constraint type, with actionable queries for major databases (SQL Server, PostgreSQL, MySQL) since system catalogs differ across platforms.

First, a quick recap for the easy case: if you did explicitly name your constraint, this is trivial:

ALTER TABLE {table_name} DROP CONSTRAINT {constraint_name};

But for unnamed constraints, we have to first look up the auto-generated name from the database’s system tables, then use that name in the drop command. Here’s how to do it for each constraint type:

1. Primary Key Constraint #

Primary keys are usually auto-named with predictable patterns (e.g., PK_<TableName> in SQL Server, <table>_pkey in PostgreSQL). MySQL treats the primary key as a special unnamed constraint you can drop directly.

SQL Server #

First find the auto-generated name:

SELECT name
FROM sys.key_constraints
WHERE type = 'PK' AND parent_object_id = OBJECT_ID('{table_name}');

Then drop it using the name you retrieve:

ALTER TABLE {table_name} DROP CONSTRAINT {pk_name};

PostgreSQL #

Confirm the auto-generated primary key name (or use the default <table>_pkey):

SELECT conname
FROM pg_constraint
WHERE conrelid = '{table_name}'::regclass AND contype = 'p';

Drop command:

ALTER TABLE {table_name} DROP CONSTRAINT {pk_name};

MySQL #

No need to look up a name—drop directly:

ALTER TABLE {table_name} DROP PRIMARY KEY;

2. Foreign Key Constraint #

Foreign keys get auto-named with patterns like FK_<TableName>_<ReferencedTableName> (SQL Server) or <table>_<column>_fkey (PostgreSQL).

SQL Server #

Find the foreign key name:

SELECT name
FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID('{table_name}');

Drop it:

ALTER TABLE {table_name} DROP CONSTRAINT {fk_name};

PostgreSQL #

Look up the foreign key:

SELECT conname
FROM pg_constraint
WHERE conrelid = '{table_name}'::regclass AND contype = 'f';

Drop command:

ALTER TABLE {table_name} DROP CONSTRAINT {fk_name};

MySQL #

First retrieve the constraint name:

SELECT CONSTRAINT_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_NAME = '{table_name}' AND REFERENCED_TABLE_NAME IS NOT NULL;

Then drop:

ALTER TABLE {table_name} DROP FOREIGN KEY {fk_name};

3. Unique Constraint #

Auto-named unique constraints follow patterns like UQ_<TableName>_<ColumnName> (SQL Server) or <table>_<column>_key (PostgreSQL). MySQL treats unique constraints as indexes, so the syntax differs slightly.

SQL Server #

Find the unique constraint name:

SELECT name
FROM sys.key_constraints
WHERE type = 'UQ' AND parent_object_id = OBJECT_ID('{table_name}');

Drop it:

ALTER TABLE {table_name} DROP CONSTRAINT {uq_name};

PostgreSQL #

Look up the unique constraint:

SELECT conname
FROM pg_constraint
WHERE conrelid = '{table_name}'::regclass AND contype = 'u';

Drop command:

ALTER TABLE {table_name} DROP CONSTRAINT {uq_name};

MySQL #

Retrieve the constraint name first:

SELECT CONSTRAINT_NAME
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = '{table_name}' AND CONSTRAINT_TYPE = 'UNIQUE';

Drop using index syntax:

ALTER TABLE {table_name} DROP INDEX {uq_name};

4. Not Null Constraint #

Not Null is a column-level constraint, so you don’t use DROP CONSTRAINT—instead, alter the column to allow nulls directly.

SQL Server/PostgreSQL #

ALTER TABLE {table_name} ALTER COLUMN {column_name} DROP NOT NULL;

MySQL #

ALTER TABLE {table_name} ALTER COLUMN {column_name} NULL;

5. Default Constraint #

Default constraints often have the most obscure auto-names (e.g., DF__<TableName>__<ColumnName>__<RandomChars> in SQL Server), so you’ll need to target the specific column.

SQL Server #

Find the default constraint for a specific column:

SELECT name
FROM sys.default_constraints
WHERE parent_object_id = OBJECT_ID('{table_name}')
AND parent_column_id = (SELECT column_id FROM sys.columns WHERE object_id = OBJECT_ID('{table_name}') AND name = '{column_name}');

Drop it:

ALTER TABLE {table_name} DROP CONSTRAINT {default_name};

PostgreSQL #

Look up the default constraint for a column:

SELECT conname
FROM pg_constraint
WHERE conrelid = '{table_name}'::regclass AND contype = 'd'
AND conkey = ARRAY(SELECT attnum FROM pg_attribute WHERE attrelid = '{table_name}'::regclass AND attname = '{column_name}');

Drop command:

ALTER TABLE {table_name} DROP CONSTRAINT {default_name};

MySQL #

No need to look up a name—drop directly:

ALTER TABLE {table_name} ALTER COLUMN {column_name} DROP DEFAULT;

6. Check Constraint #

Check constraints enforce column-level rules, and their auto-names vary by database. Note that MySQL ignored check constraints before version 8.0.16.

SQL Server #

Find the check constraint name:

SELECT name
FROM sys.check_constraints
WHERE parent_object_id = OBJECT_ID('{table_name}');

Drop it:

ALTER TABLE {table_name} DROP CONSTRAINT {check_name};

PostgreSQL #

Look up the check constraint:

SELECT conname
FROM pg_constraint
WHERE conrelid = '{table_name}'::regclass AND contype = 'c';

Drop command:

ALTER TABLE {table_name} DROP CONSTRAINT {check_name};

MySQL (8.0.16+) #

Retrieve the constraint name:

SELECT CONSTRAINT_NAME
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = '{table_name}' AND CONSTRAINT_TYPE = 'CHECK';

Drop it:

ALTER TABLE {table_name} DROP CONSTRAINT {check_name};

内容的提问来源于stack exchange，提问作者Amit Limba