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
)
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!)
3
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]
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)
)
2