What is the best way to make a simple many-to-many cross reference table which contains nothing but two columns which are themselves primary keys in other tables?
Does anyone have concrete evidence for or against creating a table with a single unique index, but no primary key? (Alternatives are detailed below).
Put another way: How does SQL Server internally uniquely identifies rows a) that have a primary key and b) that do not have a primary key?
In detail:
Given the input tables:
CREATE TABLE Foo ( FooID bigint identity(1,1) not null primary key, other stuff... )
CREATE TABLE Bar ( BarID bigint identity(1,1) not null primary key, other stuff... )
The three basic options are (in all cases assume a foreign key is created on the FooID and BarID columns):
-- Option 1: Compound primary key
CREATE TABLE FooBarXRef (
FooID bigint not null
, BarID bigint not null
, PRIMARY KEY ( FooID, BarID )
, CONSTRAINT FK... etc
)
-- Option 2: Independent primary key + unique index
CREATE TABLE FooBarXRef (
FooBarXRefID bigint identity(1,1) not null primary key
, FooID bigint not null
, BarID bigint not null
, CONSTRAINT FK... etc
);
CREATE UNIQUE INDEX I_FooBarXRef_FooBar ON FooBarXRef ( FooID, BarID );
-- Option 3: Unique index, no explicit primary key:
CREATE TABLE FooBarXRef (
FooID bigint not null
, BarID bigint not null
, CONSTRAINT FK... etc
);
CREATE UNIQUE INDEX I_FooBarXRef_FooBar ON FooBarXRef ( FooID, BarID );
Does having a separate identity PK on the xref table to be redundant; that may needlessly introduces another layer of constraint checking on the database engine?
On the other hand are multi-column primary keys problematic? With a proposed solution to have the xref table contain only the two foreign keys, and define a unique index on those columns, but not define a primary key at all... ?
I suspect that doing so will cause SQL Server to create an internal primary key for the purposes of uniquely identifying each row, thus yielding the same redundant constraints as if a primary key were defined explicitly--but I have no proof or documentation to support this. Other questions and answers suggest that there is not an internal primary key by default (i.e. no equivalent to the Oracle ROWID); as the %%physloc%% is an indicator of where a row is currently stored and thus is subject to change. My intuition is that the engine must create something to uniquely identify a row in order to implement cursors, transactions, and concurrency.
