Compact 3.5 Primary Key

[NYWGUY54]

I am trying to create a new Compact 3.5 database & tables in code so that my
user can create a new DB on the fly without my help. The problem is that I
cannot create 2 columns in the same table as a Primary Key pair this way.
When I define the table or alter it using the SQL Management Studio i can
make 2 columns the primary key. However in the SQLMS using a query i still
cannot alter the column to be a PK.

So am i doing something wrong, Is this a limitation of the Compact Engine?
My code for createing the table is:
"Create Table Settings " & _
"(Code nvarchar(50) Primary Key Not Null, " & _
"Type nvarchar(50) Primary Key Not Null) "
The error that is produced is "cannot parse query"
When i use the alter table method:
alter table settings add constraint [setPK] Primary key (Type)
The error says that the table already has a Primary Key.
Thanks

[Paul G. Tobey [eMVP]]

The errors are both correct. You can't add alter a table primary key in that
situation and your SQL is wrong in the first case. If you make the CREATE
state correctly, it will work fine. Here's the gammar for CREATE TABLE in
SQL Compact (so what you write based on this should work for almost anything):

-----
CREATE TABLE table_name
( { < column_definition > | < table_constraint > } [ ,...n ]
)
< column_definition > ::=
{ column_name data_type }
[ { DEFAULT constant_expression
| [ IDENTITY [ ( seed , increment ) ]
]
} ]
[ ROWGUIDCOL ]
[ < column_constraint > [ ...n ] ]
< column_constraint > ::=
[ CONSTRAINT constraint_name ]
{ [ NULL | NOT NULL ]
| [ PRIMARY KEY | UNIQUE ]
| REFERENCES ref_table [ ( ref_column ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
}
< table_constraint > ::=
[ CONSTRAINT constraint_name ]
{ [ { PRIMARY KEY | UNIQUE }
{ ( column [ ,...n ] ) }
]
| FOREIGN KEY
( column [ ,...n ] )
REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
}
-----

"CREATE TABLE MyTable( column1 nvarchar(50) NOT NULL, column2 nvarchar(50)
NOT NULL, column3 nvarchar(50), PRIMARY KEY(column1, column2) );" or
something roughtly like that.

Paul T.

Post by NYWGUY54
I am trying to create a new Compact 3.5 database & tables in code so that my
user can create a new DB on the fly without my help. The problem is that I
cannot create 2 columns in the same table as a Primary Key pair this way.
When I define the table or alter it using the SQL Management Studio i can
make 2 columns the primary key. However in the SQLMS using a query i still
cannot alter the column to be a PK.
So am i doing something wrong, Is this a limitation of the Compact Engine?
"Create Table Settings " & _
"(Code nvarchar(50) Primary Key Not Null, " & _
"Type nvarchar(50) Primary Key Not Null) "
The error that is produced is "cannot parse query"
alter table settings add constraint [setPK] Primary key (Type)
The error says that the table already has a Primary Key.
Thanks