CODEWITHSUNDEEP
sqlsql-serverforeign-keysconstraints
NoPyGod
NoPyGod

Reputation: 5067

SQL constraint to prevent mismatched foreign keys

I have three tables, Customer, PhoneNumber and PhoneCall.

PhoneNumber contains phone numbers, and each phone number belongs to a customer.

PhoneCall contains a log of calls and references the customer the call was made to, along with the phone number that was called.

I want to create a constraint which prevents entries from being made into the PhoneCall table where the referenced PhoneNumber does not belong to the referenced Customer.

Here are my tables:

Customer
    Id
    Name

PhoneNumber
    Id
    CustomerId (fk to Customer.Id)
    AreaCode
    Number

PhoneCall
    CustomerId (fk to Customer.Id)
    PhoneNumberId (fk to PhoneNumber.Id)
    Description
    Duration

Example customers and phone numbers:

Customers = [
    { Id: 1, Name: 'Bob' },
    { Id: 2, Name: 'Richard' },
];

PhoneNumbers = [
    { Id: 1, CustomerId: 1, AreaCode: 'xxx', Number: 'xxxx' },
    { Id: 2, CustomerId: 2, AreaCode: 'yyy', Number: 'yyyy' }
];

I want to prevent an entry like this:

PhoneCall = {
    CustomerId: 1,
    PhoneNumber: 2,
    Description: 'call to customer',
    Duration: 5
}

This seems like something that should be easy to solve with a constraint, but I'm really struggling to figure out how it should be done. Is there a type of constraint to solve this?

Update

Thanks for the answer Kentaro.

What I like the most about this is it means I can log phone calls with PhoneNumberId set to null if no phone number was specified.

Here's the queries I used to add the constraint

ALTER TABLE [PhoneNumbers] ADD UNIQUE ([Id], [CustomerId]);
ALTER TABLE [dbo].[PhoneCalls] ADD  CONSTRAINT [FK_dbo.PhoneCalls_dbo.ReferencedPhoneNumberMustBelongToReferencedCustomer] FOREIGN KEY([PhoneNumberId], [CustomerId]) REFERENCES [dbo].[PhoneNumbers] ([Id], [CustomerId]);

Upvotes: 1

Views: 70

Answers (2)

Kentaro
Kentaro

Reputation: 216

A Foreign Key constraint can easily manage this (which you already have as a tag). All you have to do is slightly change the table definition and the foreign key reference in the PhoneCall table so that it will reference CustomerID in the PhoneNumber table. But in order to do so you'll have to change the table definition of both PhoneNumber and PhoneCall a little because a foreign key needs to be either a primary key or a candidate key(i.e. unique) in the referencing table. So with the provided table definitions, in TSQL recreating those table would look like this (with assumed datatype).

CREATE TABLE PhoneNumber(
    Id INT UNIQUE,
    CustomerId INT UNIQUE REFERENCES Customer(ID),
    AreaCode INT(3),
    Number INT(7),
    CONSTRAINT CK_PhoneID_CustID (ID, CustomerID));

CREATE TABLE PhoneCall (
    CustomerId INT REFERENCES Phonenumber(CustomerID),
    PhoneNumberId  INT REFERENCES PhoneNumber(Id),
    Description NVARCHAR(MAX),
    Duration INT);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I think the simplest solution to your problem is: Don't include CustomerId in PhoneCall table. Look it up through the PhoneNumbers table.

With only one customer id, there is no chance of conflict.

Another possibility to to build a redundant unique index on PhoneNumbers(CustomerId, PhoneNumber). Then use this index for the foreign key reference. It will require that the customer ids match.

Upvotes: 3

Related Questions