Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Single Index on multiple tables?


Single Index on multiple tables?

Author
Message
Tom Brown
Tom  Brown
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1342 Visits: 1466
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?
HowardW
HowardW
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1211 Visits: 9892
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.
marc.snoeys
marc.snoeys
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 849
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"
Tom Brown
Tom  Brown
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1342 Visits: 1466
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.
HowardW
HowardW
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1211 Visits: 9892
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.
Erland Sommarskog
Erland Sommarskog
SSC Eights!
SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)

Group: General Forum Members
Points: 931 Visits: 866
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search