How do I create a table with a primary key using two foreign keys?-CodePudding

I have two tables:

Table1

idproduct (PK)

des_product

Table2

idclient (PK)

des_client

I need to create a table like this:

Table3

idproduct (FK)(PK)

idclient (FK)(PK)

des_anything

CodePudding user response：

Using "out-of-line constraint" sytnax:

CREATE TABLE T3 (
  idproduct INT NOT NULL REFERENCES t1(idproduct),  -- in-line FK syntax
  idclient INT NOT NULL REFERENCES t2(idclient),
  anyt_other_column VARCHAR(10),
  PRIMARY KEY(idproduct, idclient)   -- out-of-line PK syntax
);

More robust syntax with named constraints:

CREATE TABLE T3 (
  idproduct INT NOT NULL,
  idclient INT NOT NULL,
  anyt_other_column VARCHAR(10),
  CONSTRAINT PK_T3    PRIMARY KEY (idproduct, idclient),
  CONSTRAINT FK_T3_T1 FOREIGN KEY (idproduct) REFERENCES t1(idproduct),
  CONSTRAINT FK_T3_T2 FOREIGN KEY (idclient) REFERENCES t2(idclient)
);

db<>fiddle demo

CodePudding user response：

Here a entity framework full example with cascade delete:

CREATE TABLE [dbo].[Table3](
    [idproduct] [int] NOT NULL,
    [idclient] [int] NOT NULL,
    -- other fields
    -- [des_anything] ....
CONSTRAINT [PK_Table3] PRIMARY KEY CLUSTERED 
(
    [idproduct] ASC,
    [idclient] ASC
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Table3_idclient] ON [dbo].[Table3]
(
    [idclient] ASC
) ON [PRIMARY]

ALTER TABLE [dbo].[Table3]  WITH CHECK ADD  CONSTRAINT [FK_Table3_Table1_idproduct] FOREIGN KEY([idproduct])
REFERENCES [dbo].[Table1] ([idproduct])
ON DELETE CASCADE

ALTER TABLE [dbo].[Table3] CHECK CONSTRAINT [FK_Table3_Table1_idproduct]

ALTER TABLE [dbo].[Table3]  WITH CHECK ADD  CONSTRAINT [FK_Table3_Table2_idclient] FOREIGN KEY([idclient])
REFERENCES [dbo].[Table2] ([idclient])
ON DELETE CASCADE

ALTER TABLE [dbo].[Table3] CHECK CONSTRAINT [FK_Table3_Table2_idclient]