Identity Problem

I have two tables ones the documents tables which has the Identity set as the PK and it has a one to many relationship with the main table which is the employee table. My problem is I notice that when the users add a record to the primary table a number is not generated in the documents table (autonumber) but a new record is created just no new number. Admittedly I dont have alot of experience with using IDENTITY, but can anyone explain please or suggest a better way of doing this. Perhaps a document table is not neededI just read what Brett said about the Identity key

Yes a table doesn't need a primary key...but if it's for relational data you should.

Heap tables wouldn't have a PK.

And IDENTITY Column is a special property that enables a column to set an incremental value...so you can't have the same value twice.

Alot of people CONFUSE this as a Primary Key...they even go out of their way to make it one...so would you do?

CREATE TABLE State (
StateId int IDENTITY(1,1) PRIMARY KEY
, StateCd char(2)
, StateName varchar(50))

And then still have to create a unique contraint on statedCd?

Doesn't make sense does it.

Some people will argue that at some point a state will change their code, and then you could just update the state table and be done with it, and not have to update every other table that stores state code.

I find that argument amusing.|||Here's an example from MSDN http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp.

I am not sure what you mean by documents table. If this documents table is recording new entries then the INSERT values need to include the index generated through the use of IDENTITY() in the table creation and not using INDENTITY to create the documents table unless it is to index the new entry and not the employee record PK.

USE pubs
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'new_employees')
DROP TABLE new_employees
GO
CREATE TABLE new_employees
(
id_num int IDENTITY(1,1),
fname varchar (20),
minit char(1),
lname varchar(30)
)

INSERT new_employees
(fname, minit, lname)
VALUES
('Karin', 'F', 'Josephs')

INSERT new_employees
(fname, minit, lname)
VALUES
('Pirkko', 'O', 'Koskitalo')[I]|||the number of records in the Employee table are 2293 but the number of records in the EmployeeDocs table is 2107, so theres defintely a problem. the fields in the employeedocs is DocID, TM# (primary in EmployeeTbl) and Docs (which is a hyperlink)|||Ok when I enter a link into the Hyperlink then I get the Autonumber which increases the number of records in the child table.

eheheh
sorry guys