full text indexing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

full text indexing

Author

Topic

ronin2307
Posting Yak Master

126 Posts

Posted - 2007-04-13 : 16:45:43

hi i am trying to enable full text on one of my tables but i am having a hard time. it keeps returning this message:

"The selected table has no unique single column index on a column that does not allow NULLS"

i am new to full text, so it could be very well a dumb mistake.

the table has two colums that are set as PKs and one column that is set as identity column.

here is the create code as generate by the 2005 management studio express (i created the table using the gui designer):

USE [production]
GO
/****** Object:  Table [dbo].[sh_subcomp]    Script Date: 04/13/2007 15:39:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[sh_subcomp](
	[fid] [int] IDENTITY(1,1) NOT NULL,
	[fmodel] [varchar](50) NOT NULL,
	[fcomp] [varchar](50) NOT NULL,
	[fw] [decimal](18, 2) NOT NULL,
	[fl] [decimal](18, 2) NOT NULL,
	[fh] [decimal](18, 2) NOT NULL,
	[fwt] [decimal](18, 2) NOT NULL,
	[fpicture] [varchar](256) NOT NULL,
	[fcomments] [text] NULL,
 CONSTRAINT [PK_sh_subcomp] PRIMARY KEY CLUSTERED 
(
	[fmodel] ASC,
	[fcomp] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-13 : 16:49:45

Put a unique constraint on the identity column. Why do you have an identity column if it is not to be the surrogate key?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

ronin2307
Posting Yak Master

126 Posts

Posted - 2007-04-13 : 17:05:12

ok i will admit full confusion. SQL is not my strength obviously. I have no idea what you mean.
could you please dumb it down for me?
thanx

ronin2307
Posting Yak Master

126 Posts

Posted - 2007-04-13 : 17:28:11

i tried to add the word UNIQUE to the constraints window but it tells me it is not valid

Kristen
Test

22859 Posts

Posted - 2007-04-14 : 06:07:33

The Full Text thingie wants to have a single column that it can use to uniquely reference a record. i.e. the value in that column must be Unique within the whole table

Your Primary key is, Ipso facto, unique; however, it is a composite of two columns.

Your Identity column is [I hope!!] unique, but for SQL Server to know that you need to put a Unique Constraint on it.

As it will be used as a look-up device you should also put an Index on it.

Therefore you should create a Unique Index on the Identity column [i.e. as part of the definition of the table]

I think you may have been having a struggle with the term "surrogate key" ??

If so:

Lets say you have a Product Code for every part you sell.

So in your orders you could have a column called "ProductCode", and a table of Products also with a "ProductCode" column.

That works fine. You can JOIN the two together when you want details from both the Orders and the Products table.

But what happens when your Product Code changes? Never does? ...

... well it probably can do - typing mistakes when the product was first created, superseded products, new "versions" of a product, and so on.

Changing the Product Code means changing every single Order record too. (Actually surprisingly hard to do in SQL Server if you have any enforced Referential Integrity set up, but even if you haven't there are potential pitfalls etc)

So in that case you might decide that you will just use a number 1,2,3,... for SQL Server to use, and a column in your Products table called, say, "CurrentProductCode"

This idea of having some sort of IDENTITY number for the products, which you would probably never use in Real Life, and having all the connected records within the database use that Identity value, and then having the "Real" value just in one place - the new "CurrentProductCode" column in the Products table - is the concept of using a Surrogate key.

I can think of lots of very obvious cases where pretty much anyone would think this was a good thing - e.g. a reference to some external code which changes all the time.

But the advocates of Surrogate keys tend to use them for all such circumstances, even if the chances of a key changing is a once-in-a-blue-moon event.

For a lengthy discussion of the gory details

see:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6136

Kristen

ronin2307
Posting Yak Master

126 Posts

Posted - 2007-04-16 : 09:07:00

thank you so much for a very detailed explanation. it totally helped in all aspects except one :-(. the syntax...
like i said, i tried to do a bit reading and put the unique constraint on the FID column but apparently i am doing something wrong.
I am using the GUI designer so all it gives me is an empty field in which i can type in the constraint. Typing UNIQUE is apparently not enough..
could you please help me with the correct syntax?
thank you again

Kristen
Test

22859 Posts

Posted - 2007-04-16 : 16:06:34

I don't use the GUi tools, but in SQL I would do soemthing like:


CREATE UNIQUE INDEX MyIndexName ON MyTable
(
    MyColumn1,
    MyColumn2
)

Kristen

ronin2307
Posting Yak Master

126 Posts

Posted - 2007-04-16 : 16:10:26

thanx in the meantime i managed to figure out how to do it. thanx for everything though

ronin2307
Posting Yak Master

126 Posts

Posted - 2007-04-16 : 16:14:08

i do have one question regarding full-text index...
if i have the word 'TEST' in fcomp and in fcomments in number of entries and i do something like this:

select fmodel, fcomp, fcomments
from sh_subcomp
where freetext (fcomments, 'test') or (fcomp, 'test')

shouldn't ALL entries with that word and variations be returned as result?
right now i only get two entries back even though i have more with the word 'Test' in those columns....

hope it makes sense

ronin2307
Posting Yak Master

126 Posts

Posted - 2007-04-16 : 16:24:50

i guess to clarify, I used freetext and contains and it seems like they are returning exact matches, which is not what i am expecting

ronin2307
Posting Yak Master

126 Posts

Posted - 2007-04-16 : 16:34:30

never mind.....bad catalog. after a rebuild it worked fine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources