SQL Server - Create an identifying relationship

Question

I'm currently designing a database to be implemented in SQL Server. I created the following tables without problem:

CREATE TABLE [Client] (
  [ClientId] INT NOT NULL,
  [Name] VARCHAR(45) NOT NULL,
  [IsEnabled] BIT NOT NULL DEFAULT 1,
  CONSTRAINT PK_TCASystem PRIMARY KEY CLUSTERED (
ClientId
 )
);

 CREATE TABLE [Configuration] (
  [ConfigId] INT NOT NULL,
  [ClientId] INT NOT NULL,
  [Name] VARCHAR(45) NOT NULL,
  CONSTRAINT PK_Configuration PRIMARY KEY CLUSTERED (
    ConfigId, ClientId
  ),
  CONSTRAINT "FK_SystemConfiguration" FOREIGN KEY 
  (
    ClientId
  ) REFERENCES [Client] (
    ClientId
  )
 );

However, when I tried to add this one:

CREATE TABLE [Mail] (
   [MailId] INT NOT NULL,
   [ConfigId] INT NOT NULL,
   [Recipient] VARCHAR(500) NOT NULL,
   [Sender] VARCHAR(50) NOT NULL,
   [Subject] VARCHAR(250) NOT NULL,
   [Message] TEXT NULL,

   CONSTRAINT PK_Mail PRIMARY KEY CLUSTERED (
    MailId, ConfigId
   ),
   CONSTRAINT "FK_ConfigurationMail" FOREIGN KEY 
   (
        ConfigId
   ) REFERENCES [Configuration] (
        ConfigId
   )
);

I got an error saying that There are no primary or candidate keys in the referenced table 'Configuration' that match the referencing column list in the foreign key 'FK_ConfigurationMail'. I believe this is because the constraint is trying to reference ConfigId, only one half of the composite key, and for this to work I'd need to reference the ClientId too, is that correct?

But my problem is that I first did the design for this database in MYSQL Workbench, and there I indicated that Configuration and Mail, as well as Client and Configuration, have a 1:n identifying relationship (because a Mail instance cannot be created if there isn't a Configuration instance first, and at the same time a Configuration instance cannot exist without having being assigned to a Client first), and as such it created the composite keys for Configuration and Mail. You can see a picture of that here.

So my question is, how can I translate this identifying relationship to SQL Server? Or is that not possible?

EDIT: As suggested I will remove the composite keys from the Configuration table, albeit my question still stands: If I have a 1:n identifying relationship where one of the tables involved uses composite keys, how can I display this on SQL Server? Or is such a case never supposed to happen?

2ND EDIT: To anyone who might come across this question, this post is well worth a read. Cleared up all my confusion in the matter.

Let me see if I understand. A configuration instance can't exist without **first** being assigned to a client?? — Lamak, Mar 14 '14 at 19:25
That's right. Changed the wording in case it was too ambigous. — Ana Ameer, Mar 14 '14 at 19:28
I still read it the same way. It doesn't really make much sense to me, how is it possibly that a configuration instance can't exists without first being assigned to a client? — Lamak, Mar 14 '14 at 19:30
Does it make sense to have multiple identical ConfigIds? That is allowed at the moment. — usr, Mar 14 '14 at 19:40
@usr Identical? It should be that a Client can have more than one Configuration... but where does it say that they could be identical? — Ana Ameer, Mar 14 '14 at 19:41
Because logically if you want to assign a configuration instance to a client, then that configuration instance **should** exist first, not the other way around — Lamak, Mar 14 '14 at 19:42
Also, your design should have a `Client` table (with `ClientId` alone being the primary key), a `Configuration` table (with `ConfigurationId` alone being the primary key), **and** a `ClientConfiguration` table that relates them (with `ClientId, ConfigurationId` as the composite primary key) — Lamak, Mar 14 '14 at 19:45
@Lamak But a configuration can only belong to one client at a time, shouldn't it remain as a 1:n relationship then? Although what you mentioned about Configuration needing to exist before a Client makes sense, I'll have to check that with my manager. — Ana Ameer, Mar 14 '14 at 19:48
@AnaAmeer It's still a good idea to have those on a different table in the case of sometime in the future changing that restriction. That also makes it not necessary to update the `client` table with the `Configuration` information, and have those concepts separated — Lamak, Mar 14 '14 at 19:51
Your model is fine, just remove the composite keys. Having a `ClientConfiguration` would only be needed if you need the same configuration for multiple clients. — JodyT, Mar 14 '14 at 19:53
Thank you both for the suggestions, but my original question remains unsolved. Or is a case like this not intended to ever happen? — Ana Ameer, Mar 14 '14 at 20:01
You are currently saying that the `Configuration` table could have more than one identical value for `ConfigurationId` (since it has a composite primary key). In SQL Server (and it should be this way in every RBDMS, not sure why MySQL allows it), you can't reference a part of a primary key, it needs to reference the whole primary key — Lamak, Mar 14 '14 at 20:07
@AnaAmeer Check my solution. Foreign key must reference PK or unique index. So add this between create table [Configuration] and [Mail]. It is working in sql fiddle. — Jian Huang, Mar 14 '14 at 20:08

Jian Huang · Accepted Answer · 2014-03-14T20:27:52.133

0

Foreign key must reference PK (the entire PK, not portion of PK) or unique index. So add this between create table [Configuration] and [Mail].

CREATE UNIQUE NONCLUSTERED INDEX [UX_Configuration] ON [Configuration] ( [ConfigId] ASC )

Check out at sql fiddle for the whole working script: http://www.sqlfiddle.com/#!3/8877f

edited Mar 14 '14 at 20:27

answered Mar 14 '14 at 20:02

Jian Huang

1,155
7
17

After everyone's suggestions I ended up changing the design of the table, but thank you for the answer! – Ana Ameer Mar 14 '14 at 20:38

SQL Server - Create an identifying relationship

1 Answers1