sql-servert-sqlsql-server-2012sequence

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
)

Solution

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