Application Note for QuNect ODBC for QuickBase

Using a CREATE TABLE and ALTER TABLE statement

To create a table in Quickbase you need to specify which application the table should be created in. Each Quickbase application has an appid associated with it. Read the How to Find the APPID of a Quickbase Application to determine the appid of your application. If you wanted to create a table called CUSTOMERS in the application whose appid was bcks8a7y3 you would use the following CREATE TABLE SQL statement:

CREATE TABLE "CUSTOMERS bcks8a7y3"
(First_Name varchar(50),
Last_Name varchar(50),
Address varchar(50),
City varchar(50),
Country varchar(25),
Birth_Date date,
checkboxField bit,
fileattachmentField longVarBinary,
currencyField numeric(16, 2),
numericField double,
dateTimeField datetime,
TimeField time
) 

If you want the key field to be something other than the built-in Record ID# field you can use the following CREATE TABLE SQL statement to make the Last Name field the key field:

CREATE TABLE "CUSTOMERS bcks8a7y3"
(First_Name varchar(50),
Last_Name varchar(50),
CONSTRAINT DoesNotMatter PRIMARY KEY (Last_Name)
Address varchar(50),
City varchar(50),
Country varchar(25),
Birth_Date date,
checkboxField bit,
fileattachmentField longVarBinary,
currencyField numeric(16, 2),
numericField double,
dateTimeField datetime,
TimeField time
) 

Once you've created a table with the CREATE TABLE statement you'll need to get its DBID. You can do this by executing the following SQL statement and retrieving the first column in the first and only row.

SELECT @@newdbid

Let's say that the DBID you retrieved with the above SQL statement was bcks8b8ty. If you forgot to set the key field in the CREATE TABLE statement you can use the ALTER TABLE statement to set the key field:

ALTER TABLE bcks8b8ty ADD CONSTRAINT DoesNotMatter PRIMARY KEY (Last_Name)

You can also create a foreign key, primary key relationship with a CREATE TABLE statement. Let's say that the DBID of your customer table is bcks8b8ty. You can use a CREATE TABLE statement to create an orders table with a foreign key pointing back to the customer table:

CREATE TABLE "ORDERS bcks8a7y3"
(Customer_Last_Name varchar(50),
CONSTRAINT DoesNotMatter FOREIGN KEY (Customer_Last_Name) REFERENCES CUSTOMERS (DoesNotMatter)
Amount double,
Date date,
TaxExempt bit,
TimeOfOrder time
) 

Let's say that the DBID of the Orders table is bcks8b8tz. If you forgot to add the foreign key field in the CREATE TABLE statement you can use the ALTER TABLE statement to create it and set it as the foreign key field:

ALTER TABLE bcks8b8tz ADD Customer_Last_Name float
ALTER TABLE bcks8b8tz ADD CONSTRAINT DoesNotMatter FOREIGN KEY (Customer_Last_Name) REFERENCES CUSTOMERS (DoesNotMatter)