Foreign Key to Non-Primary Key

Foreign Key to non-primary key

If you really want to create a foreign key to a non-primary key, it MUST be a column that has a unique constraint on it.

From Books Online:

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY
KEY constraint in another table; it can also be defined to reference
the columns of a UNIQUE constraint in another table.

So in your case if you make AnotherID unique, it will be allowed. If you can't apply a unique constraint you're out of luck, but this really does make sense if you think about it.

Although, as has been mentioned, if you have a perfectly good primary key as a candidate key, why not use that?

can we have a foreign key which is not a primary key in any other table?

Yes - you can have a foreign key that references a unique index in another table.

CREATE UNIQUE INDEX UX01_YourTable ON dbo.YourTable(SomeUniqueColumn)

ALTER TABLE dbo.YourChildTable
   ADD CONSTRAINT FK_ChildTable_Table
   FOREIGN KEY(YourFKColumn) REFERENCES dbo.YourTable(SomeUniqueColumn)

Is it possible to set a non primary key as foreign key in another table?

Yes and No

SQL Server, specifically:

If you're just asking about SQL Server (and not Entity Framework), then I quoteth Microsoft's documentation:

A foreign key constraint doesn't have to be linked only to a primary key constraint in another table. Foreign keys can also be defined to reference the columns of a UNIQUE constraint in another table.

Therefore to answer your stated question directly:

• Is it possible to set a non primary key as foreign key in another table?
  
  • If they're "normal" columns, then no, because otherwise SQL Server has no way of knowing that those values will uniquely identify each row.
  • But if the columns are in either in a UNIQUE CONSTRAINT or have a non-filtered UNIQUE INDEX applied to them, then yes, you can.
  • This requires SQL Server 2005 or later. (I might be wrong, but I don't believe SQL Server 2000 supported FOREIGN KEY constraint columns referencing UNIQUE CONSTRAINT columns.

There are subtle differences between UNIQUE CONSTRAINT and UNIQUE INDEX, though the main difference is indicating intent - so you should prefer UNIQUE CONSTRAINT over UNIQUE INDEX when you're using a column - or columns - as a secondary key.

Entity Framework

• Entity Framework 6:

  • I'll admit I'm unsure. I remember having to really fight EF6 and use workarounds like VIEWs to make EF6 play-nice with FK references to UNIQUE KEY constraints, but some reports suggest that modern versions of EF6 do support this natively.

• Entity Framework Core:

  • Yes. Use HasPrincipal() with HasForeignKey() to define both a secondary-key on TPrincipal and the foreign-key on TDependant that points to that secondary-key.

---

As for your scenario, specifically: dbo.AspNetUsers:

Assuming that you want to use the Email column to uniquely identify users, then I recommend that you don't do that: Generally speaking, it's a bad idea to use mutable data (i.e. data that can change) as a key. Keys must be unique and ideally should be immutable - and while an e-mail address can be unique in some contexts (e.g. your system won't allow multiple users to share the same e-mail address) they aren't immutable (users can and do change e-mail addressees).

Another caveat: in order to use a UNIQUE CONSTRAINT and UNIQUE INDEX as a foreign-key target then those column(s) cannot be filtered or have NULLs - but dbo.AspNetUser.Email is nvarchar(256) NULL, so you'll need to ensure all users have distinct email addresses set first.

Having said that, this will work:

-- 1. Change `Email` from `NULL` to a `NOT NULL` column, as `UNIQUE CONSTRAINT` columns cannot contain duplicates or nulls:
ALTER TABLE dbo.AspNetUsers
    ALTER COLUMN [Email] nvarchar(256) NOT NULL;

-- 2. Create a UNIQUE CONSTRAINT over the `Email` column, so SQL Server knows every row has a distinct value in that column:
ALTER TABLE dbo.AspNetUsers
    ADD CONSTRAINT UQ_Email UNIQUE ( Email );

-- 3. Create a new table with a FOREIGN KEY in the other table:
CREATE TABLE dbo.OtherTable (
    Etc       nvarchar(50) NOT NULL,
    UserEmail nvarchar(256) NULL,
    
    CONSTRAINT FK_Users_by_email FOREIGN KEY ( UserEmail ) REFERENCES dbo.AspNetUsers ( [Email] )
);

Create foreign key to a non-primary key?

Yes.

From the documentation:

Finally, we should mention that a foreign key must reference columns
that either are a primary key or form a unique constraint.

Configuring one-to-many foreign key to a non primary key property

Since you don't have any navigation property on either entity, you have to use the generic version HasOne<Roles>() to indicate which table the foreign-key refers to. Once that is clear to EF, you don't need to pass anything to WithMany(). Try the following -

1. Declare RoleDisplay as an alternate-key on Roles -

modelBuilder.Entity<Roles>(e =>
{
    e.HasKey(p => p.Id);
    e.HasAlternateKey(p => p.RoleDisplay);
});

2. While configuring the foreign-key on Schedule, specify which key on Roles it should target -

modelBuilder.Entity<Schedule>(e =>
{
    e.HasKey(p => p.Id);
});
modelBuilder.Entity<Schedule>()
    .HasOne<Roles>()
    .WithMany()
    .HasForeignKey(p => p.Role)
    .HasPrincipalKey(p => p.RoleDisplay);

Hope that helps.

Rails add foreign key on non-primary key

Later this evening I seeked out some further help from the Rails IRC channel and eventually user dionysus69 pointed me to this post which was very similar to what I was looking for. For future reference this is the final solution:

class RootArea < ApplicationRecord
  has_many :common_areas, foreign_key: 'root_area_id', primary_key: 'area_id'
end

class CommonArea < ApplicationRecord
  belongs_to :root_area, foreign_key: 'root_area_id', primary_key: 'area_id', optional: true
end

Now I can successfully do

>>  RootArea.first.common_areas << CommonArea.first

and have root_area_id set to the proper area_id value instead of the id.

---

Related Topics