Is_System_Named

Is_System_Named

Issue in  Database Deployment in Agile Process 

When you migrate the DDL from one server to another server in agile development then the major problem you will face because of constraints name. As all comparison tool have same problem; it will give the differences if you have only constraints name different even for same constraint. That will suggest you to delete and recreate the constraint with the source name you have. So suppose if a primary key is getting created without specifying the name then system will give one unique name to the primary key. So while comparing two database will show you differences because the name of the primary key is different even though we have same primary key (on same table , same column). Then the tool will suggest you to delete this primary key and recreate with source primary key name. As this primary key is referring many foreign key you have to first delete all the foreign key then delete the primary key and recreate the primary key and then back create all the foreign keys.

  Here even we don’t have difference in relation we are getting huge scripts to migrate whenever we try to deploy the build to another environment. I feel this is major problem for the DB build in agile model.

 To resolve this issue we should always create all the constraint with providing the explicit name.   

Suppose you have already did this mistake, or you like to know what all constraints name are system generated that will create problem while database comparison?

   The column called is_system_named of sys.foreign_keys/sys.check_constraints

 Will help you.

 Let’s look below example:

/* create table and create CHECK constraint without specifying the Constraint name */

CREATE TABLE DBO.SystemNameTest(empid int ,Sal int ,Age int check (age>18 and age<50) )

/* Look below Meta data, it will tell which one created by system or user*/

select is_system_named,* from sys.check_constraints

select is_system_named,* from sys.foreign_keys where is_system_named=1

/* created by system*/

Solution: Select all the constraint that are named by system using below script 

SELECT is_system_named

      ,NAME

FROM sys.foreign_keys

WHERE is_system_named = 1

UNION

SELECT is_system_named

      ,NAME

FROM sys.check_constraints

WHERE is_system_named = 1

And then handle it on the server; give the user define name.

<script async src="//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js"></script>
<!-- sqlserverscripthub.com -->
<ins class="adsbygoogle"
     style="display:inline-block;width:728px;height:90px"
     data-ad-client="ca-pub-7295338841549276"
     data-ad-slot="8373664349"></ins>
<script>
(adsbygoogle = window.adsbygoogle || []).push({});
</script>