Setting up Primary Keys

You can create primary keys using SQL statements or Pervasive PSQL Control Center. See Columns Tasks in Pervasive PSQL User's Guide.

Creating a Primary Key During Table Creation

You can create a primary key when you create a table, by using the PRIMARY KEY keywords in your CREATE TABLE statement. A primary key can consist of one or more columns. The example below shows the column named id being created then being designated the primary key:

CREATE TABLE mytable (id INTEGER,  
myname CHAR(20),  
PRIMARY KEY(id)) 

The example below shows how to create a primary key using more than one column as the unique key value:

CREATE TABLE mytable (id INTEGER,  
myname CHAR(20),  
PRIMARY KEY(id, myname)) 

Regardless of whether you specify the UNIQUE attribute on the column or columns that you designate as a primary key, the database engine automatically creates an index on the designated columns that does not allow duplicate values or null values in the columns. Null values are never allowed in a key column. Every primary key value must be unique.

For more examples, see CREATE TABLE in SQL Engine Reference.

Adding a Primary Key to an Existing Table

You can add a primary key to an existing table through PCC or by using the ALTER TABLE statement with ADD PRIMARY KEY. In Pervasive PSQL User's Guide, see To set or remove a column as a primary key and SQL Editor .

You must create the primary key on a column or columns that do not allow duplicate values or null values.

If necessary, you can modify the column attributes and make the column the primary key at the same time. Here is an example using SQL:

ALTER TABLE mytable MODIFY id INTEGER UNIQUE NOT NULL 
PRIMARY KEY 

If you want to add a primary key consisting of more than one column, you must add the key separately:

ALTER TABLE mytable ADD PRIMARY KEY(id, myname) 

For more examples, see ALTER TABLE in SQL Engine Reference.