在一条语句中添加多个约束 [英] Add multiple constraints in one statement

问题描述

我应该在Microsoft SQL Server 2012中修改当前名为MEMBER的表。

I am supposed to modify my current table named MEMBER in Microsoft SQL Server 2012.

我正在尝试修改表

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:

消息156，级别15，状态1，第3行

关键字'CONSTRAINT'附近的语法不正确。

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword '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';

还：是否可以将上述代码添加到其前面的代码中，并在相同的sql查询中执行它们？

Also: is it possible to add the above code to the code preceding it and execute both of them in the same sql query?

我该如何解决？

推荐答案

您遇到三个问题：

1. 在第二行代码的末尾使用; 终止语句。


2. 上一条语句中有 FOR MEMBER_ID ，可能应该是 FOR Sys_date 。


3. 您重复 ADD ，但不要。

1. You terminate the statement with the ; at the end of your second line of code.
2. You have FOR MEMBER_ID in the last statement, which should probably be FOR Sys_date.
3. You repeat ADD but don't have to.

假定此表结构为：

CREATE TABLE Member (MEMBER_ID BIGINT NOT NULL, Sys_date DATETIME);

此DDL将起作用：

ALTER TABLE MEMBER
ADD CONSTRAINT U_MEMBERID UNIQUE(MEMBER_ID), primary key (MEMBER_ID),
    CONSTRAINT Sys_date DEFAULT GETDATE() FOR Sys_date;

请参见此sqlfiddle 。

您可以理论上也可以在 ALTER TABLE 上的MSDN页面，尽管我会很容易地承认那些规格可能很难阅读。以下是他们对其解释的一种刺探：

You can theoretically 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:

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....

ADD 关键字出现一次，并且} [，... n] 位告诉您可以在{括号} n 次之间重复位，并用，隔开。

The ADD keyword occurs once, and the } [ ,...n ] bit tells you that you can repeat the bit between {brackets} n times, separated by a ,.

这篇关于在一条语句中添加多个约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！