Can someone tell me what's wrong with this statement, when trying to create a mysql table.

CREATE TABLE dataprofile (
profilenumber char(25) DEFAULT 'profilenumber' NOT NULL,
AdHeadline char(75) DEFAULT 'Headline' NOT NULL,
EmailAddress char(90) DEFAULT 'EmailAddress' NOT NULL,
TelephoneAreaCode char(3) DEFAULT 'TelephoneAreaCode' NOT NULL,
City char(50) DEFAULT 'City' NOT NULL,
StateProvince char(50) DEFAULT 'StateProvince' NOT NULL,
Zip char(6) DEFAULT 'Zip' NOT NULL,
Country char(50) DEFAULT 'Country' NOT NULL,
RelationshipPreference char(50) DEFAULT 'RelationshipPreference' NOT NULL,
SexualPreference char(50) DEFAULT 'SexualPreference' NOT NULL,
Username char(12) DEFAULT 'Username' NOT NULL,
Password char(12) DEFAULT 'Password' NOT NULL,
VerifyPassword char(12) DEFAULT 'VerifyPassword' NOT NULL,
SmokingPreference char(50) DEFAULT 'SmokingPreference' NOT NULL,
DrinkingPreference char(50) DEFAULT 'DrinkingPreference' NOT NULL,
MaritialStatus char(50) DEFAULT 'MaritialStatus' NOT NULL,
HaveChildren char(50) DEFAULT 'HaveChildren' NOT NULL,
BodyBuild char(50) DEFAULT 'BodyBuild' NOT NULL,
Height char(50) DEFAULT 'Height' NOT NULL,
Religion char(50) DEFAULT 'Religion' NOT NULL,
Race char(50) DEFAULT 'Race' NOT NULL,
AstrologicalSign char(50) DEFAULT 'AstrologicalSign' NOT NULL,
Age char(3) DEFAULT 'Age' NOT NULL,
Occupation char(50) DEFAULT 'Occupation' NOT NULL,
MiscComments Blob(1900) DEFAULT 'MiscComments' NOT NULL,
PRIMARY KEY(Username,EmailAddress)
);


The error I get back is having to do with the blob statment near the bottom, have I not written it correctly?

Im sorry I wrote to the links sql forum, but some of you folks are the brightest folks I know with regards to programming, cgi and sql.

I appologize if it has offended anyone with my topic.
Visionary

Just change line #26 to:

MiscComments blob NOT NULL,

BLOB should not have length and DEFAULT value.
Also, I think a table can have only one PRIMARY KEY(line #27), but I am not sure.

A table can only have one primary key, and if you have an AutoIncrement field, that is your primary key.

Thanks, I managed to figure it out about an hour after posting via trial and error.

As to the primary key, I dunno, seems to work with it set the way it is, so guess it's best to leave it alone for now.

Thanks for the info guys.
Much appreciated.


------------------
Regards,
Visionary

Actually, what you've done is create a primary key using two columns:

PRIMARY KEY(Username,EmailAddress)

What this means, is you have created a key that is composed of BOTH the Username AND the EmailAddress. That means that a person can enter the same Username and a different EmailAddress and probably get away with it.

It ALSO means you have an index on Username as well as Username,EmailAddress

You've only created ONE primary key, it's just a compound key.

Hellö!


HEY, Mr. Pataki! Is this possible? May be it is.

I wonder what would be the disadvantage?

Where will be the auto_increment oriented?` It needs to be towards only one. Ofcourse one may not have one at all and it only needs to be NOT NULL, so that explains.

But may be I am not very clear in understanding what you explained.

I'm not sure what you are asking --

But an INDEX or KEY is an index to the file/table it is pointing to.

For instance, if you want rapid access to Username, you'd make that an Index and when you'd access that table on the field "Username" the index would be used, rather than a sequential table search.

You can create compound keys, such that if you wanted things sorted by Date, then title, and that was how you usually accessed your data, it would be _much_ faster to create an Index to do that... when that index was searched, items in it would already be sorted by date, then within each date by title.

This means that to find a specific date, you'd have very, very fast access.

To list items by date,title you'd have very fast access.

To list things by title, unless you had a title index as well, you'd still be traversing the table, essentially building an index on the fly.

The downside of indexes (if you look in the Links table) is that the indexes can take up MORE room than the files they index. You sacrifice disk space for speed of access.

In MySQL ... if you do a lot of searches by date, date/username or date/username/title you'd get much better performance to create an index date,username,title

That way, you can search by date, by date/username or date/username/title -- you in effect get 3 indexes for the price of one.

But, the advantage would only be if you used those indexes.

If you needed to sort/index by 3 methods -- date, date/username/title and username you'd need to create TWO indexes... one would be date/username/title and one would be username --

Make sense?

Hello Mr. Pataki!

Thanks for your reply. It may help me in something I had in mind.

You seem to be swimming in the pond (Ocean ) of MySQL a lot man!