Have an ID auto increment if not the Primary Key

Is it possible to have another field, eg, another ID, to auto increment, if the Primary Key is set to auto increment?  I assume so but what would I have to do here...set a trigger..rather not?

Thx
Glen_DAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
why do u need to auto increment columns ? oly one identity column is permitted per table, but you can have a computed column which just take the value of your identity column
0
Patrick MatthewsCommented:
You can only have one column in a table be an identity column; typically that will be your primary key,
but it does not have to be.

To get other columns to autoincrement, you are looking at triggers...
0
Patrick MatthewsCommented:
slow fingers :)
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Glen_DAuthor Commented:
I have a situation where I have a MainID & FacilityID ... the MainID is linked through numerous tables and quite a bit of code has been written for this ID; the FacilityID is set aside just for the facility table but has been used in some code.

Taking the value of the identity column may work but really looking for 2 auto incrementing fields...here's the table structure...

[dbo].[tbl_Facility](
      [FacilityID] [int] IDENTITY(1,1) NOT NULL,
      [MainID] [int] NULL,
      [FACID] [int] NULL,
      [BldID] [int] NULL,
      [BldGrpID] [int] NULL,
      [RoomID] [int] NULL,
      [Facility_Name] [varchar](max) NULL,
      [Facility_Description] [varchar](max) NULL,
      [CenterID] [int] NULL,
      [Building_Nos] [varchar](max) NULL,
      [Capability] [nvarchar](max) NULL,
 CONSTRAINT [PK_tbl_Facility_1] PRIMARY KEY CLUSTERED
0
Chris LuttrellSenior Database ArchitectCommented:
what is your other auto incrementing field?  MainID?  If it is just another name for FacilityID then do as said above and make it a computed column set equal to FacilityId.
[MainID] AS ([FacilityID])
0
Glen_DAuthor Commented:
OK...I can go with that but keep getting an error on that formula:

[MainID] AS ([FacilityID])

0
Chris LuttrellSenior Database ArchitectCommented:
I just ran this on 2008 with no problem.  What are we doing different?
CREATE TABLE [dbo].[tbl_Facility](
      [FacilityID] [int] IDENTITY(1,1) NOT NULL,
      [MainID] AS ([FacilityID]),
      [FACID] [int] NULL,
      [BldID] [int] NULL,
      [BldGrpID] [int] NULL,
      [RoomID] [int] NULL,
      [Facility_Name] [varchar](max) NULL,
      [Facility_Description] [varchar](max) NULL,
      [CenterID] [int] NULL,
      [Building_Nos] [varchar](max) NULL,
      [Capability] [nvarchar](max) NULL,
 CONSTRAINT [PK_tbl_Facility_1] PRIMARY KEY CLUSTERED (FacilityID))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chris LuttrellSenior Database ArchitectCommented:
I guessed on FacilityID being the Primary Key.  And after you do this, you do not provide a value for MainID ever, it will always be the value of FacilityID but you can use it in a query to return or select by it, etc, to work with your older code.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.