Constraint Names - Sybase SQL Anywhere to SQL Server Migration

In Sybase SQL Anywhere (Sybase ASA), constraint names are unique per table while in SQL Server they must be unique within the database.

Sybase SQL Anywhere:

  CREATE TABLE sales 
  (
     id INT,
     name VARCHAR(100),
     CONSTRAINT id_unique PRIMARY KEY (id)
   ); 
   -- Ok
 
   CREATE TABLE orders 
   (
     id INT,
     name VARCHAR(100),
     CONSTRAINT id_unique PRIMARY KEY (id)
   ); 
   -- Ok

In SQL Server this code throws an error:

SQL Server:

  CREATE TABLE sales 
  (
     id INT,
     name VARCHAR(100),
     CONSTRAINT id_unique PRIMARY KEY (id)
   ); 
   -- Ok
 
   CREATE TABLE orders 
   (
     id INT,
     name VARCHAR(100),
     CONSTRAINT id_unique PRIMARY KEY (id)
   ); 
   -- Msg 2714, Level 16, State 4, Line 1
   -- There is already an object named 'id_unique' in the database.
   -- Msg 1750, Level 16, State 0, Line 1
   -- Could not create a constraint. See previous errors.

To solve this problem you have to assign another name for the constraint, for example:

   CREATE TABLE orders 
   (
     id INT,
     name VARCHAR(100),
     CONSTRAINT orders_id_unique PRIMARY KEY (id)
   ); 
   -- Ok

Default Constraint and Table Names Conflict

By default, when you do not specify the constraint name for a foreign key, Sybase ASA uses the primary key table name as the constraint name:

Sybase SQL Anywhere:

  CREATE TABLE sales_detail
  (
     id INT REFERENCES sales(id),       -- 'sales' will be assigned as the constraint name
     name VARCHAR(100)
   ); 
   -- Ok

Then if do not use the default constraint names, and extract existing names when migrating to SQL Server you can get errors:

SQL Server:

  CREATE TABLE sales_detail
  (
     id INT CONSTRAINT sales REFERENCES sales(id),    -- 'sales' constraint extracted from the source database
     name VARCHAR(100)
   ); 
   -- Msg 2714, Level 16, State 4, Line 1
   -- There is already an object named 'sales' in the database.
   -- Msg 1750, Level 16, State 0, Line 1
   -- Could not create a constraint. See previous errors.

The reason of this error is that constraint, table and other object names must be unique within the database. In this case constraint name 'sales' conflicts with table 'sales'.

To solve this issue you can either rename the constraint or use the default name in SQL Server.

Database and SQL Migration Tools

About SQLines

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

You could leave a comment if you were logged in.