> -----Urspr�ngliche Nachricht----- > Von: Billy Willoughby [mailto:[EMAIL PROTECTED] > Gesendet: Samstag, 5. Juni 2004 16:36 > An: [EMAIL PROTECTED] > Betreff: Foreign Key Question > > Hello Everyone; > > I have a foreign key I'm trying to create to cascade a delete and I > keep getting the message "General error;-8030 POS(1) Column must be > indexed.". > > Table "FTP-COMPANY" has two comlumns, id_company and nm_company, PK is > id_company. > > Table "FTP-CONTACT" has id_company, id_contact, nm_contact, PK id_company, > id_contact. > > I want when the id_company is deleted in table "FTP-COMPANY" for all > contacts in table "FPC-CONTACT" to be deleted. Below is the command I'm > trying to use to create the FK. I've tried creating an index on > FTP-Contact, but get "Integrity constraint violation;250 POS(1) Duplicate > secondary key." when I try to create it. > > I'm sure I'm doing something obvious wrong, but so far I've been too thick > to get whatever it is from the docs. > > > ALTER TABLE "DBA"."FTP-COMPANY" FOREIGN KEY "FK-CONTACT" ("ID_COMPANY") > REFERENCES "DBA"."FTP-CONTACT" ("ID_COMPANY") ON DELETE CASCADE >
Oops, we have to start with an explanation about foreign keys. As written in the reference manual http://dev.mysql.com/doc/maxdb/en/6d/117c5fd14811d2a97400a0c9449261/frameset.htm and the delete-rule below, deleting rows in the referenCED table will cause something (depending on the delete rule) to be done in the referenCING table. In your description we all thought that FTP-COMPANY would be the referenCED table (causing a delete in that table to delete rows in the FTP(or FPC?)-CONTACT). But your command is the other way round. Let us assume, your description was understood correctly, then ALTER TABLE "DBA"."FTP-CONTACT" FOREIGN KEY "FK-CONTACT" ("ID_COMPANY") REFERENCES "DBA"."FTP-COMPANY" ("ID-COMPANY") ON DELETE CASCADE Would sound better. BTW: the SECOND ("ID-COMPANY") is optional. Now we have to check for index/key-needs: If referenced columns are specified that are not the key in the referencing table, the referenced table must have a UNIQUE definition whose column names and sequence match those of the referenced columns. "ID-COMPANY" IS the key in FTP-COMPANY, therefore no need to add any index. Elke SAP Labs Berlin > Thanks - Billy Willoughby > > > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
