Sql: Unique field contraint (slug) across multiple tables (doctrine/symfony)

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

Answers


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'


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.


Need Your Help

grep limited characters - one line

linux unix ubuntu debian

I want to look up a word in multiple files, and return only a single line per result, or a limited number of characters (40 ~ 80 characters for example), and not the entire line, as by default.