Menu
I want to create a table with two columns: user_id, image_id.
I don't want user_id or image_id to be unique, but I also want to protect my table from duplicate pairs of same user_id and image_id. Can I do that?
768
To define a UNIQUE constraint, you use the UNIQUE keyword followed by one or more columns. You can define a UNIQUE constraint at the column or the table level. Only at the table level, you can define a UNIQUE constraint across multiple columns.
To define a UNIQUE on multiple columns, we put a comma-separated columns list inside parenthesis that follows the UNIQUE keyword.
Yes, you can define UNIQUE constraints to columns other than the primary key in order to ensure the data is unique between rows.
If we define DISTINCT for one column in SQLite select statement then the DISTINCT clause will return unique values only for that column. In case if we use DISTINCT for multiple columns in SQLite SELECT statement then DISTINCT will use a combination of those columns to evaluate the duplicate values.
Add a separate constraint for both columns:
CREATE TABLE MyTable(
user_id INTEGER,
image_id INTEGER,
[...],
UNIQUE(user_id, image_id)
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
