22,302
社区成员




ALTER VIEW [dbo].[vwClient_CompanyPerson]
WITH SCHEMABINDING
AS
SELECT
A.CompanyID*1000+B.PersonID AS CompanyPersonID, A.CompanyID, B.PersonID
FROM
dbo.tblCompany AS A INNER JOIN
dbo.tblPerson AS B ON B.CompanyID=A.CompanyID
--drop table t1,t2
--1.建表
create table t1(id int)
create table t2(id int)
go
--2.建索引试图
create VIEW [dbo].[vwClient_CompanyPerson]
WITH SCHEMABINDING
AS
SELECT
A.ID*1000+B.ID AS CompanyPersonID, A.ID, B.ID as idd
FROM
dbo.t1 AS A INNER JOIN
dbo.t2 AS B ON B.id=A.ID
go
--drop index idx_t123 on [vwClient_CompanyPerson]
--3.给试图建个索引,没有报错
create unique clustered index idx_t123 on [vwClient_CompanyPerson]
(CompanyPersonID)
/*
命令已成功完成。
*/
[/quote]
创建主键没有问题,关键是创建全文索引时除问题,全文索引要求主键不能为NULL约束。[/quote]
哦,我看了一下,这个问题好像,解决不了,因为这个索引试图,只是要unique索引,而unique是可能为null值的。
而全文索引要主键索引,这个是不能有null的。--drop table t1,t2
--1.建表
create table t1(id int)
create table t2(id int)
go
--2.建索引试图
create VIEW [dbo].[vwClient_CompanyPerson]
WITH SCHEMABINDING
AS
SELECT
A.ID*1000+B.ID AS CompanyPersonID, A.ID, B.ID as idd
FROM
dbo.t1 AS A INNER JOIN
dbo.t2 AS B ON B.id=A.ID
go
--drop index idx_t123 on [vwClient_CompanyPerson]
--3.给试图建个索引,没有报错
create unique clustered index idx_t123 on [vwClient_CompanyPerson]
(CompanyPersonID)
/*
命令已成功完成。
*/
[/quote]
创建主键没有问题,关键是创建全文索引时除问题,全文索引要求主键不能为NULL约束。CREATE VIEW [dbo].vwClient_CompanyPerson
WITH SCHEMABINDING
AS
SELECT
ISNULL(A.CompanyID*1000,0)+ISNULL(C.PersonID,0) AS CompanyPersonID, A.UserOfficeID, A.CompanyID, B.CompanyName, C.[Name]
FROM
dbo.tblClient_Company AS A INNER JOIN
dbo.tblContact_Company AS B ON B.CompanyID = A.CompanyID INNER JOIN
dbo.tblContact_Person AS C ON C.CompanyID=A.CompanyID OR C.PersonID=0 INNER JOIN
dbo.tblClient_Person AS D ON D.PersonID=C.PersonID
GO
CREATE UNIQUE CLUSTERED INDEX PK_vwClient_CompanyPerson ON vwClient_CompanyPerson (CompanyPersonID)
GO
CREATE FULLTEXT INDEX ON vwClient_CompanyPerson(
CompanyName LANGUAGE [Simplified Chinese],
[Name] LANGUAGE [Simplified Chinese]
)KEY INDEX PK_vwClient_CompanyPerson ON [FD_Catalog] WITH CHANGE_TRACKING AUTO
GO
--drop table t1,t2
--1.建表
create table t1(id int)
create table t2(id int)
go
--2.建索引试图
create VIEW [dbo].[vwClient_CompanyPerson]
WITH SCHEMABINDING
AS
SELECT
A.ID*1000+B.ID AS CompanyPersonID, A.ID, B.ID as idd
FROM
dbo.t1 AS A INNER JOIN
dbo.t2 AS B ON B.id=A.ID
go
--drop index idx_t123 on [vwClient_CompanyPerson]
--3.给试图建个索引,没有报错
create unique clustered index idx_t123 on [vwClient_CompanyPerson]
(CompanyPersonID)
/*
命令已成功完成。
*/
2
3
4
5
6
7
8
ALTER VIEW [dbo].[vwClient_CompanyPerson]
WITH SCHEMABINDING
AS
SELECT
A.CompanyID*1000+B.PersonID AS CompanyPersonID, A.CompanyID, B.PersonID
FROM
dbo.tblCompany AS A INNER JOIN
dbo.tblPerson AS B ON B.CompanyID=A.CompanyID
select * from vwClient_CompanyPerson where CompanyPersonID is not null