Second, the primary keys should be compact.In the database world, the artificial keys are known as surrogate keys which are as opposed to natural primary keys. In such cases, it will create many data problems. However, you don’t know when the email or phone number changes or is reused by another person. Sometimes, you may want use meaningful data, which considers being unique, for the primary keys e.g., social security number (SSN), vehicle identification number (VIN), email, and phone number. First, let’s get the name of the primary key that we want to change: SELECT SCHEMANAME (schemaid) AS SchemaName, name AS PrimaryKey FROM sys.objects WHERE parentobjectid (OBJECTID ('dbo.t1')) AND type 'PK' This is the primary key for the table called t1, So we’ll go ahead and rename that primary key. First, the primary key should be meaningless.A primary key value should not be changed over time.Īccording to these rules, the following are the recommendations for the primary keys:.A primary key value must be unique within the entire table.A primary key column cannot contain a NULL value or an empty string.The following are rules that make a column a primary key: Introduction to the primary keyĪ primary key is a column of a combination of columns in a table that uniquely identifies a row in the table.
#Mirrorsync primary key changed how to
This does exactly the same thing (renames the specified primary key).Summary: in this tutorial, you will learn how to use Oracle PRIMARY KEY constraint to manage the primary key of a table. For primary keys and other constraints, use OBJECT: EXEC sp_rename 'dbo.PK_t1_3213663B10938530', 'PK_t1', 'OBJECT' Including the Parameter NamesĪs with any stored procedure, you can also include the parameter names when calling sp_rename: EXEC = 'OBJECT' You can also include the object type as a third argument.
We can verify this by running the previous query again: SELECT Regardless, the primary key has now been renamed. This is basically giving us the heads up that any scripts and/or stored procedures that reference the object could now break, and should be updated accordingly to reflect the new name. When you rename a primary key (or any other object) in SQL Server, you’ll probably see the following message: Caution: Changing any part of an object name could break scripts and stored procedures. When you rename a primary key, you need to include the schema with the current name.Įxample: EXEC sp_rename 'dbo.PK_t1_3213663B10938530', 'PK_t1' This is the primary key for the table called t1, So we’ll go ahead and rename that primary key. WHERE parent_object_id = (OBJECT_ID('dbo.t1')) Exampleįirst, let’s get the name of the primary key that we want to change: SELECT The syntax for sp_rename goes like this: sp_renameįor primary keys (and other constraints), the object type is OBJECT.
Fortunately, sp_rename provides a quick and easy way to change this name. in your code, documentation, etc), such names can make your life more difficult. If you need to refer to that primary key (e.g. Such names typically include a long numeric suffix, which makes it harder to remember.
When you create a primary key without explicitly providing a name for it, SQL Server automatically delegates a name for it. This can be handy if you’ve got a primary key that had its name automatically assigned, and you now want to give it a more readable name. In SQL Server, you can use the sp_rename stored procedure to rename a user created object in the current database, including a primary key.