IDENTITY Field Problems!

Question

Post reply

IDENTITY Field Problems!

Chris Roberts

SSC Veteran

Points: 200
More actions
September 9, 2004 at 3:32 am

#360865

Hi,
I have a table which has an identity field in it called ID. This is the ONLY field in the table. I want to be able to add a record to the table to generate a new sequential number. This table is then used as a kind of 'Parent' table to several other tables.
The question is, how do I get SQL Server to create the record in a table that has only 1 field in it, when that field is an identity?
Any help would be appreciated!
Thanks,
- Chris

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Tyson L. Price Mr or Mrs. 500 Points: 589 More actions · Answer 1

My first thought is to set IDENTITY_INSERT on.

This will put an initial value in the table:

SET IDENTITY_INSERT OneColumnTable On

insert into OneColumnTable(ID) values (1)

This will insert a record one higher then the max identity column.

SET IDENTITY_INSERT OneColumnTable On

Declare @InsertValue as integer

set @InsertValue = (select max(ID) from OneColumnTable)

insert into OneColumnTable(ID) values (@InsertValue +1)

SET IDENTITY_INSERT OneColumnTable OFF

Chris Roberts SSC Veteran Points: 200 More actions · Answer 2

Hi,

Thanks for your response.

I thought of doing something like that - I guess using that method we don't even need the column to be an identity - as we're always assigning the value.

Another way I thought of doing it was to add another column (a 'bit' or something small) and just insert a value into that field to trigger the creation of a new identity - but that seems a little wasteful!

Any other ideas... ?

Thanks,

- Chris

Tyson L. Price Mr or Mrs. 500 Points: 589 More actions · Answer 3

Well, I'm in the dark about what you are really trying to and just guessing that the one column table is to be used to generate a number that will be assigned as a key to others. I'm still pretty new at SQL so I'm not qualified to say what kind of DB designing that is. But, maybe you could insert a date and time stamp into the control table to generate an identity and have a meaningful piece of information in it.

You may want to look into SCOPE_IDENTITY versus IDENTITY if you are going down the path of controlling other tables like this. From what I understand it will keep you out of trouble because it is specific to your connection and will give the identity value last generated on your connection. Especially if triggeres are used.

Sorry I can't help more. I was just trying to answer your original question.

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 4

I would avoid trying to use the "SET identity ON add a record SET identity OFF"

This will cause duplicate records to get entered/records inserted out of order.

I know from personal experience.

IF you want to have a single column be your sequencer research WITH UPDLOCK. You would wrap it with a transaction. The reason for this? UPDLOCK is not friendly with other UPDLOCK and causes a "queue" effect. This way the requests are handled in the order they are received and you have unique IDs....

Good Hunting!

AJ Ahrens

webmaster@kritter.net

vinny_1973 SSCommitted Points: 1832 More actions · Answer 5

you should be able to use

INSERT INTO tablename DEFAULT VALUES

to simply generate a record.

Chris Roberts SSC Veteran Points: 200 More actions · Answer 6

That's the one I was looking for! Excellent, thanks!

- Chris

Michael Mossman Newbie Points: 4 More actions · Answer 7

I have a similar problem EXCEPT that I need to insert a record into a table with an Identity column and I want to create my own identity on the inserted record.

Manually I can do this by setting Identity Off, then Insert the record, then set Identity back on.

How can this be done wtith SQL code ? (e.g. from VB).

Any ideas please. Thanks, Mike.

Michael Mossman Newbie Points: 4 More actions · Answer 8

Sorry. I was a bit slow on the uptake here.

The answer to my problem is obviously the same advice as the first response to the original query.

Thanks anyway. Bye, Mike.

Robert Stewart Hall of Fame Points: 3399 More actions · Answer 9

Going back to good database design, this is NOT.

A table with just a primary key is meaningless. If there is nothing to group together as related, then there is no purpose for the table.

And, just because you can do it does not mean that it is right or good design.

Chris Roberts SSC Veteran Points: 200 More actions · Answer 10

Yeah - I thought that too, but it seemed to fit my needs. Since my original post however, I've worked out that it doesn't!

So - perhaps you can help with the bigger picture...?!

Basically, I'm trying to create a physical database design for a logical data model presented in a book called "Universal Data Model Resource Book".

In the logical model, there is an entity called "Person" and an entity called "Organisation". These are both related to an entity called "Party". The idea behind this is that other things like invoices can all be related to a "Party" rather than specifically linking to a person or an organisation.

What I was trying to do (at 3am after a couple of beers ) was to create a table for the "Party" which had the identity column, and link that to both the person and organisation tables. For some reason at the time I though that this was a good idea.

Any ideas of a better solution?

Thanks,

- Chris

Michael Mossman Newbie Points: 4 More actions · Answer 11

Chris,

I'm just wondering if you may be coming at this from the wrong direction.

I'm not familiar with the book you quote but, from what you say, it would appear that 'Party' is in fact a generic alias which can be used for either 'Person' or 'Organisation'.

Let's put the database to one side for the moment and concentrate on system design. It would seem reasonable to assume that any one 'Party' can only refer to any one 'Person' or 'Organisation' and never to both or more than one. It would also follow that, in order to create a 'Party' then the relevant 'Person' or 'Organisation' must first exist.

Going back to database design, this means that your 'Party' must point towards 'Person' or 'Organisation' and not the other way around. Ergo you need an Identity field plus two pointers as a minimum. When you get around to detailed design, you may also find some other common fields that you can store in 'Party' as well.

Hope this helps. Bye, Mike.

Chris Roberts SSC Veteran Points: 200 More actions · Answer 12

Thanks, Mike - that's a big help.

I think I'm looking at modelling this as a Person table with an Identity column as it's primary key, and an Organisation table with an Identity column as it's primary key.

Then - the Party table would have it's own ID, then a field to link to the other 2 tables - PersonID and OrganisationID (and any common fields).

I guess the trouble with this approach is that a single entry in the "Party" table could still relate to both a person and an organisation. This would obviously be prevented in the code for my system, but I can't help feeling that there must be a way of enforcing this kind of rule through the design of the tables.

Thanks again,

- Chris

Michael Mossman Newbie Points: 4 More actions · Answer 13

Chris,

We're in danger of drifting away from the original topic here but the answer to your new problem is to use Check Constartaints.

You actually need two pointer fields, one for Person and the other for Organisation.

The definition of these two fields is as follows :-

PersPointer Int Check (OrgPointer Is Null),

OrgPointer Int Check (PersPointer Is Null).

This will stop you trying to point to more than one entity.

Bye for now, Mike.

Robert Stewart Hall of Fame Points: 3399 More actions · Answer 14

I am not at work this week so I can look at our model on this, but I think the correct model for it would be:

tblParty

PartyID Identity, PK

PartyTypeCode char(1), FK

tlkpPartyType

PartyTypeCode char(1), PK

PartyTypeDesc varc(20)

tblOrganization

PartyID int, PK (making it a one to one with tblParty) ....Other fields as needed

tblIndividual

PartyID int, PK (making it a one to one with tblParty) ....Other fields as needed

This is one way of making it work in the manner you were originally thinking.

The other thing to think about is that sub-types and super-types, are really logical structures and are often implemented as a single table in the physical design.