cannot add or change a record
-
-
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?
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[^]
-
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.
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 2003describe Artist
Name Type1 Type2 Nullable Unique Read only
Id System.Guid DBTYPE_GUID True False False
Name System.String DBTYPE_WVARCHAR(255) True False False2 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 False2 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 False5 records affected.
-
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 2003describe Artist
Name Type1 Type2 Nullable Unique Read only
Id System.Guid DBTYPE_GUID True False False
Name System.String DBTYPE_WVARCHAR(255) True False False2 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 False2 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 False5 records affected.
-
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.
Yes, which is very limiting, no?
-
Yes, which is very limiting, no?
-
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.
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
singersing asongbefore the song iswritten. 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[^]
-
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
singersing asongbefore the song iswritten. 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[^]
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 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.
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.
-
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.
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.