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 needed
I just read what Brett said about the Identity key
Originally Posted by Brett Kaiser
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 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.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'new_employees')
DROP TABLE new_employees
CREATE TABLE new_employees
id_num int IDENTITY(1,1),
fname varchar (20),
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)