表腐败？ [英] Table Corruption?
问题描述
我的数据库中有一个名为Users的表：
CREATE TABLE [Users]（
[UserID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Users_UserID ]
DEFAULT（newid（）），
[用户名] [nvarchar]（20）COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL，
[密码] [nvarchar]（40）COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL，
[EmailAddress] [nvarchar]（100）COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL，
[ValidFrom] [datetime] NOT NULL，
[ValidTo] [datetime] NULL，
[passwordSalt] [bigint] NOT NULL，
[FullName] [nvarchar]（200）COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL，
[CreatorUserID] [uniqueidentifier] NOT NULL，
[DeletorUserID] [uniqueidentifier] NULL，
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
（
[UserID]，
[]
）ON [PRIMARY]，
CONSTRAINT [UX_Users_UserNa我是独一无二的
（
[用户名]
）ON [PRIMARY]，
CONSTRAINT [FK_Users_UsersCreator]外键
（
[CreatorUserID]
）参考[用户]（
[UserID]
），
CONSTRAINT [FK_Users_UsersDeletor] FOREIGN KEY
（
[DeletorUserID]
）参考[用户]（
[用户ID]
），
CONSTRAINT [CK_Users_Finished]检查（[ValidTo]为空并且
[DeletorUserID]为null或（（not（[ValidTo]为null）））和
（（不是（[DeletorUserID]为空）））），
CONSTRAINT [CK_Users_ValidDates]检查（[ValidFrom]< = [ValidTo]）
）ON [PRIMARY]
GO
如果你正在查看上面的主键约束，你可能已经看到了问题（这个脚本是由查询分析器生成的）。
如果我试着让查询分析器只编写PK脚本，我得到了
跟随e rror消息：
[SQL-DMO]名称''''不是有效的对象标识符，或者不是
这个属性的有效格式或者方法。
我用谷歌搜索了这个短语，找不到它。如果我在数据库中删除所有
约束，并使用以下脚本将约束重新应用于用户
：
alter table用户添加约束
DF_Users_UserID DEFAULT NEWID（）FOR UserID
go
alter table用户添加约束
PK_Users PRIMARY KEY
（
用户ID
）[PRIMARY]
go
alter table用户添加约束
UX_Users_UserName UNIQUE
（
用户名
）
go
alter table用户添加约束
CK_Users_ValidDates CHECK
（
ValidFrom< = ValidTo
）
go
alter table用户添加约束
CK_Users_Finished CHECK
（
（
ValidTo为空且
DeletorUserID为空
）
或
（
不是ValidTo为空且
不是DeletorUserID为空
）
）
go
alter table用户添加约束
FK_Users_UsersCreator FOREIGN KEY
（
CreatorUserID
）
参考用户
（
用户ID
）
go
alter table用户添加约束
FK_Users_UsersDeletor FOREIGN KEY
（
DeletorUserID
）
参考用户
（
用户ID
）
go
我*仍然*有这个奇怪的PK。我已经检查了
INFORMATION_SCHEMA视图并直接通过系统表进行检查，所以
因为他们担心只有一个列（UserID）代理
作为PK的一部分。
那么，有什么想法吗？它似乎阻止我们设置
复制。值得庆幸的是，它也发生在DB的恢复备份中，
所以我将能够重现/尝试修复我心中的内容。
谢谢，
Damien
Damien（Da ******* ************@hotmail.com）写道：我*仍然*有这个奇怪的PK。我已经检查了
INFORMATION_SCHEMA视图并直接通过系统表进行检查，所以
就他们而言，只有一个列（UserID）作为PK。
您确定检查了哪个系统表？ sysindexkeys应该是那个。
如果你这样做
SELECT * FROM syscolumns WHERE id = object_id（''Users''）ORDER BY colid
colid是从1开始连续吗？ （我的猜测是有一个
历史中涉及掉落的专栏。）
那么，有什么想法吗？它似乎阻止我们设置
复制。值得庆幸的是，它也发生在DB的恢复备份中，所以我将能够重现/尝试修复我的内容。
DBCC CHECKCATALOG可能值得一试。
简单的修复可能是重命名表，创建一个新的并移动数据
over。
-
Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se
SQL Server SP3联机丛书
http://www.microsoft.com/sql/techinf...2000/books.asp
< blockquote>对不起，通过新谷歌发帖，似乎无法找到一个选项，以便
包括之前的回复，所以这看起来有点
不连续......
我确实检查了sysindexkeys（和相关的INFORMATION_SCHEMA
视图 - 不记得它是什么。我有点顽皮，我总是把头直接用于表b）b
检查syscolumns colid确实是严格增加的并且
连续。数据库设计只有几个月的历史，我是它唯一的
开发者，虽然它已经发展了（查看历史
这里......），我从来没有删掉任何专栏。
我开始想知道的一件事......
数据库中有另一个表格以下：
CREATE TABLE [CreditorServices]。[用户]（
[UserID] [uniqueidentifier] NOT NULL，
[AbstractCreditorID] [uniqueidentifier] NOT NULL，
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
（
[UserID]，
[AbstractCreditorID]
）WITH FILLFACTOR = 90 ON [PRIMARY]，
CONSTRAINT [FK_Users_AbstractCreditors] FOREIGN KEY
（
[AbstractCreditorID]
）参考文献[AbstractCreditors]（
[AbstractCreditorID]
），
CONSTRAINT [FK_Users_Users]外键
（
[用户ID]
）参考[用户]（
[用户ID]
）
）ON [主要]
GO
SQL Server不应该与两个具有相同
名称的表混淆，是吗？他们确实有不同的所有者（原来是在dbo角色下的
）。
Doh！主键具有相同的名称，这似乎使得查询分析器感到困惑。希望这也是令人困惑的同时复制
复制
I have a table in my database called Users:
CREATE TABLE [Users] (
[UserID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Users_UserID]
DEFAULT (newid()),
[UserName] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[password] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[EmailAddress] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[ValidFrom] [datetime] NOT NULL ,
[ValidTo] [datetime] NULL ,
[passwordSalt] [bigint] NOT NULL ,
[FullName] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CreatorUserID] [uniqueidentifier] NOT NULL ,
[DeletorUserID] [uniqueidentifier] NULL ,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID],
[]
) ON [PRIMARY] ,
CONSTRAINT [UX_Users_UserName] UNIQUE NONCLUSTERED
(
[UserName]
) ON [PRIMARY] ,
CONSTRAINT [FK_Users_UsersCreator] FOREIGN KEY
(
[CreatorUserID]
) REFERENCES [Users] (
[UserID]
),
CONSTRAINT [FK_Users_UsersDeletor] FOREIGN KEY
(
[DeletorUserID]
) REFERENCES [Users] (
[UserID]
),
CONSTRAINT [CK_Users_Finished] CHECK ([ValidTo] is null and
[DeletorUserID] is null or ((not([ValidTo] is null))) and
((not([DeletorUserID] is null)))),
CONSTRAINT [CK_Users_ValidDates] CHECK ([ValidFrom] <= [ValidTo])
) ON [PRIMARY]
GO
If you''re looking at the Primary Key constraint above, you can probably
already see the problem (this script was produced by Query Analyzer).
If I try to get query analyzer to just script the PK, I get the
following error message:
[SQL-DMO]The name '''' is not a valid object identifier, or is not a
valid format for this property or method.
I''ve googled for this phrase, and cannot locate it. If I drop all of
the constraints in my database, and reapply the constraints to Users
using the following script:
alter table Users add constraint
DF_Users_UserID DEFAULT NEWID() FOR UserID
go
alter table Users add constraint
PK_Users PRIMARY KEY
(
UserID
) on [PRIMARY]
go
alter table Users add constraint
UX_Users_UserName UNIQUE
(
UserName
)
go
alter table Users add constraint
CK_Users_ValidDates CHECK
(
ValidFrom <= ValidTo
)
go
alter table Users add constraint
CK_Users_Finished CHECK
(
(
ValidTo is null and
DeletorUserID is null
)
or
(
not ValidTo is null and
not DeletorUserID is null
)
)
go
alter table Users add constraint
FK_Users_UsersCreator FOREIGN KEY
(
CreatorUserID
)
REFERENCES Users
(
UserID
)
go
alter table Users add constraint
FK_Users_UsersDeletor FOREIGN KEY
(
DeletorUserID
)
REFERENCES Users
(
UserID
)
go
I *still* have this wierd PK. I''ve checked through both the
INFORMATION_SCHEMA views and directly through the system tables, and so
far as they are concerned there is only a single column (UserID) acting
as part of the PK.
So, any ideas? It appears to be preventing us from setting up
replication. Thankfully, it also occurs in a restored backup of the DB,
so I''ll be able to reproduce/attempt fixes to my hearts content.
Thanks,
Damien
Damien (Da*******************@hotmail.com) writes:I *still* have this wierd PK. I''ve checked through both the
INFORMATION_SCHEMA views and directly through the system tables, and so
far as they are concerned there is only a single column (UserID) acting
as part of the PK.
Exactly which system table did you check? sysindexkeys should be the one.
If you do
SELECT * FROM syscolumns WHERE id = object_id(''Users'') ORDER BY colid
Are the colid sequential from 1 and up? (My guess is that there is a
history of a dropped column involved.)
So, any ideas? It appears to be preventing us from setting up
replication. Thankfully, it also occurs in a restored backup of the DB,
so I''ll be able to reproduce/attempt fixes to my hearts content.
DBCC CHECKCATALOG could be worth trying.
As simple fix may be to rename the table, create a new and move data
over.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Sorry, posting through new Google, can''t seem to find an option to
include the previous replies, so this will seem a little
discontinuous...
I did indeed check sysindexkeys (and the relevant INFORMATION_SCHEMA
view - can''t remember which it is. I''m kind of naughty, I always head
straight for the tables)
Checking syscolumns the colids are, indeed, strictly increasing and
continuous. The database design is only a few months old, I''m the only
developer of it, and although it has evolved (checking history
here...), I''ve never dropped any columns from it.
One thing I''ve started wondering... There is another table in the
database with the following:
CREATE TABLE [CreditorServices].[Users] (
[UserID] [uniqueidentifier] NOT NULL ,
[AbstractCreditorID] [uniqueidentifier] NOT NULL ,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID],
[AbstractCreditorID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_Users_AbstractCreditors] FOREIGN KEY
(
[AbstractCreditorID]
) REFERENCES [AbstractCreditors] (
[AbstractCreditorID]
),
CONSTRAINT [FK_Users_Users] FOREIGN KEY
(
[UserID]
) REFERENCES [Users] (
[UserID]
)
) ON [PRIMARY]
GO
SQL Server shouldn''t get confused with having two tables with the same
name, should it? They do have separate owners (the original being under
the dbo role).
Doh! The Primary Keys have the same name, and it''s this that seems to
be confusing Query Analyzer. Hopefully, that was what was confusing
replication as well
这篇关于表腐败？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
