Composite Key as Foreign Key

Use Composite Primary Key as Foreign Key

The line:

FOREIGN KEY (pk_studentID ) REFERENCES student(pk_studentID ),

is wrong. You can't use pk_studentID like that, this is just the name of the PK constraint in the parent table. To use a compound Primary Key as Foreign Key, you'll have to add the same number of columns (that compose the PK) with same datatypes to the child table and then use the combination of these columns in the FOREIGN KEY definition:

CREATE TABLE files
(
  files_name varchar(50) NOT NULL, 

  batch_id varchar(4) NOT NULL,         --- added, these 3 should not
  dept_id varchar(6) NOT NULL,          --- necessarily be NOT NULL
  student_id varchar (25) NOT NULL,     --- 

  files_path varchar(50),
  files_data varchar(max),              --- varchar(max) ??   
  files_bookmarks xml,                  --- xml ??
                                        --- your question is tagged MySQL, 
                                        --- and not SQL-Server

  CONSTRAINT pk_filesName 
    PRIMARY KEY (files_name),

  CONSTRAINT fk_student_files                     --- constraint name (optional)
    FOREIGN KEY (batch_id, dept_id, student_id)  
      REFERENCES student (batch_id, dept_id, student_id)
) ENGINE = InnoDB ;

SQL: Composite primary key as foreign key

The purpose of a foreign key is to guarantee that one or more keys in one table have a corresponding row in another table. The "a" in this context is singular. And, in general, foreign keys should be using primary keys. Any unique key is allowed, but primary keys are strongly recommended.

Your data model is just begging for a counties table. At the very least:

create table counties (
     county_id int generated always as identity primary key,
     name varchar(255)
);

The county_id can then be a foreign key in both the other tables. Well, in fact, perhaps the summary statistics could also be columns in counties.

Hmmmm . . . It is possible that you just have the foreign key definitions in the wrong place. You want county to be the primary key of NYAidCrimeMean and then for NYAidCrimeTimeSeries to reference that table.

If you do take this approach, I would suggest renaming NYAidCrimeMean because the table name does not suggest that it is one row per county. At least to those not familiar with your domain.

SQL: Foreign key references a composite primary key

If you have a compound PK made up from three columns, then any child table that wants to establish a foreign key relationship must ALSO have all those 3 columns and use all 3 columns to establish the FK relationship.

FK-PK relationship is an all or nothing proposal - you cannot reference only parts of a primary key - either you reference all columns - or you don't reference.

CREATE TABLE Purchase 
(
    No_Installments int,
    Rate int,

    Person varchar(50) NOT NULL PRIMARY KEY,

    First_Name varchar(20) NOT NULL,
    Name varchar(20) NOT NULL,
    Address varchar(50) NOT NULL,

    CONSTRAINT PFK 
        FOREIGN KEY (First_Name, Name, Address) 
        REFERENCES Person (First_Name, Name, Address)
);

How to make a composite key out of two foreign keys

No, it doesn't make sense to assemble a single composite FK. Those are two separate foreign key constraints, unrelated to each other.

Your table is perfectly correct as:

CREATE TABLE Table3(
  pizza varchar(12),
  ingredient varchar(12),
  amount int,
  CONSTRAINT FK_pizzaRecipe FOREIGN KEY (pizza)
  REFERENCES Table1(pizza),
  CONSTRAINT FK_ingredientBase FOREIGN KEY (ingredient)
  REFERENCES Table2(ingredient)
);

Only one key from composite primary key as foreign key

This answer takes the question's "add a foreign key to table3" to mean that a FK (foreign key) was added in table3 referencing one of the columns of the composite PK (primary key) of table4. In standard SQL a FK can reference a proper/smaller subset of a PK.

This other answer presumably takes "add a foreign key to table3" to mean that a FK was added in table4 with one of the columns of the PK referencing table3. A FK column set in a table is independent of any PK or UNIQUE declarations in it.

---

In standard SQL a FK can reference a proper/smaller subset of a PK.

The referenced column list must be declared PRIMARY KEY or UNIQUE. (PRIMARY KEY creates a UNIQUE NOT NULL constraint.) (The constraint has to be explicit, even though any set of NOT NULL columns containing a set that is UNIQUE has to be unique.)

Unfortunately MySQL lets you declare a FK referencing a column list that is not UNIQUE. Even though such a FK or one referencing non-NULL columns (OK in standard SQL) is not implemented properly, and the documentation itself advises not doing it:

The handling of foreign key references to non-unique keys or keys that contain NULL values is not well defined for operations such as UPDATE or DELETE CASCADE. You are advised to use foreign keys that reference only keys that are both UNIQUE (or PRIMARY) and NOT NULL.

