Dropping foreign key constraints from database

If you have explicitly stated the name of a constraint in a database, you can easily drop the constraint using the name specified. But if you haven't specified a name for a constraint at the time of the construction of the table, database engine will assign a name automatically and you will have to find the constraint name from the system tables of the database.

From this post we are going to see how to find the name of a foreign key constraint and drop it in a single script for different database systems.

1. H2

CREATE ALIAS IF NOT EXISTS DROP_FK AS $$ void executeSql(Connection conn, String sql)
throws SQLException { conn.createStatement().executeUpdate(sql); } $$;

call drop_fk('ALTER TABLE TABLE_NAME DROP CONSTRAINT ' ||
(SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINTS 
WHERE TABLE_NAME = 'TABLE_NAME' AND COLUMN_LIST  = 'COLUMN_NAME'));

DROP ALIAS IF EXISTS DROP_FK;

2. Microsoft SQL Server

DECLARE @COMMAND NVARCHAR(200);

SELECT @COMMAND= 'ALTER TABLE TABLE_NAME DROP CONSTRAINT ' + RC.CONSTRAINT_NAME + ';'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KF ON RC.CONSTRAINT_NAME = KF.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KP ON RC.UNIQUE_CONSTRAINT_NAME = KP.CONSTRAINT_NAME
WHERE KF.TABLE_NAME = 'TABLE_NAME';

EXEC (@COMMAND);

3. MySQL

SET @databasename = "sample";

SELECT CONCAT("ALTER TABLE TABLE_NAME DROP FOREIGN KEY ",constraint_name)
INTO @sqlst
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where TABLE_SCHEMA = @databasename and TABLE_NAME = "TABLE_NAME"
and referenced_column_name is not NULL limit 1;

PREPARE stmt FROM @sqlst;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @sqlstr = NULL;

4. Oracle

declare
  con_name varchar2(100);
  command varchar2(200);
  databasename VARCHAR2(100);
BEGIN
select constraint_name into con_name from all_constraints where table_name='TABLE_NAME' AND owner=databasename AND 
constraint_type = 'R';

    if TRIM(con_name) is not null
    then
      command := 'ALTER TABLE TABLE_NAME DROP CONSTRAINT ' || con_name;
      dbms_output.Put_line(command);
      execute immediate command;
    end if;

    exception
    when NO_DATA_FOUND
    then
    dbms_output.Put_line('Foreign key not found');
END;

5. PostgreSQL

DO $$ DECLARE con_name varchar(200); 
BEGIN 

SELECT 'ALTER TABLE table_name DROP CONSTRAINT ' || tc .constraint_name || ';' INTO con_name 
FROM information_schema.table_constraints AS tc 
JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name 
JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name 
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name = 'table_name' AND kcu.column_name = 'column_name';
 
EXECUTE con_name; 
END $$;

If you know the basic syntax of the sql, you can customize them according to your requirements.