On Tuesday May 22 2007 11:04 am, Bud Haycock wrote:
> Hi..
>
> I'm a newbie who has broken his database..
>
> I have a small database with two tables with a relationship between
> the primary field ("ID") on one and a similar field on the other.
> All was working well, but I think I inadvertently set the "ID"
> field on the second table to "primary" as well.
>
> After inputting all the data, everything was fine until I restarted
> the database. I now get the following error when trying to open
> the data source.
>
>
> SQL Status: S1000
> Error code: -78
>
> error in script file line: 4 Constraint already exists: SYS_FK_88
> in statement [CREATE CACHED TABLE "Skills"("SAR Basics"
> BOOLEAN,"Basics Date" VARCHAR(50),"FirstAid" BOOLEAN,"FirstAid
> Expiry" DATE,"Executive" VARCHAR(50),"Executive Expiry"
> DATE,"Search Manager" BOOLEAN,"Search Manager Date"
> DATE,"Mantracker" BOOLEAN,"Mantracker Date" DATE,"ID" INTEGER NOT
> NULL,"Team Leader" BOOLEAN,"Last Search/Mock" DATE,"Team "
> CHAR(1),CONSTRAINT SYS_FK_88 PRIMARY KEY("ID"),CONSTRAINT SYS_FK_88
> FOREIGN KEY("ID") REFERENCES "Members"("ID"))]
>
> I cannot open or edit the tables now. Is there a way to edit the
> individual lines in the script and recover my work? Any suggestions
> would be greatly appreciated. I realize this is 100% my fault...
>
> Thanks in advance
>
> Bud
How good are you in unzipping and zipping files? The script file is a
plain text file. If you unzip the database file, you will have extracted some
folders and files. One of those folders is labeled database. The scrip file
lies within the database folder. Open it and remove the extra contraint, namely
what is contained in this line:
,CONSTRAINT SYS_FK_88 FOREIGN KEY("ID")
Save the file as script, but make sure it has not file extension. Then zip it
all back together.
Two very important points: make a copy of the database file in a different
folder from the original. Do all of your zipping and unzipping in this second
folder.
After rezipping the file, see if you can open it.
I think you have not set your tables up correctly. The "Skills" table
needs two fields with a field type of Interger. One of them for the primary key
for the table, and the second one for the foreign key, call it MembersID. Edit
the relationship between the "Members" table and the "Skills" table so that
the Member's ID field is linked with the Skills' MemberID field.
Dan
