Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server 2005 Create Table with Column Default value range

Trying to finish up some homework and ran into a issue for creating tables. How do you declare a column default for a range of numbers. Its reads: "Column Building (default to 1 but can be 1-10)" I can't seem to find ...or know where to look for this information.

CREATE TABLE tblDepartment
(
Department_ID int NOT NULL IDENTITY,
Department_Name varchar(255) NOT NULL,
Division_Name varchar(255) NOT NULL,
City varchar(255) default 'spokane' NOT NULL,
Building int default 1 NOT NULL,
Phone varchar(255)
)

I tried Building int default 1 Between 1 AND 10 NOT NULL, that didn't work out I tried Building int default 1-10, the table was created but I don't think its correct.

like image 665
Matt Avatar asked Jun 04 '10 08:06

Matt


People also ask

Is it possible to specify the default value for a column while creating table?

You can use SQL Server Management Studio to specify a default value that will be entered into the table column. You can set a default by using the SQL Server Management Studio (SSMS) Object Explorer or by executing Transact-SQL.

How do you create a column with default value in SQL?

When using CREATE TABLE , you can specify default values for columns by typing DEFAULT and then the desired value after it. If a row is inserted that does not specify a value for that column, the database will fill it in with the default value instead.

What clause allows you to specify a default value for a column?

Use the DEFAULT clause in the CREATE TABLE statement to specify the default value for the database server to insert into a column when no explicit value for the column is specified. This syntax fragment is part of the Column definition.


1 Answers

You need to add CHECK Constraint to the column.

ALTER TABLE tblDepartment
ADD CONSTRAINT chkbuilding CHECK (Building >=1 AND Building <= 10 );
like image 56
Giorgi Avatar answered Oct 03 '22 18:10

Giorgi