我有兩個表：
表格1
idproduct (PK)
des_product
表2
idclient (PK)
des_client
我需要創建一個這樣的表：
表3
idproduct (FK)(PK)
idclient (FK)(PK)
des_anything
uj5u.com熱心網友回復：
使用“外部約束”語法：
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
);
具有命名約束的更健壯的語法：
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<>小提琴演示
uj5u.com熱心網友回復：
這是一個帶有級聯洗掉的物體框架完整示例：
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]
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/363711.html
標籤：sql sql-server 数据库