Unique key problem

Toad Data Modeler
1

I’ve got a very odd data model so far.

I’ve got a files table with a compound primary key made up of a name and an extension.

I’ve got a songs table whose primary key is the compound foreign key from the files table.

Now the reasoning is that a partiular song “Foo” may have the actual song file that can be edited in software, example “Foo.xm” and that same song may have an mp3 file “Foo.mp3” for preview.

I need to allow multiple files with the same name but different extensions in the Files table. However, I would like to only allow 1 song with a particular name in the songs table. The unique box is greyed out. How can I accomplish this?

If confused, please take a look at the attached model.

Thanks!

webdb.txp (90.6 KB)

2

Hello,

To get what you want, take the following steps:

  1. Edit entity SONGS.
  2. On the Keys tab, create a new key.
  3. Edit the new key and on tab Attributes assign attribute NAME to the key.

On the WS you will have the NAME attribute NN PFK and AK.

If you have more questions, please write us back. Thanks.

Regards,

Vladka + Mario

3

Hmmm…your model says “there can be many songs associated with a file” but based on your post it should say “there can be many files associated with a song”.

Do you have much leeway to change this model? If so, I would make the song name the PK of SONGS, then just have a FK relationship in FILES to SONGS. That way you can always determine the song name for any file. SONGS to FILES would be a 1-to-many relationship. SONG names would always be unique since they are the PK (or you could make SONG name a UK with some surrogate PK).

Otherwise, if you want unique song names in SONGS but must have the PK of that table be the file information, there may be some rows in FILES which you won’t be able to associate with a SONG, because of your requirement that song names be unique.