, 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. If you habe created the schema for the table without the UNIQUE constraint, here is a recipe how to add it.Physical_location (Foreign Key (physical_locations.id))Īs shown, the devices table references its location to the physical locations table. (SELECT MIN(permissionID) FROM Permission GROUP BY user,location ) Īnnex 2: Adding a unique contraint to table If you want to apply the first or second option, you will have to do it. If you already have duplicates in your table, the following code will help you you to remove them. If you try to insert a duplicate entry with this option, you will run into the error message you specified in the trigger: sqlite> insert into Permission (user, location) values (1, 2) When exists (select * from Permission where user = new.user and location = new.location) It is the most general way to react on an INSERT and it could look like this for your example: CREATE TRIGGER avoid_duplicate_user_locations Option 3: Use a triggerįor the sake of completeness, it is also possible to use a trigger to prevent duplicates to be inserted, albeit I hardly can imagine a reason why you should prefer this option. It really boils down to the fact, that it is much easier to add and drop an index from a table than to add and remove a unique constraint on a table. As the sqlite documentation explains, internally it is probably implemented in the exact same way. You might ask about the difference between this option and the first one, and so have many others. If you try to insert a duplicate entry with this option you get the exact same error message as in the first option: sqlite> insert into Permission (user, location) values (1, 2) It is also possible to create auniqe index CREATE TABLE Permission (ĬREATE UNIQUE INDEX user_location ON Permission (user,location) If you now try to insert a duplicate entry into the table you get the following result: sqlite> insert into Permission (user, location) values (1, 2) Įrror: UNIQUE constraint failed: er, Permission.location See the Annex 2 of this post on how to add this option to an existing table. The caveat is that it is not so easy to add and remove this option on an existing table. This is the most natural option to express your requirement. You can put the constraint you want directly in your table: CREATE TABLE Permission ( Option 1: Have a unique constraint in your table There are several options to achieve that and I will describe these options in the order you should consider them, as the former options are more natural than the latter. What you essentially want to do is to make the combination of user and location unique across you table.
0 Comments
Leave a Reply. |