SQL: Create multiple dependent tables in one script

Question

I'm trying to create 2 tables in one script. The first is an image table with a primary key and the second is an article table with a foreign key linked to the image table id. Code:

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'Image')
        CREATE TABLE [Image]
        (
            [ImageId]                       INT IDENTITY(1,1)       NOT NULL,
            [FileName]                      NVARCHAR(20)            NOT NULL,
            [ImageData]                     VARBINARY(MAX)          NOT NULL,
            CONSTRAINT PK_ImageId PRIMARY KEY CLUSTERED (ImageId ASC)
            ON [PRIMARY]
        )

    IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'Article')
        CREATE TABLE Article
        (
            ArticleId                       INT IDENTITY(1,1)       NOT NULL,
            CreatedAt                       DATE                    NOT NULL
            CONSTRAINT D_ArticleDate DEFAULT GETDATE(),
            Title                           NVARCHAR(20)            NOT NULL,
            Body                            NVARCHAR(MAX)           NOT NULL,
            MainImageId                     INT                     NULL,
            CONSTRAINT PK_ArticleId PRIMARY KEY CLUSTERED (ArticleId ASC)
            ON [PRIMARY],
            CONSTRAINT FK_MainImageId FOREIGN KEY (MainImageId) REFERENCES [Image] (PK_ImageId)
        )

If I run the Image query it runs fine. If I then run the Article query it runs fine. If I run both queries in one go, I get the following error:

Foreign key 'FK_MainImageId' references invalid column 'PK_ImageId' in referenced table 'Image'.

Is there a way to do this in one script?

Vishal · Accepted Answer · 2011-04-08 17:12:29Z

2

You should be referencing ImageId and not the constraint name PK_ImageId like this -

CONSTRAINT FK_MainImageId FOREIGN KEY (MainImageId) REFERENCES [Image] (ImageId)

and try GO between the two batch statements..to execute them at once.

answered Apr 8, 2011 at 17:12

Vishal

12.4k20 gold badges85 silver badges129 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

VARAK Over a year ago

That's the problem. I was referencing the wrong thing. Thanks!

Peter · Accepted Answer · 2011-04-08 17:11:05Z

0

Try adding a "GO" after the first query. I think this should work.

answered Apr 8, 2011 at 17:11

Peter

3,9661 gold badge25 silver badges44 bronze badges

Comments

Cade Roux · Accepted Answer · 2011-04-08 17:11:06Z

0

In one script, just put a GO in between the two operations. This divides the script into two batches. GO is a batch separator supported by SSMS and some other tools, like SQLCMD.

answered Apr 8, 2011 at 17:11

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

Collectives™ on Stack Overflow

SQL: Create multiple dependent tables in one script

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related