Changing Column to use Default as sequence object

Here we are going to check, how we can create default constraint, as a sequence to an existing column on a table object. For our demonstration we are creating a small table with just two column id and name. ID is the field on which we will create default constraint later in this article.

create table tempAddSequence

(id int,

name varchar(20))

Command(s) completed successfully.

Lets insert few rows into tempAddSequence table object.

insert into tempAddSequence values(2,'Amrapali')

insert into tempAddSequence values(5,'Mahin')

select * from tempAddSequence

id          name

----------- --------------------

2           Amrapali

5           Mahin

(2 row(s) affected)

Now our table is populated with some rows, lets create a sequence object starts with 4 and increment by 1.

create sequence seq_Counter

as int

       minvalue 1

       no maxvalue

       start with 4

       increment by 1

       ;

go

Command(s) completed successfully.

Our Sequence object is ready to consume, its now turn to create default constraint on ID column of tempAddSequence table.

alter table tempAddSequence

add constraint id_default_sequence

default (next value for seq_Counter) for id;

Command(s) completed successfully.

Our Default constraint is now in place, associated with column ID. Now we are ready to insert few more records in tempAddSequence table, here won’t insert explicit value for Id field of table.

insert into tempAddSequence(name)

select 'Aashiyana' union all

select 'Anvi'

(2 row(s) affected)

Lets check out the insertion by querying the table

select * from tempAddSequence

id          name

----------- --------------------

2           Amrapali

5           Mahin

4           Aashiyana

5           Anvi

(4 row(s) affected)

Conclusion: we can create default constraint with sequence object on existing table with existing data.

Go Go, its your SQL soul calling :)

  

Post Reference: Vikram Aristocratic Elfin Share