New answers tagged unique-key
This can be achieved by using a unique index on the combination of counter and the month of date_booked. Something like this: create unique index idx_tbl on tbl (counter, to_char(date_booked, 'yyyymm')); However the above will fail because to_char() is not an "immutable" function and thus cannot be used in an index (even if we know that the above is ...
Using surrogate IDs is one way but you don't really have to. If you want to keep your current design - which I suppose has Work (workName) as primary key and SubWork (workName, subWorkName) as a potential primary or unique key, you can define the Activity table like this (I also changed slightly the column names): Work -------- workName PK SubWork ...
Primary keys (and foreign keys) should be unique, otherwise how does the database know which row the foreign key relationship is pointing to? I'm thinking about using a primary key int, but what if one day i go in overflow? Suppose i got so much works to store that i go out the limit? I wouldn't worry about hitting the limit for ints. The mysql ...
The good thing with unique indexes is that search stops when the first value matches, but that requires the WHERE part to match exactly with the index. In your case the index will be big. If you are lucky the value might be found quickly on the b-tree, else it might need to scan almost the entire index.
Top 50 recent answers are included