Asking question CREATE TABLE in T-SQL
I am working on a pomotion database and below is what my CREATE TABLE looks like:
CREATE TABLE [dbo].[sponsors]
(
[InstId] [bigint] NOT NULL,
[EncryptedData] [varbinary](44) NOT NULL,
[HashedData] [varbinary](22) NOT NULL,
[JobId] [bigint] NOT NULL,
CONSTRAINT [PK_sponsors] PRIMARY KEY CLUSTERED
(
[InstId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[sponsors] WITH CHECK ADD CONSTRAINT [FK_sponsors_jobs] FOREIGN KEY([JobId])
REFERENCES [dbo].[jobs] ([Id])
GO
ALTER TABLE [dbo].[sponsors] CHECK CONSTRAINT [FK_sponsors_jobs]
GO
ALTER TABLE [dbo].[sponsors] WITH CHECK ADD CONSTRAINT [FK_sponsors_titles] FOREIGN KEY([TId])
REFERENCES [dbo].[titles] ([TId])
GO
ALTER TABLE [dbo].[sponsors] CHECK CONSTRAINT [FK_sponsors_titles]
GO
And I would like to get rid of the ALTER TABLE statements and make them part of the CREATE TABLE statement, I know how to do the most, but not sure how to get the CONSTRUCTION CHECKED while creating the table, does anyone have any experience? or do you know how?
You can simply add each foreign key constraint in your CREATE TABLE declaration:
CREATE TABLE [dbo].[sponsors]
(
[InstId] [bigint] NOT NULL,
[EncryptedData] [varbinary](44) NOT NULL,
[HashedData] [varbinary](22) NOT NULL,
[JobId] [bigint] NOT NULL,
[TId] [int] NOT NULL,
CONSTRAINT [PK_sponsors] PRIMARY KEY CLUSTERED
(
[InstId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [FK_sponsors_jobs] FOREIGN KEY([JobId])
REFERENCES [dbo].[jobs] ([Id]),
CONSTRAINT [FK_sponsors_titles] FOREIGN KEY([TId])
REFERENCES [dbo].[titles] ([TId])
) ON [PRIMARY]
a source to share
You seem to be missing the column (TId)
CREATE TABLE [dbo].[sponsors]
(
[InstId] [bigint] NOT NULL
CONSTRAINT [PK_sponsors] PRIMARY KEY CLUSTERED,
[EncryptedData] varbinary NOT NULL,
[HashedData] varbinary NOT NULL,
[JobId] [bigint] NOT NULL
CONSTRAINT [FK_sponsors_jobs] FOREIGN KEY REFERENCES [dbo].[jobs] ([Id]),
[TId] int NOT NULL
CONSTRAINT [FK_sponsors_titles] FOREIGN KEY REFERENCES [dbo].[titles] ([TId])
) ON [PRIMARY]
a source to share
The ALTER TABLE ... CHECK CONSTRAINT command simply enables (or disables with NOCHECK) the constraint. Constraints are included by default when they are added, so this additional statement is redundant and unnecessary if you add constraints to the CREATE statement.
http://msdn.microsoft.com/en-us/library/ms190273(SQL.90).aspx
a source to share