SQL create table reference
December 20, 2010
Here’s a quick reference for scripting table creation in SQL server by hand, where the text is readable and pretty rather than an autogenerated mess.
| CREATE TABLE sometable | |
| ( | |
| [id] [uniqueidentifier] NOT NULL | |
| CONSTRAINT [DF_sometable_id] DEFAULT (newid()), /* This isn't required for DEFAULT */ | |
| [userid] [uniqueidentifier] NOT NULL, | |
| [accountid] [uniqueidentifier] NOT NULL, | |
| [activated] [bit] NOT NULL, | |
| [activatedon] [datetime] NULL, | |
| [email] [nvarchar](128) NOT NULL, | |
| [key] [varchar](32) NOT NULL, | |
| PRIMARY KEY NONCLUSTERED (id), /* DONT USE CLUSTERED FOR uniqueidentifier */ | |
| CONSTRAINT FK_sometable_userid FOREIGN KEY(userid) REFERENCES users (id), | |
| CONSTRAINT FK_sometable_accountid FOREIGN KEY(accountid) REFERENCES accounts (id), | |
| ) | |
| CREATE NONCLUSTERED INDEX [idx_anothertable] ON [dbo].[anothertable] | |
| ( | |
| [sometypeid] ASC, | |
| [fileid] ASC, | |
| [layoutid] ASC | |
| ) |
It’s usually easier to bulk add the foreign keys after, that way the order of the CREATE TABLES doesn’t matter:
ALTER TABLE [dbo].[sometable] ADD CONSTRAINT [FK_sometable_users_id] FOREIGN KEY([userid]) REFERENCES [dbo].[users] ([id])
I'm Chris Small, a software engineer living and working in London. This is my tech blog. Find out more via Github, Stackoverflow, Resume