Thread: primary key not identity

Hey folks,

I'm trying to understand the rationale behind the way developers choose between setting the primary key as Identity=true vs false.

In the database I work with, many of the tables have a primary key that is set to not null as you'd expect, however, the "is identity" is set to false.

In the server logs when you look at the SQL statements which occur when a user (on the GUI) creates a new record, it looks like there are other statements happening before the insert query itself which asks for the Max(primarykey) in that table, then the results that are fetched are included in the insert query.

I'm wondering why they don't just set the primary key to: ID INT PRIMARY KEY IDENTITY

So for example, many of the tables are similar to this:

CREATE TABLE TEST_TABLE
(ID INT PRIMARY KEY NOT NULL, COL1 VARCHAR(10) NULL)

this looks like it would force the user to create their own primary key because the identity is not defined.

When I run the following insert and select:

INSERT INTO TEST_TABLE
(COL1)
VALUES
('TESTDATA')

SELECT * FROM TEST_TABLE

If I insert the above statements in SQL SERVER 2005, it shows an error because it says column ID can not be null. I get that, what I don't understand is why the developers would not create the table like this:

CREATE TABLE TEST_TABLE
(ID INT PRIMARY KEY IDENTITY NOT NULL, COL1 VARCHAR(10))
INSERT INTO TEST_TABLE
(COL1)
VALUES
('TESTDATA')
SELECT * FROM TEST_TABLE

This string of statements would not create an error because the ID is automatically created.

I'm just curious as it seems like not setting the ID to be the identity requires so many extra statements to occur in order to create the next consecutive number.
The database I work with does set unique primary keys, but like I said, it's through a series of preliminary select statements in the application itself that fetch the max value and then is put into the insert query.

Hope this makes sense!
Craig

Hmmmm...so setting the primary key to identity won't always be the next available number?

I realize that records that were deleted may create a skip in the next number, but I would imagine that setting to identity would incrementally increase.

Or are we talking about 2 different things?

Ah, ok...I get it.

Yeah, when I review the processes in their logs for the application,
It will go through a series of statements as I click on one action to another.

So for example, when I open a client's file, the query says,


Select Col1,Col2 etc
From Clients
where clientid=3871

Then I click on the child process (say for example to add a contact address which is in another table)

It will say

Select max(addrid) from address
addrid=4900

Insert into address (Addrid, Clientid, Col1, etc)
Values (4901,3871, 'Some Data', etc)

So it's looking like the
Select max(addrid) from address
addrid=4900

Is how the application knows to insert the next value

Perplexing to me why they'd do that, but as I have no formal educaction and
I just read online forums and books on SQL I'm sure I'm missing some advantage to doing it that way.

This all stemmed from something I was trying to do...there's a sub/child record that I want to insert into all the client files in the database but that child record also has its own child record, and neither of the sub/child tables have the primary key as Identity, which means I'd have to create a method for "knowing" what the primary keys would be manually.

I'm not going to do it because it seems too dangerous, I was just very confused at the logic for this.

*Sighs* Yeah, well I'm kind of thinking that since they have 10-15 teams of developers who have to program for both Oracle and SQL Server, all with different styles and different areas of focus for the database, that they just don't update the logic within their database as technology changes.

I'm wondering if since they started this application in the mid 90's, that perhaps it is old logic before an IDENTITY feature was created? Not even sure if that's correct, just speculating. This isn't particularly important for me as I can get around the problem that I have through other means, just trying to learn more about developers' strategies and see what I should avoid if I ever start to develop my own databases

Thanks as always!
Craig

Hey Wolffy,

I think I get it...also, I'm not exactly certain how the ID's are fetched, it's what it looks like in the server logs though. I'm pretty sure there are also a bunch of stored procedures running as well that I can't see.

So for example though, I wrote the following tables that all interact with eachother and have the same datatypes/primary key assignments as in the DB I"m working with.

Any thoughts on how, if I were developing my own DB and I didn't want to use Identity for the primary key, how I would get the sub/child tables to auto populate the foreign keys with the primary keys with another table:

TABLE 1 (CLI)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CLI](
[Clid] [int] NOT NULL,
[Com] [varchar](15) NULL,
[Last] [varchar](50) NULL,
[First] [varchar](50) NULL,
CONSTRAINT [pkCLIs] PRIMARY KEY CLUSTERED
(
[CLIId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Table 2 (COND)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[COND](
[CliId] [int] NOT NULL,
[CC] [varchar](8) NOT NULL,
[ELIId] [int] NULL,
[CONDId] [int] NOT NULL,
CONSTRAINT [pkCOND] PRIMARY KEY CLUSTERED
(
[T1EligCondId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Table 3 (Eli)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Eli](
[EliId] [int] NOT NULL,
[From] [int] NOT NULL,
CONSTRAINT [pkEli] PRIMARY KEY CLUSTERED
(
[Eli] ASC,
[From] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


T1 is the parent, T2 is a child of T1 and T3 is a child of T2. What I"m wondering is, if I make a DB similar without using Identity, how does one generate the primary keys necessary in one table to perform an insert at the same time as the record needing to be assigned in another table.

So for example, if insert a new record into T1 (Cli), a primary key is generated. So the CliID is now available for use in T2 (Cond). CliID is a FK in T2 (Cond) and is required. Then, when a record is inserted into T3 (Eli), an EliID is created which is a FK for T2 (Cond) as well.

Yes, I know my argument is circular, but that's the nature of the records which are being created. I just want to understand the processes and mess around with creating something similar so I can try my hand at these complex insert queries on my own in the future.

Thanks!
Flam