在两个表之间创建主键/外键关系

I am setting up some very simple test tables and would like to make the column USERKEY the primary key of my table tb_TestUSERS then make the column USERKEY the foreign key in the table tb_TestFACT. Then set up a relationship between this primary key and foreign key. I'd like to be able to do all of this using scripts.

我正在设置一些非常简单的测试表，并希望将列USERKEY作为我的表tb_TestUSERS的主键，然后将列USERKEY作为表tb_TestFACT中的外键。然后在此主键和外键之间建立关系。我希望能够使用脚本完成所有这些操作。

So far I just have the basic table scripts:

到目前为止，我只有基本的表脚本：

CREATE TABLE WH.dbo.tb_TestFACT
(DATEKEY INT,USERKEY INT);
INSERT INTO WH.dbo.tb_TestFACT
    values
    (1,1),
    (2,1),
    (3,2),
    (4,2),
    (5,2),
    (6,3),
    (7,3);

CREATE TABLE WH.dbo.tb_TestUSERS
(USERKEY INT,NAME VARCHAR(10));
INSERT INTO WH.dbo.tb_TestUSERS
    values
    (1,'FRED'),
    (2,'PHIL'),
    (3,'JACKO'); 

2 个解决方案

#1

CREATE TABLE tb_TestUSERS
(
    UserKey INT NOT NULL,
    Name VARCHAR(30),
    CONSTRAINT tb_pk PRIMARY KEY (UserKey),
    CONSTRAINT tb_uq UNIQUE (Name)
)
GO

CREATE TABLE tb_TestFACT
(
    UserKey INT NOT NULL,
    DateKey INT NOT NULL,
    CONSTRAINT tb_fk FOREIGN KEY (UserKey)
        REFERENCES tb_TestUSERS(UserKey),
    CONSTRAINT tb_uq1 UNIQUE (UserKey, DateKey) 
)
GO

• SQLFiddle Demo
• SQLFiddle演示

#2

If you have SQL Server Management Studio or SQL Server Management Studio Express then you can actually get it to do it for you. Alternatively, you can do this in the UI and get it to script it to a new window - all very useful facilities.

如果您有SQL Server Management Studio或SQL Server Management Studio Express，那么您实际上可以让它为您完成。或者，您可以在UI中执行此操作并将其编写到新窗口 - 所有非常有用的工具。