|
|
| Re: delete foreign key [message #371697 is a reply to message #371696] |
Mon, 27 November 2000 12:04   |
-sven
Messages: 11
Registered: August 2000
|
Junior Member |
|
|
Hi Shirley,
If you know the name of the foreign key constraint, it's super easy. All you need to do is issue the command
ALTER TABLE 'table name'
DROP CONSTRAINT 'foreign key constraint name';
If you do not know the name of the foreign key constraint, it's slightly more difficult, but you can find it assuming you know the name of the parent table (i.e., the table which the foreign key references.)
In such a case, you can use the following query:
SELECT CONSTRAINT_NAME, TABLE_NAME
FROM user_constraints
WHERE constraint_type='R'
AND r_constraint_name =
(SELECT constraint_name
FROM user_constraints
WHERE table_name = 'name of the parent table'
AND constraint_type = 'P')
UNION
SELECT CONSTRAINT_NAME, TABLE_NAME
FROM user_constraints
WHERE constraint_type='R'
AND r_constraint_name =
(SELECT constraint_name
FROM user_constraints
WHERE table_name = 'name of the parent table'
AND constraint_type = 'U');
This somewhat complicated query will provide the name(s) of any foreign key(s) which reference the parent table, along with the name of the child table which holds the foreign key, and your alter table command can then be used to drop the key constraint.
The reason you need the UNION operator in the "SELECT CONSTRAINT NAME..." statement is because a foreign key may reference either a primary key (constraint_type = 'P') or a unique key (constrain_type = 'U'). If your shop has strict standards requiring that foreign keys may only reference a primary key in a parent table, you can skip the UNION and the second part of the above query.
I hope this short explanation makes sense to you, but if not, contact me and I'll try to explain it again with an example or two.
Good luck! -Sven
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
