CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012sequence
Endy Tjahjono
Endy Tjahjono

Reputation: 24500

Sequence as default value for a column

I have already created a sequence:

create sequence mainseq as bigint start with 1 increment by 1

How do I use this sequence as the default value of a column?

create table mytable(
    id      bigint not null default mainseq     -- how?
    code    varchar(20) not null
)

Upvotes: 26

Views: 53582

Answers (4)

Ramil Aliyev 007
Ramil Aliyev 007

Reputation: 5460

You can do it in SQL Server Management Studio's design screen like as below:

enter image description here

Upvotes: 0

Akbar Khan
Akbar Khan

Reputation: 2415

create table users(
    u_id int identity(1,1) primary key,
    u_type varchar(30) default 'member', 
    entrydate datetime default (getdate()), 
    updatedate  datetime default (getdate()),
    isactive bit default 1,
    firstname varchar(30),
    lastname varchar(30),
    email varchar(50),
    password varchar(50)
)

Upvotes: -6

Endy Tjahjono
Endy Tjahjono

Reputation: 24500

It turned out to be easy enough:

create table mytable (
    id      bigint not null constraint DF_mytblid default next value for mainseq,
    code    varchar(20) not null
)

or if the table is already created:

alter table mytable
add constraint DF_mytblid
default next value for mainseq
for id

(thank you Matt Strom for the correction!)

Upvotes: 52

Matt Strom
Matt Strom

Reputation: 615

The ALTER statement is not quite complete. It needs another FOR clause to assign the default to the desired field.

ALTER TABLE mytable
ADD CONSTRAINT DF_mytblid
DEFAULT (NEXT VALUE FOR mainseq) FOR [id]

Upvotes: 15

Related Questions