Composite PK Incrementing - 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

General SQL Server Forums

Database Design and Application Architecture

Composite PK Incrementing

Author

Topic

Dufresne
Starting Member

5 Posts

Posted - 2010-05-09 : 07:37:57

Hi , I'm newbie for SQL.
I'm stuck with a problem
I've following table


Address 
(
    AddressID   TINYINT NOT NULL,
    CustomerID  INT NOT NULL,
    Address   VARCHAR(200) NOT NULL
)

AddressID and CustomerID are composite Primary Key.
CustomerID is Foreign Key to Customer Table.
What i want is automatic increment AddressID with same CustomerID

Suppose that , we'll add 3 addreses for customer whose id is 1 and 4 addresses for the customer whose id is 2.
So my table shall look like
AddressID CustomerID Address
1 1 'Address'
2 1 'blabla'
3 1 '...'
1 2 '..'
2 2 '....'
3 2 '.....'
4 2 '....'

AddressId shall increment and automatically become 1 when it's entered different CustomerID
How can i do it ?
Thanks in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-09 : 17:07:38

What version of SQL Server are you using? If 2005 or 2008, you can use the ROW_NUMBER() function.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Sheila Leverson
Starting Member

2 Posts

Posted - 2010-05-10 : 18:57:33

I don't believe you can do that "automatically". It will require some work on your part to come up with the correctly incremented address_id. Most commonly this is done in a stored procedure that handles insertion into the table.

quote:
Originally posted by Dufresne

Hi , I'm newbie for SQL.
I'm stuck with a problem
I've following table

Address
(
AddressID TINYINT NOT NULL,
CustomerID INT NOT NULL,
Address VARCHAR(200) NOT NULL
)

AddressID and CustomerID are composite Primary Key.
CustomerID is Foreign Key to Customer Table.
What i want is automatic increment AddressID with same CustomerID

Suppose that , we'll add 3 addreses for customer whose id is 1 and 4 addresses for the customer whose id is 2.
So my table shall look like
AddressID CustomerID Address
1 1 'Address'
2 1 'blabla'
3 1 '...'
1 2 '..'
2 2 '....'
3 2 '.....'
4 2 '....'

AddressId shall increment and automatically become 1 when it's entered different CustomerID
How can i do it ?
Thanks in advance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources