On Mon, 15 Mar 2004, Boyd E. Hemphill wrote: > Using a tool to generate a data model I go the following statement: > > Create table StateN ( > StateId Int NOT NULL AUTO_INCREMENT, > StateNm Char(50) NOT NULL DEFAULT '', > StateCd Char(7) NOT NULL DEFAULT '', > SortInt Int NOT NULL DEFAULT 0, > UNIQUE (StateId), > UNIQUE (StateCd), > Primary Key (StateId), > UNIQUE Index ak_State (StateCd) > ) TYPE = InnoDB > ROW_FORMAT = Default; > > The last four statements seem to be redundant in that the > UNIQUE(StateId) is a proper subset of the Primary Key(StateId) statement > and is StateCd to its index.
All four statements aren't redundant, only two of them are. > My question is, what is happening in terms of the objects I am creating? > That is: Am I creating for indexes or two? Are they the pk and ak that > I want (last two statements) or is the server only creating the first > two? All four indexes are created in this case. You have one primary key on the StateId column, one unique index on the StateId column called StateId (<-- redundant) one unique index on the StateCd column called StateCd and one unique index on the StateCd column called ak_State. The primary key should be removed if you want StateId to contain NULL values, but most likely you will want to remove the unique index on this column (since a primary key in itself is unique). Then you will remove one of the two indexes on the StateCd column. for example: DROP INDEX StateId ON StateN; DROP INDEX StateCd ON StateN; That would leave you with the Primary Key on StateId and a unique index called ak_State on the StateCd column. cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
