Cannot create table in SQL Compact with Foreign Key

Question

First time using this database because I need a type that's portable and so far it's been a headache. I can't seem to figure out what's wrong with the code.

Here's what I'm trying to run - it's in Spanish but you get the gist of it:

create table UsuarioRol
(
UsuarioRolId int primary key identity(1,1),
Nombre nvarchar(64) not null,
NivelAutoridad int not null
)

create table Usuario
(
UsuarioId int primary key identity(1,1),
UsuarioRolId int foreign key references UsuarioRol(UsuarioRolId),
Login nvarchar(64) not null,
Password nvarchar(64) not null
)

I get the error:

--------------------------- Microsoft Visual Studio --------------------------- SQL Execution Error.

Executed SQL statement: create table UsuarioRol

(

UsuarioRolId int primary key identity(1,1),

Nombre nvarchar(64) not null,

NivelAutoridad int not null

)

create table Usuario

(

UsuarioId int primary key identity(1,1),,

UsuarioRolId int foreign key references Usua... Error Source: SQL Server Compact ADO.NET Data Provider Error Message: There was an error parsing the query. [ Token line number = 8,Token line offset = 1,Token in error = create ]

--------------------------- OK Help

I don't understand what might be wrong in the syntax. Am I missing something here?

Even tried this, and I get the same error.

Running the exact same TSQL on a regular ol' SQL Server database, runs perfectly.

Can I conclude that SQL Compact doesn't support foreign keys?

Answer 1

Not this,

UsuarioRolId int foreign key references UsuarioRol(UsuarioRolId),

but this.

UsuarioRolId int references UsuarioRol(UsuarioRolId),

Answer 2

I'm not sure if that syntax is supported with SQL Server CE. The following should work:

create table UsuarioRol
(
UsuarioRolId int primary key identity(1,1),
Nombre nvarchar(64) not null,
NivelAutoridad int not null
);
GO

create table Usuario
(
UsuarioId int primary key identity(1,1),
UsuarioRolId int,
Login nvarchar(64) not null,
Password nvarchar(64) not null
)
GO

ALTER TABLE [Usuario] ADD CONSTRAINT [FK_Usario_UsarioRol]
    FOREIGN KEY ([UsuarioRolId]) REFERENCES [UsuarioRol]([UsuarioRolId]);
GO

Update:

Actually, what you had should work, just remove "foreign key" in the syntax:

create table UsuarioRol
(
UsuarioRolId int primary key identity(1,1),
Nombre nvarchar(64) not null,
NivelAutoridad int not null
);
GO

create table Usuario
(
UsuarioId int primary key identity(1,1),
UsuarioRolId int references UsuarioRol(UsuarioRolId),
Login nvarchar(64) not null,
Password nvarchar(64) not null
);
GO

Or this should also work:

create table UsuarioRol
(
UsuarioRolId int primary key identity(1,1),
Nombre nvarchar(64) not null,
NivelAutoridad int not null
);
GO

create table Usuario
(
UsuarioId int primary key identity(1,1),
UsuarioRolId int,
Login nvarchar(64) not null,
Password nvarchar(64) not null,
foreign key (UsuarioRolId) references UsuarioRol (UsuarioRolId)
);
GO

Source: http://msdn.microsoft.com/en-us/library/ms173393(v=SQL.110).aspx

Answer 3

You can only run a single statement at a time with SQl Server Compact, so depending on the tool you use you must at least separate with GO and new line.