Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  New to SQL Server Programming  Autonumbering issue with multiple tables

Author  Topic  G1ZmO Starting Member 16 Posts Posted - 2010-10-28 : 09:38:45 I have: Stock_tbl - Contains the common data types for the items tested, technician, serial no etc UnitLookup_tbl - List of part numbers with descriptions, value etc and 5 product type tables (printers_tbl, monitors_tbl etc) which have data pertaining to the testing of that product type (pagecount for printers, screensize for monitors etc) I have Item_ID (int, autonumber) as PK on the 5 product type tables Currently I have Item_ID as a FK in the Stock_tbl which wont work as the autonumbering from the 5 tables is going to create non-uniqueness in this field. Is the following a viable solution? When an Item_ID is generated in one of the tables a script (trigger?) creates another ID based on the Item_ID but with a prefix for the product type of that table either on the fly or pulled from a table like this KeyTable AutoID MonKey NetKey SysKey 10000 M10000 N10000 S10000 10001 M10001 N10001 S10001 10002 M10002 N10002 S10002 These new keys could be used to uniquely identify all products in the stock table instead of using the Item_ID which will have duplicates from one or more of the 5 tables (Systems, Networking, Monitors, Hardware, Printers) Would this work or is there a better way? Thanks -- Paul Complete SQL newbie!