12.3 Conceptual DBMS equivalents

Table 12.1 Conceptual equivalents between supported DBMS products and MySQL

ConceptMS SQL ServerSybase ASEPostgreSQLMySQLNote
AuthenticationYesYesYesYes 
Auto_IncrementYesYesYesYesPostgreSQL uses sequences for Auto_Increment.
BackupYesYesYesYesSee MySQL Enterprise Backup
CatalogYesYesYesN/AYou can map a catalog into a schema and drop the , use the owner as the schema name or merge the owner and object name together. ownerobject
ConstraintsYesYesYesYes 
Data Dictionary   N/A 
DatabaseYesYesYesYes 
Database Instance     
DumpYesYesYesYesmysqldump
EventsYesYesYesYes 
Foreign KeysYesYesYesYes 
Full Text SearchYesYesYesYesIn InnoDB as of MySQL Server 5.6, and in all versions of MyISAM
IndexYesYesYesYes 
Information SchemaYesNoYesYes 
Object Names Case SensitivityDepends on collationDepends on collationMixedMixedMySQL: sensitivity of database, table, and trigger names OS dependent; other object names are case insensitive. PostgreSQL: as specified in the SQL-99 standard, unquoted object names are treated as case insensitive while quoted object names are case sensitive. Unlike the standard, unquoted object names are converted to lowercase instead of uppercase.
Object Naming ConventionsYesYesYesYes 
PackagesN/AN/AN/AN/A 
PartitioningYesYesYesYes 
Performance SchemaN/AN/AYesYes 
PermissionsYesYesYesYes 
Primary KeyYesYesYesYes 
Referential IntegrityYesYesYesYesSybase ASE: referential integrity only through triggers.
ReplicationYesYesYesYes 
RoleYesYesYesN/ARoles are not available in MySQL at the database level.
SchemaYesYes*YesYesEquivalent to database in MySQL. Sybase ASE: Schemata corresponds to user names.
SequencesYes*Yes*YesYes*Standalone sequence objects are not supported in MySQL. Similar functionality can be obtained with IDENTITY columns in MSSQL and AUTO_INCREMENT columns in MySQL
SQL ModesYes YesYesSET_ANSI_* in MSSQL
Storage EnginesN/AN/AYes*YesPostgreSQL itself supports and uses only one storage engine (Postgresql). Other companies have added extra storage engines to PostgreSQL.
Stored ProceduresYesYesYesYes 
SynonymsN/AN/AN/AN/A 
TableYesYesYesYes 
TablespaceYesYes*YesN/AMSSQL groups tables in schemata (unless referring to CREATE TABLESPACE). Sybase ASE: tables are grouped in schemata which are more like user names.
Temporary TablesYesYesYesYes 
TransactionsYesYesYesYes 
TriggersYesYesYesYes 
UDFsYesYesYesYes 
UnicodeYesYesYesYes 
Unique KeyYesYesYesYes 
UserYesYesYesYes 
ViewsYesYesYesYes 

Handling Microsoft SQL Server and MySQL structural differences

A Microsoft SQL Server database is made up of one catalog and one or more schemata. MySQL only supports one schema for each database (or rather, a MySQL database is a schema) so this difference in design must be planned for. The Migration Wizard must know how to handle the migration of schemata for the source (Microsoft SQL Server) database. It can either keep all of the schemata as they are (the Migration Wizard will create one database per schema), or merge them into a single MySQL database. Additional configure options include: either remove the schema names (the Migration Wizard will handle the possible name collisions that may appear along the way), and an option to add the schema name to the database object names as a prefix.