在创建表时声明默认约束 [英] Declaring a default constraint when creating a table

问题描述

我在Microsoft SQL server 2000中创建一个新表，而不是使用GUI，我试图学习如何做到手动方式。

这是我实际使用的代码，它工作正常：

  CREATE TABLEattachments
（
attachment_idINT NOT NULL，
load_dateSMALLDATETIME NOT NULL，
userVARCHAR（25）NOT NULL，
file_nameVARCHAR NULL，
 CONSTRAINTpk_attachmentsPRIMARY KEY（attachment_id），
 CONSTRAINTfk_usersFOREIGN KEY（user）REFERENCESusers（user），
 CONSTRAINTch_load_date CHECK（load_date< GETDATE（））
）
  

主键，外键和检查约束本身，因为这样我可以为他们定义一个名称，否则声明它们内联将使SQL Server生成一个随机名称，我不喜欢它。

我试图声明默认值约束时出现问题：查看互联网上的信息以及Microsoft SLQ Server Management Studio如何创建它，我理解它可以创建内联和自己：

 load_dateSMALLDATETIME NOT NULL DEFAULT GETDATE（）
  

或

  CONSTRAINTdf_load_dateDEFAULT GETDATE FORload_date
  

内联方法工作正常，但它会像往常一样生成随机名称，独立的方法会抛出一个错误，说'FOR'附近的语法不正确。。

创建表，然后 ALTER ，命令工作：

  ALTER TABLEattachments
 ADD CONSTRAINTdf_load_dateDEFAULT GETDATE（）FORload_date
  

作为参考，这里是我试图执行的完整代码：

  CREATE TABLE附件
（
attachment_idINT NOT NULL，
load_dateSMALLDATETIME NOT NULL，
userVARCHAR（25）NOT NULL，
file_name VARCHAR（50）NOT NULL，
 CONSTRAINTpk_attachmentsPRIMARY KEY（attachment_id），
 CONSTRAINTfk_usersFOREIGN KEY（user）REFERENCESusers $ b CONSTRAINTch_load_dateCHECK（load_date< GETDATE（）），
 CONSTRAINTdf_load_dateDEFAULT GETDATE（）FORload_date
）
  
 
 
 
 
 
 
 
我完全在这里，我想要做不可能的事，或我做错了什么？
 
 
 
提前感谢Andrea。
 
 
 
 
 
 
 
  Edit： 
 
 
 
 David M展示了如何使用内联语法添加一个命名默认约束，我还在寻求理解独立语法是否完全
解决方案
与列创建并行：
  [load_date] SMALLDATETIME NOT NULL 
 CONSTRAINT [df_load_date] DEFAULT GETDATE（）
  
我使用方括号而不是引号，因为许多读者在默认情况下不能使用 QUOTED_IDENTIFIERS 。
 
I am creating a new table in Microsoft SQL server 2000 by writing the code instead of using the GUI, I am trying to learn how to do it "the manual way".

This is the code I am actually using, and it works fine:
CREATE TABLE "attachments"
(
    "attachment_id" INT NOT NULL,
    "load_date" SMALLDATETIME NOT NULL,
    "user" VARCHAR(25) NOT NULL,
    "file_name" VARCHAR(50) NOT NULL,
    CONSTRAINT "pk_attachments" PRIMARY KEY ("attachment_id"),
    CONSTRAINT "fk_users" FOREIGN KEY ("user") REFERENCES "users" ("user"),
    CONSTRAINT "ch_load_date" CHECK ("load_date" < GETDATE())
)
I have specified the primary key, foreign key and check constraints on their own because in this way I can define a name for them, otherwise declaring them inline would make SQL Server generate a random name, and I do not "like" it.

The problem arose when I tried to declare the default value constraint: looking at the informations on the internet and how Microsoft SLQ Server Management Studio creates it, I understood that it can be created both inline and on its own:
"load_date" SMALLDATETIME NOT NULL DEFAULT GETDATE()
or
CONSTRAINT "df_load_date" DEFAULT GETDATE() FOR "load_date"
The inline method works fine, but it generates as usual a random name for the constaint, the stand alone method throws an error, saying Incorrect syntax near 'FOR'..

Also, if I create the table and then ALTER it, the command works:
ALTER TABLE "attachments"
ADD CONSTRAINT "df_load_date" DEFAULT GETDATE() FOR "load_date"


As a reference, here is the full code I am trying to execute:
CREATE TABLE "attachments"
(
    "attachment_id" INT NOT NULL,
    "load_date" SMALLDATETIME NOT NULL,
    "user" VARCHAR(25) NOT NULL,
    "file_name" VARCHAR(50) NOT NULL,
    CONSTRAINT "pk_attachments" PRIMARY KEY ("attachment_id"),
    CONSTRAINT "fk_users" FOREIGN KEY ("user") REFERENCES "users" ("user"),
    CONSTRAINT "ch_load_date" CHECK ("load_date" < GETDATE()),
    CONSTRAINT "df_load_date" DEFAULT GETDATE() FOR "load_date"
)




I'm totally at loss here, is what I am trying to do not possible, or I am doing something wrong?

Thanks in advance, Andrea.





Edit:

David M showed how to add a named default constraint using the inline syntax, I am still looking to understand if the stand alone syntax is completely wrong or it is my fault.
解决方案
Do it inline with the column creation:
[load_date] SMALLDATETIME NOT NULL
        CONSTRAINT [df_load_date] DEFAULT GETDATE()
I have used square brackets rather than quotes as many readers won't work with QUOTED_IDENTIFIERS on by default.

                        
这篇关于在创建表时声明默认约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！