Cannot create default constraint with sequence object on global temporary table

I was toying with SQL sequence object and all of sudden I was surprise to find that we cannot create default constraint with sequence object on Global temporary table. Let me demonstrate my finding with an example.

Here we are creating a global temporary table with two fields

create table ##temoAddSequence

(id int,

name varchar(20))

Command(s) completed successfully.

Lets insert few records in it.

insert into ##temoAddSequence values(2,'Amrapali')

insert into ##temoAddSequence values(5,'Mahin')

lets create a sequence object

create sequence seq_IdCounter

as int

       minvalue 1

       no maxvalue

       start with 4

       increment by 1

       ;

go

Command(s) completed successfully.

Lets try to associate this sequence object to Id column of ##temoAddSequence table

alter table ##temoAddSequence

add constraint id_default_sequence

default (next value for seq_Counter) for id;

Msg 208, Level 16, State 1, Line 19

Invalid object name 'seq_Counter'.

Conclusion: We cannot create default constraint with sequence object on Global temporary table.

When you are on my head SQL, I abscond from all tasks, to shower my love to you  :)

Post Reference: Vikram Aristocratic Elfin Share

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

Reusability, create a global default value and bind it to various tables

Here in this article we will see, how we can create default value and bind it to various table fields.

So First we will be creating a User defined datatype if int basic type.

sp_addtype 'pin_code_type', int, null

Command(s) completed successfully.

Now lets create a Default and assign a default value.

create default pin_default as 326595

Command(s) completed successfully.

Now lets bind the Default value to the User defined Data type

sp_bindefault 'pin_default', 'pin_code_type'

Command(s) completed successfully.

Default bound to data type.

The new default has been bound to columns(s) of the specified user data type.

Lets create table and bound the  column to user defined data type ‘pin_code_type’

create table temp

(id int,

zip pin_code_type)

Command(s) completed successfully.

insert into temp(id) values(1)

select * from temp

id          zip

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

1           326595

1           326595

Lets create on more Table an assign the default variable to  the table column.

create table temp2

(id int identity(1,1),

c_address varchar(10),

zip pin_code_type)

insert into temp2(c_address) values('delhi')

select * from temp2

id          c_address  zip

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

1           delhi      326595

Conclusion: Here we can see, we created only one Default Value and bind it to more than one table. This is an example of Re-Usability.

Code makes the coder dance in the Rain; Let the life get easier, let’s welcome the coder to your life

Post Reference: Vikram Aristocratic Elfin Share