CODEWITHSUNDEEP
sqliteunique-constraint
Jamie Adams
Jamie Adams

Reputation: 307

In SQLite3, is there a way to not allow a duplicate field when another field is the same value?

This is very hard to explain which is probably why I can't find an answer. For example, I have one table of "recipe", "ingredient". Ingredients would obviously be reused for different recipes so I can't use UNIQUE, but I want the command to refuse any attempts to insert into the database of a specific recipe where the ingredient already exists in that recipe.

For example, a record: ingredient = "apple", recipe = "applePie"; don't allow the insertion of another "apple" with the field recipe = "applePie" (in my actual application, other fields besides this may exist, they may be different on both records but they both have the same ingredient and recipe, so the second attempt to insert a value should be refused unless the ingredient does not exist).

Sorry if this is incredibly basic, I thought there must be a way to natively handle this without needing my program to select and compare within the code. I don't know how well I explained everything, please let me know if you need clarity.

Thank you!

Upvotes: 1

Views: 36

Answers (1)

forpas
forpas

Reputation: 164214

In the table's CREATE statement define a UNIQUE constraint for the combination of the columns ingredient and recipe:

CREATE TABLE tablename (
  recipe TEXT,
  ingredient TEXT,
  UNIQUE(recipe, ingredient)
)

Now, if you try to insert a row with the same recipe and ingredient as an existing row, you will get an error and the new row will not be inserted.

Upvotes: 1

Related Questions