CREATE TABLE - 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)

CREATE TABLE

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2009-07-12 : 23:36:00

I've got to run a basic "CREATE TABLE" statement. I'm most familiar with SQL Server, but this statement needs to also run on MySQL and Microsoft Access.

The statement below should create the table, but I don't know how to tell it that the UserID needs to be my primary key. Also, I'm not sure if other table formats use the same variable names. Is "int" standard accepted SQL, or do some tables require "Number" or "Integer"? (The reason I ask is because I opened an Access table in Design View, it lists Integer fields as "Number" and Boolean fields as "Yes/No".)

Here's what I want to crank out:

CREATE TABLE Users
UserID int, -- AutoNumber
UserName varchar(255),
Hash1 varchar(255), -- SHA1 Encrypted Password
Firstname varchar(255),
Lastname varchar(255),
Email varchar(255),
Hint varchar(255), -- Challenge prior to resetting PWD
Hash2 varchar(255), -- SHA1 Encrypted Hint response
Active bit, -- Set after person verifies with link sent
StartDate DateTime, -- Date account was created
LastUse DateTime, -- Date account was last used
Level int -- 1=Admin, 2=PowerUser, 3=Regular, 4=Guest
)

It is probably obvious from the format I'm using what this table is going to be for.

If anyone is thinking, "Hey brother, here's this example that shows everything you want all wrapped up in little package" .. I'm interested!

The thought of writing forms for them to create their accounts with, SHA1 their data, send email challenges, activate them on replies, keep track of their dates, etc. seems like a lot of debugging, to me.

Avoid Sears Home Improvement

yonabout
Posting Yak Master

112 Posts

Posted - 2009-07-14 : 11:41:38

Hi,

You can stick a key on a column like this:

CREATE TABLE [users] 
(
userid int NOT NULL ,
username varchar(255),
--etc. etc.
Constraint [PK_users] Primay Key Clustered (userid)  ON [Primary])

You can make keys from more than one column by adding the fields in to the bit after Primay Key Clustered like this:

CREATE TABLE [users] 
(
userid int NOT NULL ,
username varchar(255),
--etc. etc.

Constraint [PK_users] Primay Key Clustered (userid, Username)  ON [Primary])

I think this should be OK on MySQL as well, but I've just tried running it on access (2003) and it gives a syntax error.
I think you'll have to have separate create table scripts for access and SQL due to the differences in the language between the two.

Cheers,

Yonabout

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources