Post reply

Unique column in 4 tables

  • June 18, 2008 at 5:22 am

    #192931

    Hi!

    I'm not sure how to resolve this problem:

    I've got 4 tables:

    - Table1 (UniqueCode, Col11, .. , Col1N)

    - Table2 (UniqueCode, Col21, .. , Col2M)

    - Table3 (UniqueCode, Col31, .. , Col3X)

    - Table4 (UniqueCode, Col41, .. , Col4X)

    Each TableX has the same column: UniqueCode and this column's value can't be duplicated in the tables.

    Ex.

    Insert Table1 Values ('Code1', 'Ex1', ... )

    Insert Table2 Values ('Code1', 'Ex2', ... ) -> Error!

    1) Should I control this programming a Trigger?

    2) Would be better to do a new Table?

    TableUnique( UniqueCode, IdTable1, IdTable2, IdTable3, IdTable4)

    .. for example??

    Thanks!!!

  • June 18, 2008 at 6:39 am

    #830346

    I don't think you have captured your requirements all that well in your posting.

    From what you have here, my first though would be to use a GUID - then it would be unique for all of the tables automatically. If you are not a fan of GUID's, I would go in the direction of using a prefixed code and have the prefix be unique to the table ("TableA00001", "TableB00001").

  • June 18, 2008 at 8:24 am

    #830490

    Yep... a GUID would do it...

    What do you want the unique column to look like and why? It may make a difference.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 18, 2008 at 9:22 am

    #830558

    Thanks for you reply.

    I think GIUD couldn't work.

    The four tables compound the structure (work center, area, department and sub-department) that could have a company (it'll be an accounting program).

    The unique code is an 'imputation code' that identify the distinct pieces of the structure, this code is unique for these tables. The user defines it and can change it.

    I'm not used to work with GUIDs but I think it's similar to an Identity, the database gives you the value.

    I haven't collected the requirements, so I can't do very much.

    Thanks, again, for your attention.

  • June 18, 2008 at 9:30 am

    #830571

    You need to better define requirements. If the user can change the fields or determines them, how do you determine uniqueness? You can use a GUID to guarentee uniqueness, but that doesn't mean the user has to work with the field. The database can generate it and you can store the user values.

    If you want to be sure that users only can enter unique values across multiple fields, the best thing is to check the other tables before inserting/updating and then throwing an error back to the user.

  • June 18, 2008 at 9:38 am

    #830580

    Thanks Steve, so the best solution could be a trigger..

  • June 18, 2008 at 10:51 am

    #830632

    It seems a little odd that you would need the codes to be unique across several tables like this. I think you are trying to really support some sort of entity / inheritance model. Typically, a database design for something like this would have a single table that defined the base entity and foreign keys to the non-inherited attributes. So, your unique key and all common attributes would be in a single table and you would have additional tables for the columns that were not common across all of your entities.

    Needing triggers to support uniqueness within your database is a bit a-typical. For me this would red flag the basic design. If this really is new development, you may want to go back and re-think your table structure.

  • June 18, 2008 at 12:12 pm

    #830681

    thanks again!

    The tables are in hierarchy (sub-dept under dept, dept under area and area under work-center). I could use a surrogate key for them.

    I thought in a fifth table that contained the unique code:

    UniqueCodeTable(UniqueCode)

    WorkCenter(IdWorkCenter,..., UniqueCode)

    Area(IdArea,...,UniqueCode)

    Department(IdDept, .. , UniqueCode)

    SubDepartmen(IdSubDept, .. , UniqueCode)

    In this case, I shouldn't allow the same UniqueCode in child tables through triggers.

    It sounds a bit strange...

    WorkCenter table is used for another application (that is running now) and it could contain null-value.

    I know it's an a-typical design. But I obey orders of a boss who has no-idea about design.

  • June 19, 2008 at 1:09 am

    #830923

    Hello again!

    I've been reading this article:

    http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server

    It simulates OOP's hierarchy like Michael suggested.

    It could work.

    Thanks a lot for your useful help!

  • June 19, 2008 at 8:39 am

    #831150

    Excellent reference! I've been looking to solve a similar problem, and this is it!

    muten79 (6/19/2008)

    Hello again!

    I've been reading this article:

    http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server

    It simulates OOP's hierarchy like Michael suggested.

    It could work.

    Thanks a lot for your useful help!

  • June 19, 2008 at 10:06 am

    #831218

    I've seen the same issue solved as follows:

    You could have a table that stores a global unique id. Start it with a value of 1.

    Create a stored procedure that returns the current id and then increments the global key.

    When you want to insert into one of those tables you can use the stored procedure to get the next global key.

  • June 20, 2008 at 12:34 am

    #831512

    Hi ggraber, thanks for your reply.

    I've used your solution sometimes to enumerate documents, but this issue is special because the key is given by the user.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply