Dropping Unnamed Constraints

Dropping unnamed constraints

There is not a built in procedure to accomplish this, but you can build your own using the information in the information_schema views.

Table based example

Create Proc dropFK(@TableName sysname)
as
Begin

Declare @FK sysname
Declare @SQL nvarchar(4000)
Declare crsFK cursor for

select tu.Constraint_Name from 
information_schema.constraint_table_usage TU
LEFT JOIN SYSOBJECTS SO 
ON TU.Constraint_NAME = SO.NAME
where xtype = 'F'
and Table_Name = @TableName
open crsFK
fetch next from crsFK into @FK
While (@@Fetch_Status = 0)
Begin
    Set @SQL = 'Alter table ' + @TableName + ' Drop Constraint ' + @FK
    Print 'Dropping ' + @FK
    exec sp_executesql  @SQL
    fetch next from crsFK into @FK
End
Close crsFK
Deallocate crsFK
End

Remove unnamed constraint from PostgreSQL

The default naming strategy that Postgres uses is tablename_columnname_key for such a constraint. For CHECK constraints the default name is tablename_columnname_check.

In your case the name would be file_file_name_key.

So you can use

alter table file drop constraint file_file_name_key;

If you don't want to rely on the default naming strategy, you can use the following query to retrieve the name:

select constraint_name
from information_schema.key_column_usage
where table_name = 'file'
  and table_schema = 'public'
  and column_name = 'file_name';

how to drop unnamed primary key constraint

alter table studenttbl drop primary key;

More details in the manual: http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_3001.htm#i2103997

^{(What is this tbl suffix for? Sounds like a terrible naming convention)}

DROP Constraint without knowing the name

-- Table T1
IF OBJECT_ID('dbo.T1') IS NOT NULL
    DROP TABLE T1

CREATE TABLE T1 (COL1 INT PRIMARY KEY)

-- Table T2
IF OBJECT_ID('dbo.T2') IS NOT NULL
    DROP TABLE T2

CREATE TABLE T2 (
    COL1 INT FOREIGN KEY REFERENCES T1(COL1)
    ,COL2 VARCHAR(2) UNIQUE
    ,COL3 INT NOT NULL DEFAULT(0)
    ,CHECK (
        COL3 IN (
            0
            ,1
            ,2
            )
        )
    )
GO

-- *** Foreign key constraint ***

DECLARE @Table NVARCHAR(256) = N'T2'
DECLARE @Column NVARCHAR(256) = N'COL1'
DECLARE @Command NVARCHAR(1000)

SELECT @Command = 'ALTER TABLE ' + '[' + @Table + '] DROP CONSTRAINT ' + CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = @Table
    AND COLUMN_NAME = @Column

--PRINT (@Command)  -- ALTER TABLE [T2] DROP CONSTRAINT FK__T2__COL1__058EC7FB
EXECUTE (@Command)

GO

-- *** Primary key constraint ***

DECLARE @Table NVARCHAR(256) = N'T1'
DECLARE @Command NVARCHAR(1000)

SELECT @Command = 'ALTER TABLE ' + '[' + @Table + '] DROP CONSTRAINT ' + [name]
FROM sys.key_constraints
WHERE [type] = 'PK'
    AND [parent_object_id] = OBJECT_ID(@table);

--PRINT (@Command)  -- ALTER TABLE [T1] DROP CONSTRAINT PK__T1__AA1D004EBAE57D94
EXECUTE (@Command)

GO

-- *** Unique key constraint ***

DECLARE @Table NVARCHAR(256) = N'T2'
DECLARE @Column NVARCHAR(256) = N'COL2'
DECLARE @Command NVARCHAR(1000)

SELECT @Command = 'ALTER TABLE [' + @Table + '] DROP CONSTRAINT ' + d.name
FROM sys.tables t
JOIN sys.indexes d ON d.object_id = t.object_id
    AND d.type = 2
    AND d.is_unique = 1
JOIN sys.index_columns ic ON d.index_id = ic.index_id
    AND ic.object_id = t.object_id
JOIN sys.columns c ON ic.column_id = c.column_id
    AND c.object_id = t.object_id
WHERE t.name = @Table
    AND c.name = @Column

