Tables can have single and compound primary keys. To create a table having a single primary key, use the PRIMARY KEY keywords followed by the name of the key, enclosed in parentheses.
- Create and use the keyspace in the last example if you haven't already done so.
users table in the demodb keyspace, making the user name the primary key.
CREATE TABLE users ( user_name varchar, password varchar, gender varchar, session_token varchar, state varchar, birth_year bigint, PRIMARY KEY (user_name));
Using a compound primary key
Use a compound primary key when you want to create columns that you can query to return sorted results.
To create a table having a compound primary key, use two or more columns as the primary key.
CREATE TABLE emp ( empID int, deptID int, first_name varchar, last_name varchar, PRIMARY KEY (empID, deptID));
The compound primary key is made up of the empID and deptID columns in this example. The empID acts as a partition key for distributing data in the table among the various nodes that comprise the cluster. The remaining component of the primary key, the deptID, acts as a clustering mechanism and ensures that the data is stored in ascending order on disk (much like a clustered index in Microsoft SQL Server).
Inserting data into a table
In a production database, inserting columns and column values programmatically is more practical than using cqlsh, but often, being able to test queries using this SQL-like shell is very convenient.
To insert employee data for Jane Smith, use the INSERT command.
INSERT INTO emp (empID, deptID, first_name, last_name) VALUES (104, 15, 'jane', 'smith');