Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Single Index on multiple tables? Expand / Collapse
Author
Message
Posted Friday, July 19, 2013 6:05 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, August 3, 2014 3:41 PM
Points: 1,219, Visits: 1,430
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?
Post #1475414
Posted Friday, July 19, 2013 6:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 1:54 AM
Points: 1,191, Visits: 9,879
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.
Post #1475430
Posted Friday, July 19, 2013 6:23 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 1:14 AM
Points: 23, Visits: 575
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"
Post #1475431
Posted Friday, July 19, 2013 6:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, August 3, 2014 3:41 PM
Points: 1,219, Visits: 1,430


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.

Post #1475441
Posted Friday, July 19, 2013 7:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 1:54 AM
Points: 1,191, Visits: 9,879
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.
Post #1475470
Posted Saturday, July 20, 2013 6:22 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 803, Visits: 720
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



Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1475751
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse