Apache OpenOffice Community Forum

Hi, as of now I'm new to databases, and I'm creating a database for all the dvds that I have home. But as the title says, I can't find a way to use multiple primary keys in GUI. I figured I didn't really need to do that since even if we had a movie in double, the 2nd dvd would still exist, but it's out of curiosity, to see what I can do with that.

Then again, I also have some other questions related to this problem (sorry for not putting that in the title ).
Does multiple primary keys work as AND (if we add an item with combination of an element A and an element B that is already in the table, not valid) or as OR (if we add item with element A or element B being the same as for another item in the table, not valid)? Can you change that? Does foreign keys only allow you to check if linked item exists or not?
Thanks for your time, have a good day (or night).

[Example] Relations reflected by list boxes in forms. In this example, the entire functionality relies on automatic primary keys although no primary key is visible in the forms and reports.
If you are wondering how to create a compound primary key in table edit mode, you select one column, then ctrl+click a second column and right-click mark them as PK.

In the example database open the relations window (Tools>Relations).
The mapping table "P_T" between "Persons" and "Things" has been created with a compound primary key consisting of 2 columns which are also foreign keys pointing to their respective detail table.
-- This table does not allow any duplicate combinations of "PID" and "TID" because that would violate the primary key.
-- This table does not allow any "PID" that does not exist in the primary key of the "Persons" table because that would violate the foreign key.
-- This table does not allow any "TID" that does not exist in the primary key of the "Things" table because that would violate the foreign key.

The detail tables "Persons", "Animals" and "Things" only have a name field and an auto-ID because this is about foreign keys, primary keys and the resulting relations. The item names are for the user interface (forms) while the keys work automatically in the background.
However, I added an additional unique index to the name fields. Open for instance the "Animals" for editing and then open the index designer (menu:Tools>Index Design). There is one index SYS_IDX_90 representing the primary key and a second "uniqueAnimalName" created by me. It enforces unique animal names. You can't enter the same name twice. SInce the column type is VARCHAR_IGNORECASE and this column is not nullable, any valid row in this table has to include a distinct animal name where "Dog", "dog", "DOG" etc would be rejected as duplicates.
These additional indices beyond the primary and foreign keys can also be added without uniqueness. In this latter case they just improve the lookup performance. Think of a hidden table storing each item's locations.

What do you mean by "multiples primary keys"?

• Multiple "records" (entries) with the same value in the primary key?
  This is not possible. The primary key is a unique identifier. Think of it as the "address" for the entry. For the DVDs in question, the ID printed on the cover will identify the "edition" of the work. If you need a separate identifier for the copy, you could manually label it (as many libraries do), or use the disc ID which is often available near the middle (BCA, around the hub).
• Multiple fields acting as the "access key", so you can choose in each case which one to use?
  You can retrieve a particular record by any unique identifier (alternate keys). The primary key is the "main handle", but by indexing you can also use other fields efficiently for queries.
• Multiple fields combined to form the primary key?
  This is not uncommon. The term for this is "composite key" (the "compound key", which Villeroy mentions in his reply, is a kind of composite key).
  For the situation assumed above with multiple copies of identical DVDs, the edition ID + BCA-ID may form a composite key to identify a particular DVD copy.

Make sure you understand the example Villeroy provided before you proceed. Creating a large data set without understanding key concepts is not advisable. Also, look up sources on normalising. It takes some work, but in time it will be redeemed, if you see yourself doing any significant database building.

Please note:
Physical labels on the dvd itself is not a good idea. Use a "CD-safe" permanent marker instead. Use a sleeve for barcode tags if you need that.

https://en.wikipedia.org/wiki/Database_normalization
https://docs.microsoft.com/en-us/office ... escription describes the path from a cross-table on a spreadsheet towards a many-to-many relation in a database.

I just write this end note to say that I am sorry I haven't closed this topic myself, I was tired and couldn't process all those answers, as I am really new to this. But thanks, I'll do my research as to how to interpret them.
Edit : to Villeroy, thanks for your answer, it describes pretty well the logic I was trying to understand, but I didn't understand why it'd matter so much, besides preventing some errors, to check if PID is valid since I would generally fill the table using a name, checking the corresponding ID in the persons table then applying that ID to the things table (or vice versa). I also don't know how to use indexation, but I'll figure all of this out, thank you.
to keme : I meant the third thing you mentioned. Thanks for the advice. Also yeah, I was asking that for a dvd database and I was wondering if dvds had any IDs for different models, so thanks for confirming that and for the marker tip.