can i insert a value, in child table's Foreign Key Constraint, which is not yet created in parent table as primary key?

Question

I have a relation:

Contact Table - using contactNumber as PK
Message Table - using messageID as PK
Contact_Message - contains foreign keys for many to many mapping - used to populate inbox

i.e. One Contact can have many messages sent/received and One Message can be sent to many contacts but Received from only one contact
BUT there is possibility of sending/receiving Message to/from unknown number that isn't in Contact table of database.
Since it is a many to many relation, the unknown number needs to be added to Contact_Message Table so that i may query it.

what I'm looking for is to just add the contactNumber (to show in inbox as is) in Contact_Message table but SQLite DB engine generates error Foreign Key Constraint Failed since that contactNumber is not found in Contact table.

....

Moreover: to explain my concern, I want:

contactNumber is to INSERT in contact_m2m_message table irrespective of contactNumber whether it exists in Contact Table or not
if a contact is DELETEd, relevant row/s in contact_m2m_message shouldn't delete ; relevant contactNumber and messageID row should persist
if a message is deleted, relevant row/s should be deleted in contact_m2m_message

.

is there any solution to this problem? .

Here is the code:

CREATE TABLE contact (
   contactNumber Numeric PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, 
   contactName TEXT NOT NULL
);

CREATE TABLE message (
   msgID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
   messageContent TEXT NOT NULL
);

CREATE TABLE contact_m2m_message (
   contactNumber NUMERIC NOT NULL REFERENCES contact ( contactNumber ) 
   ON DELETE NO ACTION ON UPDATE CASCADE, 

   messageID INTEGER NOT NULL REFERENCES message ( msgID ) 
   ON DELETE CASCADE ON UPDATE CASCADE
);

score 0 · Answer 1 · answered Oct 14 '14 at 07:11

You have the requirement that it is possible to have Contact_Message relations without a corresponding Contact.

With the foreign key, you introduce another constraint, which says that all Contact_Message relations must have a corresponding Contact.

One of these constraint is wrong, and according to your description, it's the second one. Just remove the foreign key constraint.

If you still want to have the ON UPDATE CASCASE action, you have to implement it with a trigger.

can i insert a value, in child table's Foreign Key Constraint, which is not yet created in parent table as primary key?

1 Answers1