(You can ponder just what are and are not the well-defined operations, since the documentation doesn't actually clarify.)

1.8.2.3 Foreign Key Differences

13.1.18 CREATE TABLE Syntax

13.1.18.6 Using FOREIGN KEY Constraints

PS Re relational vs SQL

In the relational model a FK references a CK (candidate key). A superkey is a unique column set. A CK is a superkey containing no smaller superkey. One CK can be called the PK (primary key). When a column set's values must appear elsewhere we say there is an IND (inclusion dependency). A FK is an IND to a CK. When an IND is to a superkey we could call that a "foreign superkey".

An SQL PK or UNIQUE NOT NULL declares a superkey. It is a CK when it does not contain a smaller column set declared as SQL PK or UNIQUE NOT NULL. SQL FK declares a foreign superkey. So an SQL PK might actually be a relational PK (hence CK) and a UNIQUE NOT NULL might actually be a CK. An SQL FK to one of these actually is a relational FK.

Use composite key as foreign key in table SQL Server when we only need to reference 1 element of the key

TL;DR: You are assigning attributes to entities they don't belong to. See the diagram below.

This is how your sample model might look like:

Some details are below:

1. Your Languages table is fine.
2. Identifier of a country should not include language. It should be a single field, as countries tend not to depend on any other entities.
3. Generally, every country tends to have a single flag. You can move the actual picture into a separate table, linked 1:1 to dbo.Countries, or you can include the data into the country directly, as on the sample model.
4. Don't use image or other similar data types in new development, they have been deprecated for 15 years already.
5. List of official languages depend on country + language combination.
6. The dbo.Country table has alternative key (marked <ak> on the diagram) on the EnglishName column (because you need some way to distinguish countries from each other without resorting to the dbo.CountryLanguages table). This approach is disputable; if you don't like to hardcode any kind of language here, you can use some other natural key for a country. Off the top of the head, ISO 3166 codes might be a good choice.

Here is the DDL for the model, to make it easier to understand:

/*==============================================================*/
/* Table: Countries                                             */
/*==============================================================*/
create table dbo.[Countries] (
   [Id] int                  identity(1,1),
   [EnglishName] varchar(100)         not null,
   [FlagImage] varbinary(max)       not null,
   constraint [PK_Countries] primary key (Id),
   constraint [UQ_Countries_EnglishName] unique (EnglishName)
)
go

execute sp_addextendedproperty 'MS_Description', 
   'Country identifier',
   'schema', 'dbo', 'table', 'Countries', 'column', 'Id'
go

execute sp_addextendedproperty 'MS_Description', 
   'Country name in English',
   'schema', 'dbo', 'table', 'Countries', 'column', 'EnglishName'
go

execute sp_addextendedproperty 'MS_Description', 
   'Country flag image',
   'schema', 'dbo', 'table', 'Countries', 'column', 'FlagImage'
go

/*==============================================================*/
/* Table: CountryLanguages                                      */
/*==============================================================*/
create table dbo.[CountryLanguages] (
   [CountryId] int                  not null,
   [LanguageId] int                  not null,
   [CountryName] nvarchar(100)        not null,
   constraint [PK_CountryLanguages] primary key (CountryId, LanguageId)
)
go

execute sp_addextendedproperty 'MS_Description', 
   'Country identifier',
   'schema', 'dbo', 'table', 'CountryLanguages', 'column', 'CountryId'
go

execute sp_addextendedproperty 'MS_Description', 
   'Language identifier',
   'schema', 'dbo', 'table', 'CountryLanguages', 'column', 'LanguageId'
go

execute sp_addextendedproperty 'MS_Description', 
   'Name of the country in that language',
   'schema', 'dbo', 'table', 'CountryLanguages', 'column', 'CountryName'
go

/*==============================================================*/
/* Table: Languages                                             */
/*==============================================================*/
create table dbo.[Languages] (
   [Id] int                  identity(1,1),
   [Name] nvarchar(100)        not null,
   constraint [PK_Languages] primary key (Id)
)
go

execute sp_addextendedproperty 'MS_Description', 
   'Language identifier',
   'schema', 'dbo', 'table', 'Languages', 'column', 'Id'
go

execute sp_addextendedproperty 'MS_Description', 
   'Language name',
   'schema', 'dbo', 'table', 'Languages', 'column', 'Name'
go

alter table dbo.CountryLanguages
   add constraint FK_CountryLanguages_Countries_CountryId foreign key (CountryId)
      references dbo.Countries (Id)
go

alter table dbo.CountryLanguages
   add constraint FK_CountryLanguages_Languages_LanguageId foreign key (LanguageId)
      references dbo.Languages (Id)
go

is it ok for composite primary key to have foreign key refer to parent table

Typically speaking you are better off with a single field pk over a composite. The only real reason for making a composite would be if you need that extra field to ensure uniqueness. If you are going to have an autonumber field in child table then you already have a field that is guaranteed to be unique for each record. That is sufficient for creating whatever kind of table joins are necessary. In this scenario, creating the pk as a composite key just adds complexity without any real benefit. There are times when a composite key does make sense, but I don't see any valid reason for making one here.

---

Related Topics