SQL 在一个语句中添加多个约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24593101/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Add multiple constraints in one statement
提问by user3579754
I am supposed to modify my current table named MEMBER in Microsoft SQL Server 2012.
我应该修改 Microsoft SQL Server 2012 中名为 MEMBER 的当前表。
I am trying to modify the tables
我正在尝试修改表格
ALTER TABLE MEMBER
ADD CONSTRAINT U_MEMBERID UNIQUE(MEMBER_ID), primary key (MEMBER_ID);
ADD CONSTRAINT Sys_date DEFAULT GETDATE() FOR MEMBER_ID;
The above doesn't work and it says:
以上不起作用，它说：
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'CONSTRAINT'.
消息 156，级别 15，状态 1，第 3 行
关键字“CONSTRAINT”附近的语法不正确。
I think I am doing something wrong, but I am not sure what it is.
我想我做错了什么，但我不确定它是什么。
ALTER TABLE TITLE
ADD CONSTRAINT U_TITLEID UNIQUE(TITLE_ID), primary key (TITLE_ID);
add constraint C_CATEGORY CHECK(CATEGORY='DRAMA' OR 'COMEDY' OR 'ACTION'
OR 'CHILD' OR 'SCIFI' OR 'DOCUMENTARY';
Also: is it possible to add the above code to the code preceding it and execute both of them in the same sql query?
另外：是否可以将上面的代码添加到它前面的代码中并在同一个 sql 查询中执行它们？
How do I fix this?
我该如何解决？
回答by Jeroen
You have three problems:
你有三个问题：
- You terminate the statement with the
;at the end of your second line of code. - You have
FOR MEMBER_IDin the last statement, which should probably beFOR Sys_date. - You repeat
ADDbut don't have to.
;在第二行代码的末尾用 终止语句。- 您
FOR MEMBER_ID在最后一条语句中有，可能应该是FOR Sys_date. - 你重复，
ADD但不必。
Assuming this table structure:
假设这个表结构：
CREATE TABLE Member (MEMBER_ID BIGINT NOT NULL, Sys_date DATETIME);
This DDL will work:
此 DDL 将起作用：
ALTER TABLE MEMBER
ADD CONSTRAINT U_MEMBERID UNIQUE(MEMBER_ID), primary key (MEMBER_ID),
CONSTRAINT Sys_date DEFAULT GETDATE() FOR Sys_date;
See this sqlfiddle.
看到这个 sqlfiddle。
You cantheoretically also see this on the MSDN's page on ALTER TABLE, though I'd readily admit those specs can be hard to read. Here's a stab at how they explain it:
理论上，您也可以在MSDN 页面上ALTER TABLE看到这一点，尽管我很乐意承认这些规范可能难以阅读。这是他们如何解释它的一个刺：
ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name
{
ALTER COLUMN column_name
-- Omitted....
| ADD
{
<column_definition>
| <computed_column_definition>
| <table_constraint>
| <column_set_definition>
} [ ,...n ]
-- Omitted....
The ADDkeyword occurs once, and the } [ ,...n ]bit tells you that you can repeat the bit between {brackets} ntimes, separated by a ,.
该ADD关键字出现一次，而} [ ,...n ]位告诉你，你可以重复{括号}位n时间，由分离,。
回答by mehdi lotfi
ALTER TABLE MEMBER ADD CONSTRAINT U_MEMBERID UNIQUE(MEMBER_ID), primary key (MEMBER_ID);
ALTER TABLE MEMBER ADD CONSTRAINT Sys_date DEFAULT GETDATE() FOR MEMBER_ID;
