SQL Server Performance Forum – Threads Archive

problem with named "default constraint"

hi all, i need to have named constraints in my create table scripts.
therefore, i need to do the following: CREATE TABLE users (
id int identity,
username varchar(255) NOT NULL,
password varchar(255) NOT NULL,
realname varchar(255) NOT NULL,
human bit NOT NULL,
CONSTRAINT PK_users
PRIMARY KEY(id),
CONSTRAINT UQ_users_username
UNIQUE(username),
CONSTRAINT DF_users_human
DEFAULT ‘true’ FOR human
); however, it fails on the last constraint creation.
isn’t it possible to create named constraints this way?

---

i know i can also do it in two separate statements, but that looks a bit silly.
i need it in 1 statement

---

Default constraints are column constraints, so you don’t add them at the end of the table definition, but within the column definition: CREATE TABLE users (
id int identity,
username varchar(255) NOT NULL,
password varchar(255) NOT NULL,
realname varchar(255) NOT NULL,
human bit CONSTRAINT DF_users_human DEFAULT (1) NOT NULL,
<other_columns>,
CONSTRAINT PK_users PRIMARY KEY(id), CONSTRAINT UQ_users_username UNIQUE(username)) Note that you cannot use ‘true’ as a default value for a bit column – it has to be 1 (or 0 for false). There is no error when you create the table with ‘true’ (including those quotes) – the error occurs only when you insert rows into the table in a way that invokes the default. Books Online for SQL 2000, for the CREATE TABLE syntax, mentions
Perhaps SQL 2005 has dropped named defaults?

---

I notice you have animal users as well? Or astral bodies?

---

]]>