SQL Server - Composite key creation

Question

I'm trying to create a composite primary key like this:

CREATE TABLE TableA (Column1 nvarchar(50) NOT NULL, Column2 nvarchar(3) NOT NULL, Column3 nvarchar(50) NOT NULL)

ALTER TABLE TableA ADD CONSTRAINT PK_AuxGroupData PRIMARY KEY CLUSTERED (Column1 , Column2)

By some reason, the second query is generating the following exception:

System.Data.SqlServerCe.SqlCeException occurred Message=The constraint specified is not valid. Source=SQL Server Compact ADO.NET Data Provider HResult=-2147217900 NativeError=25505

This happens when I run my project from Microsoft Visual Studio 2010 in Debug Mode.

My development machine has:

Microsoft SQL Server 2008 Compact 3.5 SP2 ENU
Microsoft SQL Server 2008 Compact 3.5 SP2 x64 ENU

Any help please?

score 1 · Accepted Answer · answered Jul 10 '13 at 23:08

1

Unfortunately, SQL Server Compact Edition does not support clustered indexes. This goes for the Primary Key as well.

Link showing that it does not support clustered indexes:
- http://technet.microsoft.com/en-us/library/ms345331(v=sql.105).aspx

Link showing that PRIMARY KEYs are maintained by unique indexes:
- http://technet.microsoft.com/en-us/library/ms173393.aspx

answered Jul 10 '13 at 23:08

MatBailie

83,401
18
103
137

Thats a shame... Thanks for the quick reply – RedEagle Jul 10 '13 at 23:38
This: http://stackoverflow.com/questions/1545571/how-do-i-make-a-composite-key-with-sql-server-management-studio indicates that it *is* possible. – B. Clay Shannon-B. Crow Raven Nov 19 '13 at 23:05
@ClayShannon - That question is about SQL-Server, not SQL-Server-CE specifically. This question is specifically about SQL-Serve-CE, which has much more limited functionality. – MatBailie Nov 20 '13 at 13:52
Ah, yes; I was focused on my issue, and failed to notice that. – B. Clay Shannon-B. Crow Raven Nov 20 '13 at 16:00

SQL Server - Composite key creation

1 Answers1