Multicolumn Unique Constraint? [Archive]

jal

01-26-2009, 02:50 PM

That's a nice article, but I didn't understand the section on indexes. It says this command:

CREATE INDEX NewIndex ON NewTable(Field1) WITH PRIMARY

"creates a primary key index. A primary key index ensures that each row of the table has a unique value in the index field. No nulls are allowed in the index field." Is he saying that this index serves to index the primary key column? If so, I don't get it, because I thought PK columns are indexed by default.

Next he refers to this command:

CREATE UNIQUE INDEX NewIndex ON NewTable(Field1)

What is a unique index? I thought an index just means that the engine will track the order of the rows in that one column for rapid retrieval. Yet the writer doesn't speak of rows, nor does he speak of one column. Instead he speaks of comparing two columns? He says the command above "Creates a unique index on the designated field. In this example, no two columns could have the same value, but null values would be allowed."

Furthermore, what's the difference between an index and a unique index?

His next sample is this:

CREATE INDEX NewIndex ON NewTable (Field1) WITH DISALLOW NULL

He says, "Creates an index that is not unique, but does not allow null columns." Again he is referring to multiple columns - and not rows?

Finally:

CREATE INDEX NewIndex ON NewTable (Field1) WITH IGNORE NULL

Creates a non-unique index that allows null records in the index column.

If anyone could clarify this stuff, I'd like to hear it.