Description:
I am using MySql 5.5.5 m3, Connector/Net 6.3.2, with MySql Workbench 5.2.25.6302
I am accessing from Visual Studio 2010 Professional on Windows Vista Home Premium.
I created a simple data model with two tables and one many to many association, and the created a database This normally creates a sql command file with extension .sql. I tried to execute this with MySql Workbench. The script had errors and would not execute.
When I try to do the same thing using MS SqlServer the procedure is much the same with the exception of using the SqlServer Manager.
I don't know whether the problem is in Connector/Net, MySql, or MySql Workbench.
How to repeat:
From Visual Studio connect to MySql. Create a project. Add a Ado Data Entity Model. Connect a database on the MySql server. Select update the entity from the database. Add a table with a key and an association. Right click and select generate database from model.
In MySql Workbench, open the .sql file and execute it. There are errors and the execution can not proceed.
This is the normal development cycle using the Visual Studio 2010. It should work
This is the script that was produced.
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 07/24/2010 10:10:51
-- Generated from EDMX file: C:\Visual Studio 2010 Projects\Playgrounds\MySqlPlayground\Studies\FirstTest\FirstTest\Models\Model2.edmx
-- --------------------------------------------------
SET QUOTED_IDENTIFIER OFF;
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO
-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------
-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------
IF OBJECT_ID(N'[test].[person]', 'U') IS NOT NULL
DROP TABLE [test].[person];
GO
-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------
-- Creating table 'people'
CREATE TABLE [dbo].[people] (
[FirstName] nvarchar(1000) NULL,
[LastName] nvarchar(1000) NULL,
[PersonID] int NOT NULL
);
GO
-- Creating table 'Companies'
CREATE TABLE [dbo].[Companies] (
[CompanyID] int IDENTITY(1,1) NOT NULL,
[CompanyName] nvarchar(1000) NOT NULL
);
GO
-- Creating table 'Companyperson'
CREATE TABLE [dbo].[Companyperson] (
[Companies_CompanyID] int NOT NULL,
[people_PersonID] int NOT NULL
);
GO
-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------
-- Creating primary key on [PersonID] in table 'people'
ALTER TABLE [dbo].[people]
ADD CONSTRAINT [PK_people]
PRIMARY KEY CLUSTERED ([PersonID] ASC);
GO
-- Creating primary key on [CompanyID] in table 'Companies'
ALTER TABLE [dbo].[Companies]
ADD CONSTRAINT [PK_Companies]
PRIMARY KEY CLUSTERED ([CompanyID] ASC);
GO
-- Creating primary key on [Companies_CompanyID], [people_PersonID] in table 'Companyperson'
ALTER TABLE [dbo].[Companyperson]
ADD CONSTRAINT [PK_Companyperson]
PRIMARY KEY NONCLUSTERED ([Companies_CompanyID], [people_PersonID] ASC);
GO
-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------
-- Creating foreign key on [Companies_CompanyID] in table 'Companyperson'
ALTER TABLE [dbo].[Companyperson]
ADD CONSTRAINT [FK_Companyperson_Company]
FOREIGN KEY ([Companies_CompanyID])
REFERENCES [dbo].[Companies]
([CompanyID])
ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
-- Creating foreign key on [people_PersonID] in table 'Companyperson'
ALTER TABLE [dbo].[Companyperson]
ADD CONSTRAINT [FK_Companyperson_person]
FOREIGN KEY ([people_PersonID])
REFERENCES [dbo].[people]
([PersonID])
ON DELETE NO ACTION ON UPDATE NO ACTION;
-- Creating non-clustered index for FOREIGN KEY 'FK_Companyperson_person'
CREATE INDEX [IX_FK_Companyperson_person]
ON [dbo].[Companyperson]
([people_PersonID]);
GO
-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------