SyntaxException: line 2:10 no viable alternative at input 'UNIQUE' > (...NOT EXISTS books ( id [UUID] UNIQUE...)

Question

I am trying the following codes to create a keyspace and a table inside of it:

CREATE KEYSPACE IF NOT EXISTS books WITH REPLICATION = { 'class': 'SimpleStrategy',
'replication_factor': 3 };

CREATE TABLE IF NOT EXISTS books (
  id UUID PRIMARY KEY,
  user_id TEXT UNIQUE NOT NULL,
  scale TEXT NOT NULL,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  reward map<INT,TEXT> NOT NULL,
  image_url TEXT NOT NULL,
  video_url TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

But I do get:

SyntaxException: line 2:10 no viable alternative at input 'UNIQUE' (...NOT EXISTS books ( id [UUID] UNIQUE...)

What is the problem and how can I fix it?

Answer 1

I see three syntax issues. They are mainly related to CQL != SQL.

The first, is that NOT NULL is not valid at column definition time. Cassandra doesn't enforce constraints like that at all, so for this case, just get rid of all of them.

Next, Cassandra CQL does not allow default values, so this won't work:

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Providing the current timestamp for created_at is something that will need to be done at write-time. Fortunately, CQL has a few of built-in functions to make this easier:

INSERT INTO books (id, user_id, created_at)
VALUES (uuid(), 'userOne', toTimestamp(now()));

In this case, I've invoked the uuid() function to generate a Type-4 UUID. I've also invoked now() for the current time. However now() returns a TimeUUID (Type-1 UUID) so I've nested it inside of the toTimestamp function to convert it to a TIMESTAMP.

Finally, UNIQUE is not valid.

user_id TEXT UNIQUE NOT NULL,

It looks like you're trying to make sure that duplicate user_ids are not stored with each id. You can help to ensure uniqueness of the data in each partition by adding user_id to the end of the primary key definition as a clustering key:

CREATE TABLE IF NOT EXISTS books (
    id UUID,
    user_id TEXT,
    ...
    PRIMARY KEY (id, user_id));

This PK definition will ensure that data for books will be partitioned by id, containing multiple user_id rows.

Not sure what the relationship is between books and users is, though. If one book can have many users, then this will work. If one user can have many books, then you'll want to switch the order of the keys to this:

PRIMARY KEY (user_id, id));

In summary, a working table definition for this problem looks like this:

CREATE TABLE IF NOT EXISTS books (
  id UUID,
  user_id TEXT,
  scale TEXT,
  title TEXT,
  description TEXT,
  reward map<INT,TEXT>,
  image_url TEXT,
  video_url TEXT,
  created_at TIMESTAMP,
  PRIMARY KEY (id, user_id));