I´m trying to create a table (collection container) using GridDB Cloud, using a Python script, as shown in GridDB Developers Quick Start Guide, available at /. The script runs fine, but the primary key is always created on the first column (Order_ID). However, I´d like the primary key to be based on both Order_ID and Product_ID columns. Any ideas on how to accomplish that? The Python script that drops and creates the table is shown below:

#CreateOrderDetailsTable.py
import requests
import json

# Set table name
tableName = "OrderDetails"

# URL and authentication
url = ":443/griddb/v2/gs_clustermfcloud****/dbs/******/containers"

headers = {
  'Content-Type': 'application/json',
  'Authorization': 'Basic *********************',
  'User-Agent': 'PostmanRuntime/7.29.0'
}

# If table exists, drop it...
payload = json.dumps([
  tableName
])
response = requests.request("DELETE", url, headers=headers, data=payload)
print("Drop table result: ", response.status_code)

# Create table 
payload = json.dumps({
  "container_name": tableName,
  "container_type": "COLLECTION",
  "rowkey": True,
  "columns": [
    {
      "name": "Order_ID",
      "type": "INTEGER",
      
    },
    {
      "name": "Product_ID",
      "type": "INTEGER",
      
    },
    {
      "name": "Quantity",
      "type": "INTEGER",
    },
    {
      "name": "Price",
      "type": "FLOAT"
    }
  ]
})
response = requests.request("POST", url, headers=headers, data=payload)
print("Create table result: ", response.status_code)

I´m trying to create a table (collection container) using GridDB Cloud, using a Python script, as shown in GridDB Developers Quick Start Guide, available at https://griddb/en/blog/griddb-cloud-quick-start-guide/. The script runs fine, but the primary key is always created on the first column (Order_ID). However, I´d like the primary key to be based on both Order_ID and Product_ID columns. Any ideas on how to accomplish that? The Python script that drops and creates the table is shown below:

#CreateOrderDetailsTable.py
import requests
import json

# Set table name
tableName = "OrderDetails"

# URL and authentication
url = "https://cloud5197.griddb:443/griddb/v2/gs_clustermfcloud****/dbs/******/containers"

headers = {
  'Content-Type': 'application/json',
  'Authorization': 'Basic *********************',
  'User-Agent': 'PostmanRuntime/7.29.0'
}

# If table exists, drop it...
payload = json.dumps([
  tableName
])
response = requests.request("DELETE", url, headers=headers, data=payload)
print("Drop table result: ", response.status_code)

# Create table 
payload = json.dumps({
  "container_name": tableName,
  "container_type": "COLLECTION",
  "rowkey": True,
  "columns": [
    {
      "name": "Order_ID",
      "type": "INTEGER",
      
    },
    {
      "name": "Product_ID",
      "type": "INTEGER",
      
    },
    {
      "name": "Quantity",
      "type": "INTEGER",
    },
    {
      "name": "Price",
      "type": "FLOAT"
    }
  ]
})
response = requests.request("POST", url, headers=headers, data=payload)
print("Create table result: ", response.status_code)

• python
• sql
• cloud
• griddb

1 Answer 1

It seems that GridDB´s REST API doesn´t currently support the creation of a compound Primary Key. A workaround for that is to use the query editor of GridDB´s management website to create the table, using a regular CREATE TABLE SQL statement, like the one below. I was unable to assign a explicit name to the primary key constraint, but I verified that the constraint is being enforced, thus preventing duplicate Order_ID and Product_ID combinations.

Create table OrderDetails (

Order_ID INTEGER NOT NULL,

Product_ID INTEGER NOT NULL,

Quantity INTEGER NOT NULL,

Price FLOAT NOT NULL,

PRIMARY KEY (Order_ID, Product_ID ) )