T-SQL中的CREATE TABLE语句问题

I am working on a pomotion database and below is what my CREATE TABLE steatment looks like:

我正在研究一个pomotion数据库,下面是我的CREATE TABLE步骤:

CREATE TABLE [dbo].[sponsors]
(
    [InstId] [bigint] NOT NULL,
    [EncryptedData] [varbinary](44) NOT NULL,
    [HashedData] [varbinary](22) NOT NULL,
    [JobId] [bigint] NOT NULL,
    CONSTRAINT [PK_sponsors] PRIMARY KEY CLUSTERED 
    (
        [InstId] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[sponsors]  WITH CHECK ADD  CONSTRAINT [FK_sponsors_jobs] FOREIGN KEY([JobId])
REFERENCES [dbo].[jobs] ([Id])
GO

ALTER TABLE [dbo].[sponsors] CHECK CONSTRAINT [FK_sponsors_jobs]
GO

ALTER TABLE [dbo].[sponsors]  WITH CHECK ADD  CONSTRAINT [FK_sponsors_titles] FOREIGN KEY([TId])
REFERENCES [dbo].[titles] ([TId])
GO

ALTER TABLE [dbo].[sponsors] CHECK CONSTRAINT [FK_sponsors_titles]
GO

And I'd like to get rid of ALTER TABLE statements and make them part of CREATE TABLE statements, I know how to do the most but not sure how to get CHECK CONSTRAINT during create table, does anyone have experience with this? or know how to?

我想摆脱ALTER TABLE语句并使它们成为CREATE TABLE语句的一部分,我知道如何做最多但不确定如何在创建表中获取CHECK CONSTRAINT,有没有人有这方面的经验?或者知道如何?

3 个解决方案

#1

You can just add each foreign key constraint right in the CREATE TABLE declaration:

您只需在CREATE TABLE声明中添加每个外键约束:

CREATE TABLE [dbo].[sponsors]
(
    [InstId] [bigint] NOT NULL,
    [EncryptedData] [varbinary](44) NOT NULL,
    [HashedData] [varbinary](22) NOT NULL,
    [JobId] [bigint] NOT NULL,
    [TId] [int] NOT NULL,
    CONSTRAINT [PK_sponsors] PRIMARY KEY CLUSTERED 
    (
        [InstId] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
           IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
           ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],

    CONSTRAINT [FK_sponsors_jobs] FOREIGN KEY([JobId]) 
                                   REFERENCES [dbo].[jobs] ([Id]),

    CONSTRAINT [FK_sponsors_titles] FOREIGN KEY([TId]) 
                                  REFERENCES [dbo].[titles] ([TId])
) ON [PRIMARY]

#2

You seem to have missed a column (TId)

你似乎错过了一个专栏(TId)

CREATE TABLE [dbo].[sponsors] 
( 
    [InstId] [bigint] NOT NULL
        CONSTRAINT [PK_sponsors] PRIMARY KEY CLUSTERED, 
    [EncryptedData] varbinary NOT NULL, 
    [HashedData] varbinary NOT NULL, 
    [JobId] [bigint] NOT NULL
        CONSTRAINT [FK_sponsors_jobs] FOREIGN KEY REFERENCES [dbo].[jobs] ([Id]), 
    [TId] int NOT NULL
        CONSTRAINT [FK_sponsors_titles] FOREIGN KEY REFERENCES [dbo].[titles] ([TId])
) ON [PRIMARY]

#3

The ALTER TABLE ... CHECK CONSTRAINT command simply enables (or disables with NOCHECK) the constraint. Constraints are enabled by default when you add them, so this extra statement is redundant and not needed if you are adding the constraints in the CREATE statement.

ALTER TABLE ... CHECK CONSTRAINT命令只是启用(或禁用NOCHECK)约束。在添加约束时,默认情况下会启用约束,因此如果要在CREATE语句中添加约束,则此额外语句是多余的,不需要。

http://msdn.microsoft.com/en-us/library/ms190273(SQL.90).aspx

#1