Is a Composite Primary Key possible in ParaSQL?

[SHG]

I'd like to use two columns together as the primary key for a table. If I were writing this myself, it would be as follows:

CREATE TABLE SAMPLE (

    item1ID INT,

    item2ID INT,

    PRIMARY KEY (item1ID, item2ID)

) 

I can't seem to accomplish this with the ParaSQL Create Table tool.  What am I missing?

Thanks

[ParaSQL Support]

If Item1ID and Item2ID are integers (not auto keys) you should be able to designate them both as primary and make a composite PK. However, per best practices highlighted below (see Help > Best Practices for full docs) we don't recommend it. The problem is if you ever need to have another record refer to that record, you can't in ParaSQL (child records can't refer to parents with composite PKs in ParaSQL). The better solution is usually to have a single PK (typically an AutoKey) and then use a unique index across the two other columns... that way if you ever need to put a child under it you can.

From the ParaSQL Best Practices docs:

-------------------------------------------------------------------

Primary Key Values

We strongly suggest using AutoKey for your Primary Keys. We do NOT suggest that you embed any meaning in the key itself (say an "order number" with the date as part of it) as these keys are difficult to generate and maintain.

The most common exception to the "always use an AutoKey" rule of thumb is look-up tables. For example, you might have a list of U.S. states where a two character column containing the state abbreviation is the logical Primary Key for the table.

We suggest that you NEVER use Composite Primary Keys (these are Primary Keys composed of multiple columns). If you are an advanced database designer, we suggest you instead use an AutoKey for the Primary Key and then add an additional UNIQUE INDEX over the columns that are required to be unique. You can do so easily via Tools > SQL Console... with a command like the following:

CREATE UNIQUE INDEX index_name ON table_name (col_name_1, col_name_2)