Hmm, useful code, thanks.
I replaced the dialog with:
CHOOSE vgettables_dialog_response +
FROM #VALUES FOR DISTINCT SYS_COLUMN_NAME +
from SYS_COLUMNS +
lines 30
and had it loop to the beginning until escaped at the CHOOSE command.
"I don't envy anyone having to do that to thirty tables. I'd love to see
a three-line solution."
Yeah, I was hoping for something like the command: DROP INDEX ColumnName
in TableName:
DROP FKEY ColumnName in TableName
DROP PKEY ColumnName in TableName
Doug
On 2/12/2022 7:09 PM, Bruce Chitiea wrote:
Doug:
PKo - original PK column
PKr - replacement PK column
I suppose the efficient approach depends on the number of tables
containing the PK-FK relationship.
My databases tend to be small, so I use a very low-tech, "manual"
approach to replacement. I've performed the following for up to six
relationships without denting the drywall.
(1) Identify all tables containing the PK column you want to replace:
best viewed in Courier New font :
{UTILITY | Interactive: Find all Tables containing a given column Name}
--file: utility_find_all_tables_containing_columnName.rmd
--rbse: GENERIC
--auth: rlist, adapted bachitiea
--crea: 2020-08-12
--updt: 2021-05-15
{purpose : Find all tables containing a given column name. Useful when
need
to change a column's parameters, but the designer will not permit it
because
the columnName exists in another table. }
--set trace on
--show prep -----------------------------------------------------------
CLS
SET VAR +
vgetTables_targetColumnName TEXT = NULL , +
vgetTables_dialog_caption TEXT = NULL, +
vgetTables_dialog_message TEXT = NULL , +
vgetTables_dialog_response TEXT = NULL , +
vendKey TEXT = NULL
--select target -------------------------------------------------------
SET VAR +
vgetTables_dialog_caption = ' Find Table Names' , +
vgetTables_dialog_message = 'Enter the Column Name'
DIALOG .vgetTables_dialog_message vgetTables_dialog_response =30
vendKey 1 +
CAPTION .vgetTables_targetColumnName +
OPTION THEMENAME Longhorn +
|MESSAGE_FONT_NAME Segoe UI +
|MESSAGE_FONT_SIZE 10 +
|BUTTON_OK_FONT_NAME Arial +
|BUTTON_OK_FONT_SIZE 10 +
|BUTTON_OK_CAPTION OK
IF vendKey = '[Esc]' THEN ; GOTO cleanup ; ENDIF
--do the do -----------------------------------------------------------
BROWSE sys_table_name, sys_table_id
FROM sys_tables +
WHERE sys_table_id +
IN (SELECT sys_table_id +
FROM sys_columns +
WHERE sys_column_name = .vgetTables_dialog_response ) +
ORDER BY sys_table_name +
OPTION WINDOW_STATE NORMAL |WIDTH 950
--cleanup -------------------------------------------------------------
LABEL cleanup
CLEAR VAR vgetTables%, vendKey
CLS
RETURN
*****************************************************
(2) Make a screen shot of the table list
(3) Insert PKr into the primary table immediately to the right of PKo
(4) AUTONUM PKr IN (primary table) USING xxxxxx 1 ORDER BY (whatev
DESC) NUM
(5) Insert the PKr column definition in each secondary table inside
the PKo column
(6) For each secondary table:
UPDATE PKr IN (secondary table) = t2.PKr +
FROM (secondary table) t1, (primary table) t2 +
WHERE t1.PKo = t2.PKo
(7) Drop the PKo -> PKo relationship for each table
(8) Preserve the PKo columns by renaming as PKo2
(9) Rename PKr with the original PKo name
(10) Establish the new PKo -> PKo relationship for each table
(11) Test everything
(12) Only after the gods have smiled on you, delete the PKo2 columns
I don't envy anyone having to do that to thirty tables. I'd love to
see a three-line solution.
Best, Bruce
Go Bengals
Bruce A. Chitiea | SafeSectors, Inc.
112 Harvard Ave #272 | Claremont CA 91711-4716 | USA
/[email protected]/ | +011 (909) 238-9012 c | +011 (909) 912-8678 f
------ Original Message ------
From: "Doug Hamilton" <[email protected]>
To: "R:Base List" <[email protected]>
Sent: 2/12/2022 4:22:41 PM
Subject: [RBASE-L] - Deleting & redefining PK & FK
Hi All -
A couple of questions:
I want to redefine some Primary Keys and Foreign Keys in my database.
How do I programmatically make them go away so I can redefine them
with ALTER TABLE?
I want to do it by code so I can run it on a test db before risking a
screwup on the production db.
How do I redefine them as DESCENDING? I don't see that option in the
command syntax.
Any Blues Clues?
TIA,
Doug
-- This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus
-- For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
--- You received this message because you are subscribed to the
Google Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it,
send an email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/974447d8-c6bf-f377-23db-1089215ea7c9%40wi.rr.com.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google
Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/em7c4ad5a3-79bc-4642-911a-0ec5b483401f%40pathfinder
<https://groups.google.com/d/msgid/rbase-l/em7c4ad5a3-79bc-4642-911a-0ec5b483401f%40pathfinder?utm_medium=email&utm_source=footer>.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/e3001a02-69d3-83e2-73a7-802d8508d394%40wi.rr.com.
