Single Index on multiple tables?

  • This is a long shot, but is it possible to create an index that references columns on two tables.

    Scenario: The application tracks internal and external items in separate tables - a new requirement is a reference number that must be unique in both internal and external tables.

    So we have a unique index on both tables, and a check trigger to make sure reference numbers in insert/updates don;t exist on the other table,

    So that's 4 T-SQL objects for one business requirement, and its causing maintenance issues.

    Is there an easier way? - perhaps something to do with indexed views?

  • Yep, indexed views are the answer. You can join the two tables in a view and create a unique clustered index on the view for the required columns. It will then throw a unique constraint violation when either table is updated in a way that violates the constraint.

    What are you struggling with?

    edit: Actually, surely it would be more simple to just increment your numbers in the two tables from a different range and use a check constraint on both tables to ensure there is no overlap.

  • Are there any other requirements related to that "reference number"?

    If it is just an integer, then perhaps you could use a SEQUENCE, which is a new feature in SQL Server 2012.

    EDIT: HowardW's answer is more to the point, related to the QUESTION posted 😉

    "Walking on water and developing software from a specification are easy if both are frozen." -- Edward V. Berard, "Life-Cycle Approaches"

  • Unfortunately its a complex alphanumeric, not an integer sequence

    I was a bit reticent to try indexed views because last time I did that I disappeared down a rabbit hole, and only emerged after it turned out that it wasn't possible for what I was trying to do: Something to do with SCHEMABINDING being required on the view, but not allowed on the underlying tables or something like that. In retrospect what I really needed in then was partial indexes - which weren't possible under 2005.

    Anyway thanks for the vote of confidence on indexed views, I'll give it a go.

  • Tom Brown (7/19/2013)


    Unfortunately its a complex alphanumeric, not an integer sequence

    Not sure why that matters. Whatever you're using to generate your references, you should be able to define a range for each table? Whether they're to start with a different letter, or number, or one be odd/one even for the numeric part or whatever meets the requirements for that field? That would be best for performance.

    Thinking about this further, the restrictions of indexed views make this difficult. If you can't use a check constraint and control your inputs, then a separate table with the reference as the PK, referencing it as an FK in the other two tables would be more appropriate.

  • No, indexed views does not cut it. You would need a view that performs a UNION ALL of the two tables, but UNION is not a permitted construct in an indexed view.

    What you can do - and which I think is a better option anyway - is to have a supertable that holds the key values. This table could also hold other attributes that are common to external and internal data. To make sure that the same key value is not in both table, you can have type column and there is a trick that I learnt from Joe Celko: have a PK on the key itself, and a UNIQUE constraint on the keycolumn and the type column. Then set up FK constraints in the child tables. The script below illustrates:

    CREATE TABLE keytable (keycol int NOT NULL,

    typecol tinyint NOT NULL

    CONSTRAINT ckc_keytable_typecol CHECK (typecol IN (1, 2)),

    CONSTRAINT pk_keytable PRIMARY KEY (keycol),

    CONSTRAINT u_keytable UNIQUE (keycol, typecol)

    )

    go

    CREATE TABLE externaldata (keycol int NOT NULL,

    typecol tinyint NOT NULL

    CONSTRAINT ckc_externalcol_typecol CHECK (typecol = 1)

    CONSTRAINT def_externalcol_typecol DEFAULT 1,

    othercols nvarchar(200) NULL,

    CONSTRAINT pk_externaldata PRIMARY KEY (keycol),

    CONSTRAINT fk_externaldata_keytable FOREIGN KEY (keycol, typecol)

    REFERENCES keytable (keycol, typecol) ON DELETE CASCADE

    )

    go

    CREATE TABLE internaldata (keycol int NOT NULL,

    typecol tinyint NOT NULL

    CONSTRAINT ckc_internalcol_typecol CHECK (typecol = 2)

    CONSTRAINT def_internalcol_typecol DEFAULT 2,

    othercols nvarchar(200) NULL,

    CONSTRAINT pk_internaldata PRIMARY KEY (keycol),

    CONSTRAINT fk_internaldata_keytable FOREIGN KEY (keycol, typecol)

    REFERENCES keytable (keycol, typecol) ON DELETE CASCADE

    )

    go

    DROP TABLE externaldata, internaldata, keytable

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

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