Question

How to add 1 primary key to 2 tables as foreign key?

Answer 1

Answer:

Explanation:

have two columns(ID,Code) in 1st table and make them as a primary key. In 2nd Table you also make a primary key which contain two columns(ID,Code). Foreign key must have same number of columns as primary key in references table.

In your query you must have declare 2 columns in foreign key in 2nd table like.

   create table table1

   (ID integer NOT NULL IDENTITY(1,1),    

   Code varchar(50) NOT NULL,    

   Primary key(ID,Code));

--second query:

create table table2

   (ID integer NOT NULL IDENTITY(1,1),    

   Code varchar(50) NOT NULL,    

   )

  ALTER TABLE table2

  ADD CONSTRAINT pk_table2_idCode PRIMARY KEY(ID,Code);  

   ALTER TABLE table2  

   ADD CONSTRAINT fkey FOREIGN KEY (ID,Code) REFERENCES table1(ID,Code);

create table table1

   (ID integer NOT NULL IDENTITY(1,1),    

   Code varchar(50) NOT NULL,    

   Primary key(Code));

--second query:

create table table2

   (ID integer NOT NULL IDENTITY(1,1),    

   Code varchar(50) NOT NULL,    

   )

  ALTER TABLE table2

  ADD CONSTRAINT pk_table2_idCode PRIMARY KEY(Code);  

   ALTER TABLE table2  

   ADD CONSTRAINT fkey FOREIGN KEY (Code) REFERENCES table1(Code);