Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: 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 needed

  2. #2
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Uh Oh??????

    I just read what Brett said about the Identity key

    Quote 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?

    Code:
    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.

  3. #3
    Join Date
    Nov 2004
    Posts
    8

    Use of IDENTITY()

    Here's an example from MSDN http://msdn.microsoft.com/library/de...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]

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Number of records

    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)

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Red face Guess What??

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •