Databases
StackOverflow2013
Postsdbo
PODeclaring a default constraint when creating a table
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODeclaring a default constraint when creating a table
    text
    copied!<p>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".</p> <p>This is the code I am actually using, and it works fine:</p> <pre><code>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" &lt; GETDATE()) ) </code></pre> <p>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.</p> <p>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:</p> <pre><code>"load_date" SMALLDATETIME NOT NULL DEFAULT GETDATE() </code></pre> <p>or</p> <pre><code>CONSTRAINT "df_load_date" DEFAULT GETDATE() FOR "load_date" </code></pre> <p>The inline method works fine, but it generates as usual a random name for the constaint, the stand alone method throws an error, saying <code>Incorrect syntax near 'FOR'.</code>.</p> <p>Also, if I create the table and then <code>ALTER</code> it, the command works:</p> <pre><code>ALTER TABLE "attachments" ADD CONSTRAINT "df_load_date" DEFAULT GETDATE() FOR "load_date" </code></pre> <p><br /> As a reference, here is the full code I am trying to execute:</p> <pre><code>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" &lt; GETDATE()), CONSTRAINT "df_load_date" DEFAULT GETDATE() FOR "load_date" ) </code></pre> <p><br /> <br /> I'm totally at loss here, is what I am trying to do not possible, or I am doing something wrong?</p> <p><br /> <strong>Edit:</strong></p> <p>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.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload