Sequence as default value for a column

23
votes

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
)
3
sql-servertsqlsql-server-2012sequence

3 Answers

37
votes

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!)

13
votes

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]
-4
votes
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)
)