|
Home > Archive > SQL Anywhere database > October 2005 > Dropping Constraint Blows Away FK!!
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Dropping Constraint Blows Away FK!!
|
|
| Jeff Gibson 2005-10-28, 8:23 pm |
| Any advice on this would be greatly appreciated...
I'm in the process of starting to migrate our database from ASA6 to ASA9.
On my first attempt to load my new ASA9 database, I noticed that I was
getting a ton of warnings telling me I had a duplicate indexes on my tables.
Found that this was due to the fact that I had set the "Values are unique"
constraint on my PK columns.
My thought was, OK, I'll just go back in and remove the constraint from the
PK. Keeping the PK setting in place.
The problem is that when I removed the constraint, any FK's that reference
that column that I just dropped the constraint from, are blown away.
I would like to get rid of these in ASA6 before I port to ASA9. Can anybody
give me a hint on what I would need to do to drop the constraint without
dropping my FK's as well???
TIA
Jeff Gibson
Intercept Solutions
Nashville, TN
| |
| Breck Carter [TeamSybase] 2005-10-28, 8:23 pm |
| Some guesswork: Foreign key constraints can point at primary keys *or*
unique constraints, so perhaps your foreign key were pointing at the
unique constraint that you dropped... thus cascading to zap the
foreign key.
More guesswork: Fixing the reload.sql file might be easier than
anything else. You run ASA9 dbunload on the ASA6 database, then
manually edit the reload.sql.
Send me the reload.sql from dbunload -n (just schema) by email if you
want me to look at what's goin' on.
Breck
On 28 Oct 2005 13:30:20 -0700, "Jeff Gibson"
< jgibson@interceptsol
utions.com> wrote:
>Any advice on this would be greatly appreciated...
>
>I'm in the process of starting to migrate our database from ASA6 to ASA9.
>
>On my first attempt to load my new ASA9 database, I noticed that I was
>getting a ton of warnings telling me I had a duplicate indexes on my tables.
>Found that this was due to the fact that I had set the "Values are unique"
>constraint on my PK columns.
>
>My thought was, OK, I'll just go back in and remove the constraint from the
>PK. Keeping the PK setting in place.
>
>The problem is that when I removed the constraint, any FK's that reference
>that column that I just dropped the constraint from, are blown away.
>
>I would like to get rid of these in ASA6 before I port to ASA9. Can anybody
>give me a hint on what I would need to do to drop the constraint without
>dropping my FK's as well???
>
>TIA
>
>Jeff Gibson
>Intercept Solutions
>Nashville, TN
>
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
| Stephen Rice 2005-10-28, 8:23 pm |
| Hi Jeff
Its getting late on a Friday so I'm not sure I'm following you and I
don't have a v6 installation to play with.
Can you post a snippet of your schema for the tables in question and
then the sequence of commands you issued.
I did a quick test in v9 and adding/removing a unique constraint from a
primary key had no negative effects. I also did a quick search of the
fix logs and found nothing obviously related
/steve
Jeff Gibson wrote:
> Any advice on this would be greatly appreciated...
>
> I'm in the process of starting to migrate our database from ASA6 to ASA9.
>
> On my first attempt to load my new ASA9 database, I noticed that I was
> getting a ton of warnings telling me I had a duplicate indexes on my tables.
> Found that this was due to the fact that I had set the "Values are unique"
> constraint on my PK columns.
>
> My thought was, OK, I'll just go back in and remove the constraint from the
> PK. Keeping the PK setting in place.
>
> The problem is that when I removed the constraint, any FK's that reference
> that column that I just dropped the constraint from, are blown away.
>
> I would like to get rid of these in ASA6 before I port to ASA9. Can anybody
> give me a hint on what I would need to do to drop the constraint without
> dropping my FK's as well???
>
> TIA
>
> Jeff Gibson
> Intercept Solutions
> Nashville, TN
>
>
--
Stephen Rice
Technical Services Manager
iAnywhere Solutions
--- Please Post ---
Whitepapers, Tech Docs, Solved Cases, Bug Fixes and
"Report a bug" are all available on www.ianywhere.com
| |
| Bruce Hay 2005-10-28, 8:23 pm |
| The "duplicate indexes" warning message in the server console is telling you
that you have two (or more) indexes on the same set of columns, not two rows
with the same combination of values for a unique index or primary key. The
extra indexes are overhead you don't need, requiring more space in the
database file, more time taken by the server to maintain them, and more
search strategies to consider for queries.
You should continue to define primary keys. You can refer to the article
below for a method of finding and removing duplicate indexes.
http://www.sybase.com/detail?id=1032350
Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer
"Jeff Gibson" < jgibson@interceptsol
utions.com> wrote in message
news:43628a5c@forums
-2-dub...
> Any advice on this would be greatly appreciated...
>
> I'm in the process of starting to migrate our database from ASA6 to ASA9.
>
> On my first attempt to load my new ASA9 database, I noticed that I was
> getting a ton of warnings telling me I had a duplicate indexes on my
tables.
> Found that this was due to the fact that I had set the "Values are unique"
> constraint on my PK columns.
>
> My thought was, OK, I'll just go back in and remove the constraint from
the
> PK. Keeping the PK setting in place.
>
> The problem is that when I removed the constraint, any FK's that reference
> that column that I just dropped the constraint from, are blown away.
>
> I would like to get rid of these in ASA6 before I port to ASA9. Can
anybody
> give me a hint on what I would need to do to drop the constraint without
> dropping my FK's as well???
>
> TIA
>
> Jeff Gibson
> Intercept Solutions
> Nashville, TN
>
>
| |
| Jeff Gibson 2005-10-28, 8:23 pm |
| Hey Guys,
I did a test here with ASA6 and ASA9 running an unloaded v6 db loaded into a
new v9 db.
Everything came over into new database. FK's were identical between both
db's. Was getting warnings again under ASA9.
When I went to the parent table, and unchecked the unique constraint under
v6, it blew the FK away between the child table and itself.
However....
When I did the same thing under v9, the FK stayed in place.
So I'm wondering if this is just a bug with ASA6 and nothing more.
I'll see what I can do to post a small example later this evening.
Jeff Gibson
Intercept Solutions
Nashville, TN
"Stephen Rice" <NSsrice@ianywhere.com> wrote in message
news:436294b2$1@foru
ms-2-dub...
> Hi Jeff
>
> Its getting late on a Friday so I'm not sure I'm following you and I don't
> have a v6 installation to play with.
>
> Can you post a snippet of your schema for the tables in question and then
> the sequence of commands you issued.
>
> I did a quick test in v9 and adding/removing a unique constraint from a
> primary key had no negative effects. I also did a quick search of the fix
> logs and found nothing obviously related
>
> /steve
>
>
> Jeff Gibson wrote:
>
> --
> Stephen Rice
> Technical Services Manager
> iAnywhere Solutions
>
> --- Please Post ---
> Whitepapers, Tech Docs, Solved Cases, Bug Fixes and
> "Report a bug" are all available on www.ianywhere.com
| |
| Jeff Gibson 2005-10-28, 8:23 pm |
| Breck,
The unique constraints are on the pk of each table only.
The FK's only pointed toward the PK column in another table. I have no idea
how I would point the FK to the constraint in lieu of the table.
Or am I missing your point here?
See my post below with how ASA9 worked and ASA6 didn't.
I would like to determine what is going on here. Seems like it just might
be a bug. I will however see about getting a snippet of the reload script
sent to you.
Jeff Gibson
Intercept Solutions
Nashville, TN
"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:jj45m15kgqa0lsq
23iohocqoio11rt6skc@
4ax.com...
> Some guesswork: Foreign key constraints can point at primary keys *or*
> unique constraints, so perhaps your foreign key were pointing at the
> unique constraint that you dropped... thus cascading to zap the
> foreign key.
>
> More guesswork: Fixing the reload.sql file might be easier than
> anything else. You run ASA9 dbunload on the ASA6 database, then
> manually edit the reload.sql.
>
> Send me the reload.sql from dbunload -n (just schema) by email if you
> want me to look at what's goin' on.
>
> Breck
>
> On 28 Oct 2005 13:30:20 -0700, "Jeff Gibson"
> < jgibson@interceptsol
utions.com> wrote:
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
|
|
|
|
|