I have an article table where I want the slug to be unique.
CREATE TABLE article ( title char(50) NOT NULL, slug char(50) NOT NULL );
When the user enters a title e.g.
News on Apple, I want to check the database to see if a corresponding slug exists e.g.
news-on-apple. If it does, I'll suffix a numerical value until I find a unique one e.g.
news-on-apple-1. Can that be achieved with a recursive CTE query instead of doing recursion in my ORM. Is there a good ballpark number where I should stop recursing and error out. I can imagine people using the same title a 1000 times which would result in 1000 queries just to create 1 article.
It's possible that my understanding of recursive CTE is incorrect and there's no better way to find a unique slug. Please suggest any alternatives.