带冗余键的TSQL参考表

您需要为正在引用的每个表添加一列,否则,如果它们都插入到同一字段中,您将不知道ID来自何处。类似这样:

/*
CREATE TEST TABLES
*/
DROP TABLE IF EXISTS tbOne;

CREATE TABLE tbOne ( ID     INT     IDENTITY(1,1) NOT NULL      PRIMARY KEY
                   , TXT    VARCHAR(10) 
                   );

DROP TABLE IF EXISTS tbTwo;

CREATE TABLE tbTwo ( ID     INT     IDENTITY(1,1) NOT NULL      PRIMARY KEY
                   , TXT    VARCHAR(10) 
                   );

DROP TABLE IF EXISTS Target;

CREATE TABLE Target ( ID     INT    IDENTITY(1,1) NOT NULL      PRIMARY KEY
                    , FKTB1  INT
                    , FKTB2  INT
                    , TXT   VARCHAR(100) 
                    );



-- 1st FK tbOne
ALTER TABLE Target ADD CONSTRAINT FK_One FOREIGN KEY (FKTB1) REFERENCES tbOne (ID);

--2nd FK tbTwo
ALTER TABLE Target ADD CONSTRAINT FK_Two FOREIGN KEY (FKTB2) REFERENCES tbTwo (ID);



-- Populate test tables
INSERT INTO tbOne (TXT)
SELECT TOP 100 LEFT(text, 10)
FROM SYS.messages

INSERT INTO tbTwo (TXT)
SELECT TOP 100 LEFT(text, 10)
FROM SYS.messages



INSERT INTO [Target] (FKTB1, FKTB2, TXT)
SELECT 1, 1, 'Test - constraint'


-- Check result set
SELECT *
FROM tbTwo

SELECT *
FROM tbOne


SELECT *
FROM [Target] T
    INNER JOIN tbOne TB1
        ON T.FKTB1 = TB1.ID
    INNER JOIN tbTwo TB2
        ON T.FKTB2 = TB2.ID