Best way to implement auto-increment column that can take duplicates ?

I have a table that has 2 columns composing the primary key - 1 of them I need to autoincrement if not specified, else make a duplicate entry - the other will always be specified.

example:

MainId : int
Id2 : int
SomeText : nvarchar

Primary Key is (MainId,Id2)

Examples to demonstarte functionality I need:

INSERT INTO ExampleTable (Id2,SomeText) VALUES (2414,"Hello")

should give
1 | 2414 | Hello

INSERT INTO ExampleTable (Id2,SomeText) VALUES (93284,"Something")

should then result in:
1 | 2414 | Hello
2 | 93284 | Something

INSERT INTO ExampleTable (MainId,Id2,SomeText) VALUES (1,2429,"DifText")

should give
1 | 2414 | Hello
1 | 2429 | DifText
2 | 93284 | Something

INSERT INTO ExampleTable (Id2,SomeText) VALUES (7474747,"Random")

should give
1 | 2414 | Hello
1 | 2429 | DifText
2 | 93284 | Something
3 | 7474747 |Random

Right- thats enough examples, how shall I implement it?

( the database table in question is unlikely to grow beyond 6000 rows in next 6 months & will not be quiried too often - most records will probably only be read back max 10 times each ever ! )

The database is being used by some webservices written in C#.

Cheers in advance.

mmmm, you have a abnormal looking DB structure. A table can only have one primary key.

you can concantenate two fields in a table to create a unique identifyer (is this what you mean?)

An auto incremeted primary key can not under any circumstances have a duplicate entry

your intended statement:
INSERT INTO ExampleTable (MainId,Id2,SomeText) VALUES (1,2429,"DifText")

Which ever field is your primary key (can only be one remember) does not need to be referenced in the statement. Auto incrementation means just that -you can not asign a value, this happens automaticly. If MainId was your incrementing primary key, you would:
INSERT INTO ExampleTable (Id2,SomeText) VALUES (2429,"DifText")

I hope this has been helpful.

the primary key can be composite, i.e. more than one column

Bothware:Hawk, the way you would implement it is exactly the way you wrote it

there is a wee trick to this, though:
okay, thanks, that was actually a very helpful error message, so let's try setting it...

it works!

Bothware:Hawk
r937

My apologies for mis informing you on your post. I am amazed at the ability to physicaly create a composite primary key. I have always created tables/relationships etc using the enterprize manager GUI. Whe you select a field as a primary key then try to select another it re asigns and moves the little yellow key (visually telling me the first selected key has been replaced by the most recent selection)

However running your create script, then viewing in enterprize manager show a key against each of the fields (in design view) achieving what I claimed as 'not possible'

Once again sorry bout that
Matt

Thanks.

I've been using Enterprise Manager to setup all my tables etc. you can create composite primary keys by right clicking and choosing "Indexes/Keys...".

Hmmm .. Enterprise Manager seems to provide absolutey no way to set identity_insert -- does anyone know where the option is hiding? -- it would make life easier if I could do it graphically in the future.

EDIT : just read this :
-- seems like a flawed implementation - surely it should be permanantly either set on or off ?, this is really not what I would expect - unless I am misunderstanding?????