How I can remove foreign key on same table

Question

I have an table name of emp and I have applied primary key on empid 1st column and by mistake I have applied foreign key on the same emp table on same column without giveing name of foreign key.
So, now the problem is I want to drop that foreign key but I don't know the name of foreign key which I applied on the same table.. how can I find out the name of foreign key ?
If I got the name then I can drop that key
Please share your thoughts

Answer 1

If this is MySQL, the following will show all constraints on a given table in a given database. Just substitute for <table> and <database>:

SELECT
TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_SCHEMA = '<database>' AND
REFERENCED_TABLE_NAME = '<table>';

I got that from:
https://stackoverflow.com/questions/201621/how-do-i-see-all-foreign-keys-to-a-table-or-column

I ran that on one of my MySQL tables and got this output which shows the foreign key names:

mysql> SELECT
-> TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
-> FROM
-> INFORMATION_SCHEMA.KEY_COLUMN_USAGE
-> WHERE
-> REFERENCED_TABLE_SCHEMA = 'hhaccessibility' AND
-> REFERENCED_TABLE_NAME = 'question';
+---------------+-------------+-----------------------------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+---------------+-------------+-----------------------------------+-----------------------+------------------------+
| user_answer | question_id | user_answer_question_id_foreign | question | id |
| user_question | question_id | user_question_question_id_foreign | question | id |
+---------------+-------------+-----------------------------------+-----------------------+------------------------+
2 rows in set (6.09 sec)