Search code examples
sqlentity-frameworksql-server-2014

Primary Key and Foreign key in same table

I am using SQL Server 2014 and Entity Framework code-first. I am trying to create a table Account Master which will have the details of the customer and columns are as below.

  • Id as autoincrement primary key
  • Name as varchar
  • City as varchar
  • phonenumber as varchar
  • BrokerId as int

Now my question is how refer to the BrokerId as foreign key to the same table name Account Master with the primary key on column Id.

Because in my case broker will be also having all this details. As well as it is not necessary that every customer will have broker. Whether I should do like this or not.

Thanks in advance.

Solution

  • Could you add a Foreign Key constraint to your CREATE TABLE statement?

    CONSTRAINT FK_AccMaster_ID FOREIGN KEY (BrokerID)
  REFERENCES AccountMaster(ID)

    This is a common way to link two tables together. Unless I am missing something in your question...