Make foreign key as auto increment in SQL Server 2005

Question

I have these table structure in MySQL, and according to my requirement its working fine.

CREATE TABLE `ebasic` ( <br>
`eid` int(11) NOT NULL AUTO_INCREMENT, <br>
`d_id` int(11) NOT NULL, <br>
`pos` varchar(50) DEFAULT NULL, <br>
`hdd` varchar(5) DEFAULT NULL, <br>
`hmm` varchar(10) DEFAULT NULL, <br>
`hyy` varchar(5) DEFAULT NULL, <br>
`fname` varchar(25) DEFAULT NULL, <br>
`lname` varchar(25) DEFAULT NULL, <br>
`bdd` varchar(5) DEFAULT NULL, <br>
`bmm` varchar(10) DEFAULT NULL, <br>
`byy` varchar(5) DEFAULT NULL, <br>
`gender` varchar(10) DEFAULT NULL, <br>
`addr` varchar(100) DEFAULT NULL, <br>
 `city` varchar(25) DEFAULT NULL, <br>
 `state` varchar(25) DEFAULT NULL, <br>
 `pin` varchar(10) DEFAULT NULL, <br>
 `country` varchar(25) DEFAULT NULL, <br>
 `tel` varchar(25) DEFAULT NULL, <br>
 `mobile` varchar(25) DEFAULT NULL, <br>
 `email` varchar(30) DEFAULT NULL, <br>
 `qual` varchar(30) DEFAULT NULL, <br>
 PRIMARY KEY (`eid`) );<br>



CREATE TABLE `eaccount` ( <br>
`eid` int(11) NOT NULL AUTO_INCREMENT, <br>
`d_id` int(11) NOT NULL, <br>
`accno` varchar(20) DEFAULT NULL, <br>
`pan` varchar(15) DEFAULT NULL, <br>
`passport` varchar(15) DEFAULT NULL, <br>
`visa` varchar(15) DEFAULT NULL, <br>
`type` varchar(20) DEFAULT NULL, <br>
KEY `eid` (`eid`), <br>
FOREIGN KEY (`eid`) REFERENCES `ebasic` (`eid`) ON DELETE CASCADE ); <br>

So, the column eid is primary key in ebasic and foreign key in eaccount, so that if row is deleted from ebasic, it gets deleted from eaccount too, because of ON DELETE CASCADE.

But, both of them are auto increment columns, too, so when a row is inserted into ebasic successfully, the corresponding row with eid same as ebasic is inserted into eaccount with auto increment number same.

Now, I am trying to migrate my database to SQL Server, what I have there for auto increment is IDENTITY(seed, increment). But, I can't make foreign key as auto_increment here in SQL Server but in MySQL its possible. Please help !!

You cannot do that in MS SQL, you can try this - first insert a row in ebasic then get inserted row eid and insert that into eaccount table. — rs.
for that i have to refract the whole project code and it will much time to do that.... — user2150770
This whole design seems like a really horribly bad idea.... what if the auto increments in ebasic and eaccount suddenly get out of sync?? This is not a very reliable way to go!! You need to insert into ebasic and then fetch whatever eid you got, and insert that into eaccount - anything else is a kludge.... — marc_s
how do you keep both auto-increments in sync? just 1 insert into 1 of the tables (rather than both) ruins your approach — devio

rs. rs. · Accepted Answer · 2013-03-09T05:44:35

In SQL Server you cannot create foreign keys with auto increment option and set cascading delete option on, if you are inserting a row in one table you can get inserted row id and use that to store in other table that references this key, for example:

declare @idtable table (id int);


insert into ebasic (d_id, pos ......)
OUTPUT Inserted.eid INTO @idTable
values (1,2,...);

declare @eid int
select eid = id from @idTable;

insert into eaccount (eid, d_id...)
values (@eid, 1,...);

Make foreign key as auto increment in SQL Server 2005

1 Answers