Composite primary keys and multiple relationships

[Sam Hobbs]

I posted Creating table relationships but not to ask about relationships in general but I got a tutorial about relationships so I apologize if it is inappropriate to ask general database questions.

I have a relatively unusual requirement. I will simplify it as much as possible. I have files, each of which references (includes) 0-n other files. For example:

For example if file1 references (includes):

• file2
• file3

And if file4 references (includes):

• file2
• file3

Then the "files" table might be (fields "id" and "filename"):

• 1 file1
• 2 file2
• 3 file3
• 4 file4

Then the "includes" table would be (fields "IncludingId" and "IncludedId"):

• 1 2
• 1 3
• 4 2
• 4 3

Note that "IncludingId" and "IncludedId" are not the same. So I create the tables as in the following:

Code: Select all

CREATE TABLE [dbo].[files] (
    [Id] INT AUTO_INCREMENT NOT NULL,
    [Filename] NVARCHAR (100) NOT NULL,
    PRIMARY KEY ([Id] ASC)
);

Code: Select all

CREATE TABLE [dbo].[includes] (
    [IncludingId] INT NOT NULL,
    [IncludedId] INT NOT NULL,
    PRIMARY KEY ([IncludingId],[IncludedId] ASC),
    CONSTRAINT FK_Including FOREIGN KEY (IncludingId)
        REFERENCES files(Id),
    CONSTRAINT FK_Included FOREIGN KEY (IncludedId)
        REFERENCES files(Id)
);

Or at least that is what I have so far. I had something like that set up using SQL Server and Visual Studio but then Visual Studio totally wiped out (deleted) the SQL. Visual Studio has been improved so much that it is now too complicated so that is why I am trying Open Office. I have not yet had the opportunity to test this, please be understanding if there are mistakes. I am still working on it.

I have tried using the Open Office GUI to create the foreign keys but they were not saved when I saved the changes. So my next attempt will be to put the SQL into MySQL.

Is my database model too complicated for my purposes? I understand about self-referencing recursive tables. I think I can't do that since the children (included / referenced) files can be children of other files too.

[Villeroy]

If MySQL is the connected database engine, then you have to use MySQL specific tools or some text editor from where you can copy valid SQL code to menu:Tools>SQL... which provides a most simple command line interface to the connected database engine.
Base is not a database development environment. It is just a tiny frontend enabling the use of databases in office documents. The very limited tool set to create and maintain embedded HSQL databases is the same since OpenOffice.org 2.0 of 2005. From 2005 until today the developers could not even update from HSQL1 to HSQL2.

[Sam Hobbs]

Okay, so my question is off-topic for these forums. I understand.

In the other thread you provided a tutorial about foreign keys. I created this thread for that. The tutorial is off-topic for my question in that other thread; it is asking about the documentation.

[Villeroy]

In the other thread you provided a tutorial about foreign keys

In the other thread you did not mention which database engine you are using. So I did not even know which software we were talking about.

[Sam Hobbs]

The other thread is asking about the documentation of Open Office Base. The database engine is not relevant.

[Villeroy]

As far as I know, you can not edit the relations for any database other than embedded HSQL 1.8. Even this outdated database engine is not fully supported by the GUI.

Edit: Not exactly right. I can edit the relations but not the table structure of a HSQL2 table. Nobody knows which tools are supported for which backend. Not being able to edit the primary keys, I can not define composite primary keys of a HSQL2 table.