Bernie,
To find the FKs in other tables that are referencing the PK on your
Junktable, try this:
SELECT fk2.sys_index_id=5 as `FK ID`, +
fk2.sys_index_name=18 as `FK Name`, +
ft3.sys_table_name=18 as `FK Table`, +
fk2.sys_column_name=18as `FK Column` +
FROM sys_indexes pk1, sys_indexes fk2, sys_tables ft3 +
WHERE +
(pk1.sys_table_id = +
(SELECT sys_table_id +
FROM sys_tables where sys_table_name = 'JunkTable')) +
AND (pk1.sys_primary_key = 1) +
AND (fk2.sys_foreign_key = pk1.sys_index_id) +
AND (fk2.sys_table_id = ft3.sys_table_id) +
ORDER BY fk2.sys_index_id, fk2.sys_column_name
Bill
You will have to drop those FKs from the appropriate table, and recreate
them pointing to the real table.
Bill
On Thu, Oct 28, 2010 at 9:13 PM, Bernard Lis <[email protected]> wrote:
> I did this:
> R>SET IDQUOTES=`
>
> R>RENAME TABLE `49ee1862` TO JunkTable NOCHECK
>
> Table 49ee1862 renamed to JunkTable
> You must manually fix references to this table in VIEWS, RULES, and any
> expressions in Forms, Reports, or Labels. See Reference Manual-RENAME.
>
> R>DROP TABLE JunkTable
> -ERROR- This table is referenced by a Foreign Key - unable to drop table.
> (2720)
>
>
> ----- Original Message ----- From: "A. Razzak Memon" <[email protected]>
> To: "RBASE-L Mailing List" <[email protected]>
> Sent: Thursday, October 28, 2010 9:08 PM
>
> Subject: [RBASE-L] - Re: Finding constraints
>
>
> At 08:56 PM 10/28/2010, Bernard Lis wrote:
>>
>> I did that but my real problem is that a new table suddenly
>>> appeared. The name of the table is 49ee1862 Looking at the
>>> data in the table it is an image of another table. I can't
>>> seem to get rid of that table because it is referenced.
>>> When I did list keys, that table does not show up. Any ideas
>>> what I should try next?
>>>
>>
>>
>> That is the result of un-finished process while in data designer.
>>
>> Here's how to DROP that table.
>>
>> 01. CONNECT dbname
>>
>> 02. Make sure that the database IDQUOTES SETting is set to `.
>> It is the reversed single quote on the same key with "~".
>>
>> You may correct this settings as:
>>
>> SET IDQUOTES=`
>>
>> 03. Now rename the ghost table.
>>
>> RENAME TABLE `49ee1862` TO JunkTable NOCHECK
>>
>> 04. DROP TABLE JunkTable
>>
>> That's all there is to it!
>>
>>
>> Very Best R:egards,
>>
>> Razzak.
>>
>>
>>
>>
>
>
