Re: [dba-users] composite foreign key

[Andrew Jensen]

THUFIR HAWAT wrote: crossposted to: mailing.comp.open-office,comp.databases follow up to: mailing.comp.open-office This is in "base" for open office 2.0: Three tables, A,B,C with an * to denote primary key fields: A one* two three ===================== 1 2 3 4 5 6 7 8 9 B four* five six =================== 1 2 3 4 5 6 7 8 9 C seven* one four ==================== 1 1 1 2 1 4 3 1 7 4 4 1 5 4 4 6 4 7 7 7 1 8 7 4 9 7 7 Relations ========= A.one 1:n C.one update/delete cascade B.four 1:n C.four update/delete cascade I want to make C.one and C.four a composite foreign key so that the following never occurs: C seven* one four ==================== 1 1 1 2 1 4 ... 55 1 4 C.seven is unnecessary and fails to enforce uniqueness. I asked this also in the general list, before finding the archives for this list. I'm not finding a specific answer to this problem, although it seems to depend somewhat on the database engine. I installed via yum and am using HSQLDB, which, presumably, supports composite foreign keys. You can do either of these steps. Create table C without column seven. You designate both one and four as the primary key, in the table definition editor you highlight both rows and right click then select 'Primary Key'. You could also leave column seven as the primary key. Then you would create a unique constraint. ALTER TABLE "C" ADD UNIQUE ("One", "Four"); Now, if you want to actually create foreign key constraints you will need to add these (or least you must add the second one) via direct DDL commands. To do so you open the SQL windows in Base, Tools>SQL and enter the appropriate ALTER TABLE commands. In the case of the tables as they exist in your example the commands are ALTER TABLE "C" ADD FOREIGN KEY ("One") REFERENCES "A" ("One")  ON DELETE CASCADE ON UPDATE CASCADE ; ALTER TABLE "C" ADD FOREIGN KEY ("Four") REFERENCES "B" ("Four") ON DELETE CASCADE ON UPDATE CASCADE ; Instead of the unique constraint, you could have created a unique index from the table defnition editor on columns c.one and c.four this will still keep you from adding the last entry in your example. A unique constraint I believe, should not be the same as a unique index - yet under Base it shows up as one. I can't tell you for sure whether creating a unique constraint therefore actually generates the index or not. There is one difference under base, add a unique index via the GUI and you can drop it. Add it via the ALTER TABLE ADD command and you may not. It is listed as a system index, you must again use DDL to drop it. I would use the ALTER TABLE ADD command myself, and add the index only later, if needed. Finally, if you tried to apply the two foreign key relationships in the Relationship dialog under Base you would not be able to, this is a known issue. However, once you have added them via the DDL commands, opening the Relationship dialog will show both. (you will need to close and open the Base file, before they will show up in this dialog???) HTH Drew Do I need to use SQL commands directly? If so, I'll have to install something to do so? thanks, Thufir