I am trying to link two tables in a database and enforce referential integrity. The first table contains ID information about children, including their location, name, record #, and birth date. The record number repeats within each village, and many children have the same name. As a result, the primary keys for this table are all location related fields, the child's name, record #, and date of birth. The second table will contain information about rounds of vaccines the child has received. This table has the same primary keys plus another one for the "round" field. Ideally during data entry, first, we will check whether a child exists in the ID table. If not, we will add them. Then we will add their info to the immunization table.
I created relationships, but I cannot enforce referential integrity. I already have primary keys, and I checked that the data type in the two tables matches. I would like to set cascade updates as well. Any idea why I can't enforce RI?
Please take a look and let me know what you think! Thanks.