Setting up Primary Keys

---

You must create primary keys using SQL statements. Pervasive Control Center does not provide this ability in the user interface, unless you wish to modify by hand the SQL statement created by Create Table Wizard before you generate the table.

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, by using the ALTER TABLE statement with ADD PRIMARY KEY key words. 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:

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.