cannot add or change a record

ASPnoob

Hi all,

I have a table called Singer and another called Song in Access. The Singer table has a primary key of SingerID and the Song table has a primary key of SongID and a foreign key of SingerID. I'm trying to add data to the Song table and I'm getting the error, "You cannot add or change a record because a related record is required in table 'Singer'. Why am I getting that error? Any help will be greatly appreciated, thanks in advance for your help.

Lost User

A Singer must exist before a Song can be sung by the singer.

Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]

ASPnoob

Hi Eddy, thanks for replying. So you are saying that I will get the error message I mentioned if I try to add a song into the Song table before I add an singer for that song in the Singer table?

Lost User

Yes. The song you try to enter does not have a (valid) SingerId. Create a singer, like "Fat Lady", give her number 1 and enter your song with her SingerId. You'll find that the song will be accepted. If you want to "point" to other tables from your Song-table, then the data in the other table has to exist before the Foreign Key is entered.

Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]

PIEBALDconsult

When I wrote something similar a few years back, I created Artist and Title tables and a PlayList table to allow many-to-many relationships between them because a song may be performed by multiple artists.

C:\Projects\KCDXlogger>dbt kcdx "describe Artist ; describe Title ; describe Playlist
DBT V6.0 -- Simple SQL interface Sir John E. Boucher 2003

describe Artist

Name Type1 Type2 Nullable Unique Read only

Id System.Guid DBTYPE_GUID True False False
Name System.String DBTYPE_WVARCHAR(255) True False False

2 records affected.

describe Title

Name Type1 Type2 Nullable Unique Read only

Id System.Guid DBTYPE_GUID True False False
Name System.String DBTYPE_WVARCHAR(255) True False False

2 records affected.

describe Playlist

Name Type1 Type2 Nullable Unique Read only

CreTim System.DateTime DBTYPE_DATE True False False
Text System.String DBTYPE_WVARCHAR(255) True False False
PlaTim System.DateTime DBTYPE_DATE True False False
ArtistId System.Guid DBTYPE_GUID True False False
TitleId System.Guid DBTYPE_GUID True False False

5 records affected.

ASPnoob

Thank you all for replying. I have solved this problem. It is as someone had said, you can't create a song before you have a singer.

PIEBALDconsult

Yes, which is very limiting, no?

Lost User

PIEBALDconsult wrote:

Yes, which is very limiting, no?

Yes, it is. But that was not his original question, it was about referential integrity. So his statement can be rewritten as you can't create a song before you have singers.

Lost User

Shameel wrote:

you can't create a song before you have singers.

Don't be shocked too much, but I lied (just a little bit). Of course it's possible with the correct schema;

Id

SongWriter

1

Paul McCartney

2

Mick Jones

Song

WrittenBy

1

A world without love

You can't have a singer sing a song before the song is written. Creating a table-structure can be hard; a schema that has already been tried and used by a coworker, is a schema that has probably been tested by the co-workers user-base. Meaning, there's little chance that his code contains errors. ..and sometimes, you have to answer beyond the scope of the question. That must be what I enjoy most of the forums; reading strange questions and strange answers that make you go "hmz". Sometimes one can add a helpful answer, sometimes it's merely a waste of time. The most interesting discussions often start with "care to explain" or "why do you.." Do keep up the good work and keep answering questions; not only will it have saved a few people quite some time and frustration, it's also a very efficient way of learning.

Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]

Lost User

ok, I get you. I made a pretty bad assumptions that the singer is the writer. :-)

Eddy Vluggen wrote:

Do keep up the good work and keep answering questions; not only will it have saved a few people quite some time and frustration, it's also a very efficient way of learning.

I agree. I started learning many things when I started answering questions here. Helping others is a great way of learning.

PIEBALDconsult

Right, but the cause of the problem is a limiting schema. He wanted to create a song before he had singers, therefore the schema was the problem, not the code. :-D A better schema allows one to add either first and then associate them later. And furthermore, his way requires you to add duplicates of a song that is performed by many singers -- which is bad.

PIEBALDconsult

Shameel wrote:

the singer is the writer

I don't think the OP is concerned with the writer at all. And if he wanted to track writers he would still be best served by a many-to-many schema.