23

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
)
gotqn
  • 42,737
  • 46
  • 157
  • 243
Endy Tjahjono
  • 24,120
  • 23
  • 83
  • 123

3 Answers3

46

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

Endy Tjahjono
  • 24,120
  • 23
  • 83
  • 123
  • 2
    Just as a small note that might be very obvious to some... because it's using `default`, it only applies if you don't provide a value or `null`. Users could still override the auto sequence value by providing a value in their query. – Adam Plocher Jul 09 '14 at 09:36
  • 2
    Is there any way we can enforce use of the default? – Mattias Nordqvist Nov 11 '15 at 11:28
  • 1
    @MattiasNordqvist using [identity](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property) – Andre Figueiredo May 03 '17 at 19:17
13

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]
Matt Strom
  • 595
  • 8
  • 13
-4
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)
)
stollr
  • 6,534
  • 4
  • 43
  • 59
Akbar Khan
  • 2,215
  • 19
  • 27