Cannot create table in SQL Compact with Foreign Key

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?

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

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.

Not this,

UsuarioRolId int foreign key references UsuarioRol(UsuarioRolId),

but this.

UsuarioRolId int references UsuarioRol(UsuarioRolId),