--PRINT (@Command)  -- ALTER TABLE [T2] DROP CONSTRAINT UQ__T2__AA1D0040A435D4E0
EXECUTE (@Command)

GO

-- *** Not Null constraint ***

-- ALTER TABLE T2 ALTER COLUMN COL3 INT NULL

-- GO

DECLARE @Table NVARCHAR(256) = N'T2'
DECLARE @Column NVARCHAR(256) = N'COL3'
DECLARE @Command NVARCHAR(1000)

SELECT @Command = 'ALTER TABLE ' + '[' + @Table + '] DROP CONSTRAINT ' + d.name
FROM sys.tables t
JOIN sys.default_constraints d ON d.parent_object_id = t.object_id
JOIN sys.columns c ON c.object_id = t.object_id
    AND c.column_id = d.parent_column_id
WHERE t.name = @Table
    AND c.name = @Column

--PRINT (@Command)  -- ALTER TABLE [T2] DROP CONSTRAINT DF__T2__COL3__0682EC34
EXECUTE (@Command)

GO

-- *** Check constraint ***

DECLARE @Table NVARCHAR(256) = N'T2'
DECLARE @Column NVARCHAR(256) = N'COL3'
DECLARE @Command NVARCHAR(1000)

SELECT @Command = 'ALTER TABLE ' + '[' + @Table + '] DROP CONSTRAINT ' + d.name
FROM sys.tables t
JOIN sys.check_constraints d ON d.parent_object_id = t.object_id
JOIN sys.columns c ON c.object_id = t.object_id
    AND c.column_id = d.parent_column_id
WHERE t.name = @Table
    AND c.name = @Column

--PRINT (@Command)  -- ALTER TABLE [T2] DROP CONSTRAINT CK__T2__COL3__0777106D
EXECUTE (@Command)

Some of the queries are from my local repository and some are taken references from different sources:

Finding a Primary Key Constraint on the fly in SQL Server 2005

Drop Foreign Key without knowing the name of the constraint?

How to drop a unique constraint from table column?

Will unnamed constraints be generated with different placeholder names each time a script is run on a different server?

Your thinking is correct. You cannot rely on the generated names of constraints.

There is no guarantee that a constraint name will be generated the same on two systems or even on the same system if you were to drop and re-create a constraint. You should always try to use named constraints.

How to drop SQL default constraint without knowing its name?

Expanding on Mitch Wheat's code, the following script will generate the command to drop the constraint and dynamically execute it.

declare @schema_name nvarchar(256)
declare @table_name nvarchar(256)
declare @col_name nvarchar(256)
declare @Command  nvarchar(1000)

set @schema_name = N'MySchema'
set @table_name = N'Department'
set @col_name = N'ModifiedDate'

select @Command = 'ALTER TABLE ' + @schema_name + '.[' + @table_name + '] DROP CONSTRAINT ' + d.name
 from sys.tables t
  join sys.default_constraints d on d.parent_object_id = t.object_id
  join sys.columns c on c.object_id = t.object_id and c.column_id = d.parent_column_id
 where t.name = @table_name
  and t.schema_id = schema_id(@schema_name)
  and c.name = @col_name

--print @Command

execute (@Command)

Drop unnamed Foreign Key in MySql

You need to know the name of foreign key. If it was created without name, then name will be autogenerated. You should get information about the foreign key.

Use one of these queries to get foreign key names -

SELECT
  constraint_name
FROM
  information_schema.REFERENTIAL_CONSTRAINTS
WHERE
  constraint_schema = <'db_name'> AND table_name = <'table_name'>;

SELECT *
FROM
  information_schema.KEY_COLUMN_USAGE
WHERE
  constraint_schema = <'db_name'> AND table_name = <'table_name'> AND   
  referenced_table_name IS NOT NULL;

...and use ALTER TABLE <table_name> DROP INDEX <fk_name>; to drop foreign key.

Drop unnamed referential integrity constraint in Teradata

Ok I found it...

ALTER TABLE MIGRATION_TOOL.UNIT_TEST_EXISTING_TABLE_DEPENDENCY_CHILD
DROP FOREIGN KEY (PARENT_ID) REFERENCES 
MIGRATION_TOOL.UNIT_TEST_EXISTING_TABLE_DEPENDENCY_PARENT(ID);

---

Related Topics