在SQL中安排表 [英] Arrange the table in sql

问题描述

我在Sql中创建了一个表，其中添加了50行，当我删除41到50行时，它被删除了.但是当我为下一行输入值时，它显示的是第51行而不是第41行. ???

问候
Balamurugan

Hi,
I have created a table in Sql.In that added 50 rows and when i delete 41 to 50 rows it gets deleted.But when i''m entering values for next row means it shows 51th row not 41th row.How to solve this one???

Regards
Balamurugan

推荐答案

巴拉，

您需要使用以下
将您的身份种子设置为40 控制台命令.

Hi Bala,

You need to set the identity seed of your to 40 in using the following
console command.

DBCC CHECKIDENT (yourtable, reseed, 40)

之后，开始将行插入表中.新行以41作为标识列开头.
让我用一个例子来解释.使用此脚本创建表

After that start inserting the rows into your table. New rows starts with 41 as identity column.
Let me explain with an example. Create a table by using this script

CREATE TABLE [dbo].[ReseedTable](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Value] [char](1) NOT NULL
) ON [PRIMARY]

现在插入数据.

Now insert the data..

Insert Into ReseedTable VALUES('a'),('b'),('c'),('d'),('e'),
 ('f'),('g'),('h'),('i'),('j')

现在，您的表中有数据...

Now you have the data in your table like ..

Id     Value
------------
1	a
2	b
3	c
4	d
5	e
6	f
7	g
8	h
9	i
10	j

删除最后5行

Delete last 5 rows

DELETE FROM ReseedTable WHERE Id > 5

现在表格中的数据如

Now the table has data like

Id     Value
-----------
1	a
2	b
3	c
4	d
5	e

现在，使用以下控制台命令在您的表上将标识种子值设置为5

Now set the identity seed value to 5 on you table using following console command

DBCC CHECKIDENT ('Reseedtable', reseed, 5)

现在开始插入类似
的记录

Now start insert the records like

 Insert Into ReseedTable VALUES ('f'),('g'),('h'),('i'),('j')

 -- Now see all the data in your table
SELECT * FROM ReseedTable

您将得到结果为

You will get the result as

Id     Value
------------
1   a
2   b
3   c
4   d
5   e
6   f
7   g
8   h
9   i
10  j

请参阅:"ID"列中的字符数不能大于10.这样，我们可以覆盖
表的标识属性.

要了解更多信息，请单击此处

注意:-表不应该具有参照完整性..

谢谢

See.. it doesn''t have greater than 10 in the Id column. Like this we can override the
identity property of a table.

To explore more Click here

Note:- Table should not have referntial integrity..

Thank you

整数主键即使在删除后也能保持插入的历史记录，这样可以确保在引用其他表时数据不会丢失，并且保持唯一性.

如果您想要一个像您描述的那样的递增计数器，请在表格中添加一列并更新自己的内容.

Integer primary keys maintain the history of inserts even when deleted, this is so data is not lost when referencing other tables and keeps things unique.

If you want an incrementing counter like the one you described add a column to your table and update the contents your self.

这篇关于在SQL中安排表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！