![]() If I remove the constraint then my code works fine. and I get: : constraint failed FOREIGN KEY. Android room FOREIGN KEY constraint failed even though values are the same. foreign keystrue in the connection string, which I read here and here but after adding the constraint Im no longer able to add a Job record. Foreign Key Constraint Failed 787 causing abort on attempt to insert entry into table. Android Room FOREIGN KEY constraint failed (code 787) when insert new row. UNIQUE constraint failed: devices.logical_address, devices. SQLSTATE 23000: Integrity constraint violation: 19 FOREIGN KEY constraint failed > Laravel. The unique constraint prevents duplicates: insert into devices (device_id,logical_address, physical_location_id, district) Insert into physical_locations (physical_location_id, name, district)ĭrawback is that you have to add district to devices: insert into devices (device_id,logical_address, physical_location_id, district) References physical_locations (district, physical_location_id) ![]() ![]() , foreign key (district, physical_location_id) Now it is possible to reference that in a foreign key (guarantees that the same location, district is used: create table devices , UNIQUE (district, physical_location_id) ( physical_location_id char(2) not null - primary key Is there a way to enforce this using a unique constraint or by restructuring my tables, or will I need something more advanced?Ī common trick is to add a unique constraint to physical_locations: create table physical_locations Then it is possible for a device to have a location with a district that does not match the district of the logical_address. One thought I had was to create another logical_address table with address and district columnns, with a unique constraint on both and simply reference device logical addresses to these entries with a unique constraint, but that introduces the same issue in another area. Alternatively, I am not sure how to structure my data to maintain this requirement without such an issue. I just don't know how to reference this column in a foreign key referenced table for a UNIQUE constraint. So for example, consider these records: physical_locationsġ 100 LA <-this is okay, same district as 2 but different addressĢ 201 LB <-this is okay, same district as 1 but different addressģ 100 LC <-this is also okay, same address as 1 but different districtĤ 100 LD <-this is NOT OKAY, same address AND same district as 3 What I need to do is enforce uniqueness of the logical_address column in the devices table but ONLY within the district associated with its location record. Physical_location (Foreign Key (physical_locations.id))Īs shown, the devices table references its location to the physical locations table.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |