How do I give a name to a primary key constraint?

I have (开发者_运维技巧another) question about indexing.

I use the following code:

CREATE TABLE [dbo].[PnrDetails1](
        [OId] [int] IDENTITY(1,1) NOT NULL ,
    [file_name] [varchar](256) NOT NULL,
    [gds_id] [int] NOT NULL,
    [pnr_locator] [varchar](15) NOT NULL,
    [first_cust_name] [varchar](50) NOT NULL,
    [ticket_number] [varchar](20) NOT NULL,
    [full_price] [decimal](18, 0) NOT NULL,
    [currency_desc] [varchar](4) NOT NULL,
    [user_name] [varchar](50) NOT NULL,
    [save_time] [datetime] NOT NULL,
    [update_time] [datetime] NOT NULL,
    [clerk_id] [int] NOT NULL,
    [isUpdated] [bit] NOT NULL,
    [isDeleted] [bit] NOT NULL,
    [pnr_file_id] [int] NOT NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[PnrDetails1] ADD PRIMARY KEY CLUSTERED 
(
[OId] ASC
)ON [PRIMARY]

this is actually a script sql server 2008 created for me, but when I look at the object explorer I see an ugly name for the index (something like PK_PnrDetai_CB394B1958F2C25C). How can I change it? If so?

While I agree with @marc_s that you should always declare these names up front, I disagree that you have to drop and re-create:

EXEC sp_rename 'PK_PnrDetai_CB394B1958F2C25C', 'my_new_shiny_name', OBJECT;

You can (and you should) explicitly give a name to your primary key constraint:

ALTER TABLE [dbo].[PnrDetails1] 
ADD CONSTRAINT PK_PnrDetails1
PRIMARY KEY CLUSTERED([OId] ASC) ON [PRIMARY]

You can only do this at the time of creation - so in your case, you probably have to drop it first, and then re-create it with the proper, readable name.

Or use this, if the table already exists:

DECLARE @new_pk_name VARCHAR(MAX)='pk_new_name' --here set new name of primary key
DECLARE @table VARCHAR(MAX)='dbo.table_name' --here set name of the table
DECLARE @old_pk_name VARCHAR(MAX)
SELECT
    @old_pk_name=name
    FROM sys.key_constraints
    WHERE [type] = 'PK'
    AND [parent_object_id] = OBJECT_ID(@table);
SET @old_pk_name=@table+'.'+@old_pk_name;
EXEC sp_rename @old_pk_name, @new_pk_name;
GO