UML Modelling: How to set the combination of two colums to be unique?

Question

Hello everybody,

I have a question about DB modeling, I have a table created as following:

CREATE TABLE "users_articles" ("id" INTEGER PRIMARY KEY  NOT NULL,"article_id" INTEGER,"user_id" INTEGER)

1. Which statement will alter this table, so that the combination of article_id and user_id is unique?
2. Which statement tells me, if the DB has already been altered?

Thanks,

Markus

Answer 1

1. You need to create unique index:

   CREATE UNIQUE INDEX unique_users_articles ON users_articles (article_id, user_id)

2. Please clarify why you need it?
   UPDATE: Use IF NOT EXISTS to ignore index creation if it already exists:

   CREATE UNIQUE INDEX IF NOT EXISTS unique_users_articles ON users_articles (article_id, user_id)