tags:

views:

1161

answers:

4

I am working on a pomotion database and below is what my CREATE TABLE steatment 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'd like to get rid of ALTER TABLE statements and make them part of CREATE TABLE statements, I know how to do the most but not sure how to get CHECK CONSTRAINT during create table, does anyone have experience with this? or know how to?

+4  A: 

You can just add each foreign key constraint right in the 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]
Jose Basilio
You can add the constraint directly to each column declaration as per Mitch Wheats answer.
ck
@ck - It works both ways. Great!
Jose Basilio
+1  A: 

You seem to have missed a 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]
Mitch Wheat
A: 

The ALTER TABLE ... CHECK CONSTRAINT command simply enables (or disables with NOCHECK) the constraint. Constraints are enabled by default when you add them, so this extra statement is redundant and not needed if you are adding the constraints in the CREATE statement.

http://msdn.microsoft.com/en-us/library/ms190273(SQL.90).aspx

NYSystemsAnalyst
A: 

Thanks guys, I had to change the create table code so it looks different from the original source, so there is somthing missing.

You need to be more specific. Are you getting an error message?
Jose Basilio