Drop foreign key without knowing the name of the constraint?

Question

I have created one table using the below command:

create table Table1(
    Id int Not Null 
        Foreign key 
        references Table2(Id)  
        on delete cascade 
        on update cascade,
    UserName nvarchar(150),
    TimeInSeconds int Not Null 

    primary key(Id,TimeInSeconds)
);

But now I want to drop the foreign key. As I haven't given a constraint name, I can't use:

Alter table <tablename>
drop foreign key <foreign key name>

Is there any way?

Answer 1

You can find the name of the constraint in INFORMATION_SCHEMA.TABLE_CONSTRAINTS

select CONSTRAINT_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where TABLE_NAME = 'Table1'

Answer 2

Similar to Ed's Answer but you can use this to select the key name based on the table and column name.

That way you can run it in a script or maybe as a subquery to drop the constraint.

SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME =  'post'
AND COLUMN_NAME =  'userID'

Answer 3

Expanding on the answers since I ran into some gotchas. Also, I had 2 foreign keys declared, so I added an optional key to keep, if it's null it'll just be ignored:

declare @name varchar(255),
     @table varchar(255) = 'mytable',
     @column varchar(255) = 'mykeycolumn',
     @validkey varchar(255) =  'mykeyIwanttokeep'

SELECT @name = CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = @table
    AND COLUMN_NAME =  @column
    AND (CONSTRAINT_NAME != @validkey or @validkey is null)

declare @sql varchar(1023) = 'alter table ' + @table + ' drop ' + @name 

exec (@sql)

Answer 4

A SQL Server option:

DECLARE @foreignkey varchar(100)
DECLARE @tablename varchar(100)
DECLARE @command nvarchar(1000)

DECLARE db_cursor CURSOR FOR
SELECT fk.name, t.name
FROM sys.foreign_keys fk
JOIN sys.tables t ON t.object_id = fk.parent_object_id
WHERE t.name IN (
    'table_1_name_here',
    'table_2_name_here'
)

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @foreignkey, @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @command = 'ALTER TABLE ' + @tablename + ' DROP CONSTRAINT ' + @foreignkey
    EXECUTE(@command)
    FETCH NEXT FROM db_cursor INTO @foreignkey, @tablename
END
CLOSE db_cursor
DEALLOCATE db_cursor

The SQL selects all the constraints for the tables you care about into a cursor and drops them one by one. All you need to know are the names of the tables you want them dropped from.

Answer 5

To drop a foreign key use the following commands :

1. SHOW CREATE TABLE table_name;
2. ALTER TABLE table_name DROP FOREIGN KEY table_name_ibfk_3; ("table_name_ibfk_3" is constraint foreign key name assigned for unnamed constraints). It varies.
3. ALTER TABLE table_name DROP column_name.

Answer 6

Never mind, below is Postgres syntax. It would be better if the question had sql server in the title as I didn't notice the tag

You could also drop and re-add the primary key on the parent table using cascade. This will remove any foreign keys that reference that table without you needing to know the foreign key names.

ALTER TABLE parent_table
DROP CONSTRAINT 'pk_id' CASCADE

-- add back pk
ALTER TABLE parent_table
ADD CONSTRAINT 'pk_id' PRIMARY KEY (id)

WARNING: you'd want to check all the dependencies first and if there are other tables, you'd need to add back their foreign keys. That does allow you to name the foreign keys properly when you add them back. This approach also may not be viable in a high transaction system due to the blocking transaction.

Answer 7

If you just look at the table in enterprise manager / management studio you will be able to see the list of keys and delete it from there.

Answer 8

you can put:

> show create table tablename;

you will see how was created the table...columns, types...etc. and you could see your constraint name.