Sets: Unique Values
Sometimes one column should hold several values. For example, a user can have more than one email address. In SQL, you would put the emails in a second table and join the two tables. Cassandra has no joins. Instead, it has collection types, which store several values inside one column. There are three: set, list and map.
This lesson is about the set. A set holds unique values. The same value can't be in it twice.
Create a table with a set
Write the type of the values inside < >. Here, set<text> is a set of text values:
CREATE TABLE profiles (
username text PRIMARY KEY,
emails set<text>
);
Write a set
Put the values inside curly braces { }. This insert gives '[email protected]' twice:
INSERT INTO profiles (username, emails)
VALUES ('lee', {
'[email protected]',
'[email protected]',
'[email protected]'
});
SELECT * FROM profiles;
Output:
username | emails
----------+---------------------------------------
lee | {'[email protected]', '[email protected]'}
(1 rows)
The set keeps each value only once. It also stores the values sorted, so '[email protected]' comes first. Don't expect the order you wrote them in.
Add and remove values
Use + to add values and - to remove them:
UPDATE profiles
SET emails = emails + {'[email protected]'}
WHERE username = 'lee';
UPDATE profiles
SET emails = emails - {'[email protected]'}
WHERE username = 'lee';
SELECT * FROM profiles;
Output:
username | emails
----------+-----------------------------------------
lee | {'[email protected]', '[email protected]'}
(1 rows)
+ and - change only the values you name. The other values stay. You don't need to read the set first. If you add a value that is already in the set, nothing changes and you get no error.
Be careful not to forget emails +. Without it, the new set replaces the whole old set:
UPDATE profiles
SET emails = {'[email protected]'}
WHERE username = 'lee';
SELECT * FROM profiles;
Output:
username | emails
----------+---------------------
lee | {'[email protected]'}
(1 rows)
Lee's other two emails are gone.
When to use a set
Use a set for a small group of unique values that belong to one row, like tags or email addresses. Collections are made for small amounts of data. If the number of values can grow without limit, like all the comments on a post, use a separate table with a clustering column instead.
In the exercise, add a new email to dev1's set, and keep the emails that are already there.