Primary key and constraints - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Primary key and constraints

Author

Topic

knight1321
Starting Member

2 Posts

Posted - 2011-09-22 : 22:50:52

Good Day!

I am new to SQL Server. I have a question regarding on how to add an additional Primary to a table with existing constraints. The Create table is like this

quote:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FCT_SALE_HEADER](
[DATE_KEY] [int] NOT NULL,
[STORE_KEY] [int] NOT NULL,
[TILL_KEY] [int] NOT NULL,
[INVOICE_NO] [varchar](20) NOT NULL,
[SALE_INVC_TYPE] [varchar](50) NOT NULL,
[CUSTOMER_KEY] [int] NOT NULL,
[CASHIER_KEY] [int] NOT NULL,
[TIME_KEY] [int] NOT NULL,
[STORE_CODE] [varchar](20) NOT NULL,
[FLG_FRANCHISE] [smallint] NULL,
[CUSTOMER_CODE] [varchar](20) NULL,
[EMPLOYEE_CODE] [varchar](20) NULL,
[SALE_TOT_QTY] [numeric](12, 3) NULL CONSTRAINT [DF__FCT_SALE___SALE___6B99EBCE] DEFAULT ((0)),
[SALE_NET_VAL] [numeric](16, 4) NULL CONSTRAINT [DF__FCT_SALE___SALE___6C8E1007] DEFAULT ((0)),
[SALE_RCVD_VAL] [numeric](16, 4) NULL CONSTRAINT [DF__FCT_SALE___SALE___6D823440] DEFAULT ((0)),
[SALE_TOT_TAX_VAL] [numeric](16, 4) NULL CONSTRAINT [DF__FCT_SALE___SALE___6E765879] DEFAULT ((0)),
[SALE_TOT_DISC_VAL] [numeric](16, 4) NULL CONSTRAINT [DF__FCT_SALE___SALE___6F6A7CB2] DEFAULT ((0)),
[SALE_TOT_ITEM_COUNT] [int] NULL CONSTRAINT [DF__FCT_SALE___SALE___705EA0EB] DEFAULT ((0)),
[SALE_TOT_PACK_COUNT] [int] NULL CONSTRAINT [DF__FCT_SALE___SALE___7152C524] DEFAULT ((0)),
[LOYALTY_TRANS_FLG] [smallint] NULL,
[LOYALTY_BONUS_POINTS] [int] NOT NULL,
[LOYALTY_STD_POINTS] [int] NOT NULL,
[ARC_DATE] [datetime] NULL,
CONSTRAINT [PK__FCT_SALE_HEADER__71F1E3A2] PRIMARY KEY CLUSTERED
(
[DATE_KEY] ASC,
[STORE_KEY] ASC,
[TILL_KEY] ASC,
[INVOICE_NO] ASC,
[SALE_INVC_TYPE] ASC,
[CUSTOMER_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG_FCT_INTR]
) ON [FG_FCT_INTR]

GO
SET ANSI_PADDING OFF

My question is, how can I add the CASHIER_KEY field to the existing list of my primary keys and will not affect the existing data.

Thanks and regards,
Joseph

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-22 : 23:01:56

[code]
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='PK__FCT_SALE_HEADER__71F1E3A2' AND TABLE_NAME='FCT_SALE_HEADER'
ALTER TABLE [dbo].[FCT_SALE_HEADER] DROP CONTSTRAINT [PK__FCT_SALE_HEADER__71F1E3A2]

ALTER TABLE [dbo].[FCT_SALE_HEADER] ADD CONSTRAINT [PK__FCT_SALE_HEADER__71F1E3A2]PRIMARY KEY CLUSTERED
(
[DATE_KEY] ,
[STORE_KEY] ,
[TILL_KEY] ,
[INVOICE_NO] ,
[SALE_INVC_TYPE] ,
[CUSTOMER_KEY] ,
CASHIER_KEY
)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

knight1321
Starting Member

2 Posts

Posted - 2011-09-23 : 00:46:18

Thanks a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 01:01:55

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources