StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWith a foreign key, can I reference a fixed value in a composite primary key?
text
Body
copied!<p>I have a database structure which includes the following table:</p> <pre><code>CREATE TABLE dbo.PaymentProvidersForEntities ( PaymentProviderId SMALLINT NOT NULL, EntityId BIGINT NOT NULL, CONSTRAINT PK_PaymentProvidersForEntities PRIMARY KEY (PaymentProviderId, EntityId), CONSTRAINT FK_PaymentProvidersForEntities_PaymentProviders FOREIGN KEY (PaymentProviderId) REFERENCES PaymentProviders(PaymentProviderId) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT FK_PaymentProvidersForEntities_Entities FOREIGN KEY (EntityId) REFERENCES Entities(EntityId) ON DELETE CASCADE ON UPDATE CASCADE ) </code></pre> <p>Obviously this is a simple many-to-many linking table with a composite primary key. I want another table which references that table but provides data for only one PaymentProvider (i.e. where PaymentProviderId = a fixed value). Something like:</p> <pre><code>CREATE TABLE dbo.SpecificPaymentProviderExtraDetails ( EntityId BIGINT NOT NULL, ExtraDetails NVARCHAR(MAX) NOT NULL, CONSTRAINT PK_PaymentProviderExtraDetails PRIMARY KEY (EntityId), CONSTRAINT FK_PaymentProviderExtraDetails_PaymentProvidersForEntities FOREIGN KEY (EntityId, 1) REFERENCES PaymentProvidersForEntities(EntityId, PaymentProviderId) ON DELETE CASCADE ON UPDATE CASCADE ) </code></pre> <p>Obviously I could just add a nullable 'ExtraDetails' field in the PaymentProvidersForEntities table but I don't find that very elegant as there will be several different types of payment providers, each requiring different types of extra details. Is there an elegant way of doing what I want? If not, then what would be a better way of achieving the same thing?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload