There really is an abundance of documentation and tools to do MS SQL Server to MySQL migrations - likely there would be no need to re-invent the wheel. Exporting your database tables' data to CSV just to re-import it to MySQL seems like a rather bad idea since you are going to lose all metadata (data types, constraints, indexes, views, ...).
Look at this question over at DBA.SE for a number of references to migration tools. Also, the MySQL website publishes a white paper on MS SQL migration which has some useful information about how data types and functions could be substituted and describes how MySQL Workbench could be used for migration.
While DTS could be used to perform a one-time migration, it probably is not worth the effort - it has been created with repeatable procedures in mind (i.e. database synchronization, repeated export tasks etc.). The amount of time for creating a transformation package is going to be far higher than what you are aiming for.
It looks like you've used the option to create an Identifying relationship (this is the one with the unbroken line in the toolbox).
An Identifying relationship assumes that the primary key in the referenced table should be part of primary key of the referencing table.
Because Company_id is now part of Department's Primary Key, it must also be part of the foreign key of Employee, hence your problem #1.
What you're probably wanting to do is create a Non-identifying relationship. This can be done by either selecting the dashed line from the toolbox, or unticking "Identifying relationship" in the Foreign Key tab of the Relationship properties.
A Non-identifying relationship is a classic foreign key constraint, and simply ensures that any value in the referencing table exists in the referenced table.
Technically this should resolve your problems, but there are still potential issues with the underlying database design.
For example, there's nothing to stop multiple companies existing with the same name, or multiple departments with the same name, even within the same company.
This could be solved by adding unique key constraints, but another way to tackle the same problem would be to use the Company and Department names as primary keys.
If you were doing this, you'd actually want to use an Identifying relationship, so that the Company name becomes an integral part of the Department.
(You may wish to read up on database theory and database normalisation, as it will help you avoid a lot of traps that you're likely to come up against when building a database)
I'm not suggesting that this is a better database design, simply that this is one where an Identifying relationship is valid / useful.
Best Answer
There's probably an error occurring somewhere in the process, because that would cause exactly what you're seeing.
Here's why:
When you define a view, the views or tables referenced by that view must already exist:
For this reason,
mysqldumpgenerates a dump file that includes statements defining all of the views as equivalent(ish) tables, then goes back one by one and drops the tables and creates the views. This avoids the problem that would occur if any view referenced another view, or referenced a table that hadn't been restored yet (tables are restored in alphabetical order).The error could be while generating the dump file -- if any of your existing views are invalid -- for example, they reference tables or columns that don't exist, or reference
DEFINERusers that don't exist or don't have proper permissions, or if you don't have sufficient privileges -- their definitions can't be extracted, becauseSHOW CREATE VIEWonly works on valid views.If, by way of illustration, you create a view against a table, then drop the table, you'll find that you can no longer
SHOW CREATE VIEWagainst that view.If your views reference tables in a different schema that aren't on the destination server, you'd have a similar problem, because their definitions won't be accepted.
Being someone with a strong distaste for almost all graphical tools, I can't stand Workbench, but I believe it uses
mysqldumpunder the hood, so this is likely to be the nature of the problem.The export file should be human-readable. If, at the end of the file, there are view definitions, then a problem is occurring when you're restoring the file. If not, then you've got a problem preventing the export from finishing, and Workbench may not be showing you the error... but an incomplete export (or import) would be exactly the explanation for views being defined as tables on the destination server.