在创建表时声明默认约束 [英] 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屋!