I have a number of tables that will be looked up using the url 'slug'. For example 'news' and 'blog' both have the 'slug' field, which is defined as a unique field in the doctrine database schema.
Is there any way I can extend this uniqueness across both tables: for example if there is a news article with slug= "good-story" then it will fail if I try to enter that as a slug for a blog article?
Thanks Tom
As far as I'm aware you can't have a UNIQUE contstraint which spans tables. One way to handle it would be to have a SLUG table where the 'slug' field would be the primary key. You'd need INSERT triggers on 'news' and 'blog' which would try to insert the new 'slug' into the SLUG table. You might also want to put UPDATE triggers on 'news' and 'blog' to update SLUG.slug if 'news.slug' or 'blog.slug' was updated.
I hope this helps.
Depending on where you are in development. If you are maintaining the app, then I agree with Bob Jarvis's suggestion. Seems like a heap of disconnected code.
What you are describing is an entity that belongs in its own table. With that in mind, this is how I might model it.
tbl_slug -------------------- slug slug_type -primary key ( slug, slug_type ) -constraint - slug is unique to table tbl_news -------------------- id fk_slug_slug fk_slug_type -constraint - fk_slug_type = 'news' tbl_blog -------------------- id fk_slug_slug fk_slug_type -constraint - fk_slug_type = 'blog'