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
