SQL Server doesn't understand USE <>

Question

I have a problem with my SQL code. When I try to use database DIGITECH, SQL Server says the database does not exist.

This is my code

create database DIGITECH;
Go

use DIGITECH;

/*Erstellen der Tabellen*/
create table produkt(
    PID int identity(1,1) NOT NULL,
    Produktname nvarchar(50) NULL,
    Spezifikationen nvarchar(100) NULL,
    Beschreibung nvarchar(200) NULL,
    Preis int NOT NULL,
    FK_HeID int NULL,
    FK_KatID int NULL,
    FK_StID int NULL
);
go

create table hersteller(
    HeID int identity(1,1) NOT NULL,
    Hersteller nvarchar(50) NOT NULL
);
go

create table kategorie(
    KatID int identity(1,1) NOT NULL,
    KategorieName nvarchar(20) NULL
);
go

create table stat_us(
    StID int identity(1,1) NOT NULL,
    Status nvarchar(20) NOT NULL
);
go

create table verbindungstabelle_produkt_order(
    FK_OderID int NOT NULL,
    FK_PID int NOT NULL
);
go

create table or_der(
    OrderID int identity(1,1) NOT NULL,
    Status char NOT NULL,
    FK_Kunde int NULL
);

create table kunde(
    KID int identity NOT NULL,
    Name nvarchar(50) NULL,
    Vorname nvarchar(50) NULL,
    Email nvarchar(70) NULL,
    Geschlecht char NULL,
    FK_AdID int NULL
);
go

create table adresse(
    AdID int identity(1,1) NOT NULL,
    Strasse nvarchar(50) NULL,
    Hausnummer int NULL,
    FK_PLZ int NULL
);
go

create table plz(
    PLZ int identity(1,1) NOT NULL,
    Ort nvarchar(60) NOT NULL
);
go

/* Primärschlussel*/

alter table produkt
    add constraint PK_produkt
        primary key (PID);
go

alter table kategorie
    add constraint PK_kategorie
        primary key (KatID);
go

alter table stat_us
    add constraint PK_status
        primary key (StID);
go

alter table hersteller
    add constraint PK_hersteller
        primary key (HeID);
go

alter table or_der
    add constraint PK_order
        primary key (OrderID);
go

alter table kunde
    add constraint PK_kunde
        primary key (KID);
go

alter table adresse
    add constraint PK_adresse
        primary key (KID);
go

alter table plz
    add constraint PK_plz
        primary key (PLZ);
go

/* Fremdkeys hinzufügen */

alter table produkt
    add constraint FK_kategorie
        foreign key  (FK_KatID)
        references kategorie (KatID);
go

alter table produkt
    add constraint FK_status
        foreign key (FK_StID)
        references stat_us (StID);
go

alter table produkt
    add constraint FK_hersteller
        foreign key (FK_HeID)
        references hersteller (HeID);
go

alter table verbindungstabelle_produkt_order
    add constraint FK_PID
        foreign key (FK_PID)
        references produkt (PID);
go

alter table verbindungstabelle_produkt_order
    add constraint FK_OrderID
        foreign key (FK_OrderID)
        references ord_er (OrderID);
go

alter table ord_er
    add constraint FK_JID
        foreign key (FK_KID)
        references kunde (KID);
go

alter table kunde
    add constraint FK_AdID
        foreign key (FK_AdID)
        references adresse (AdID);
go

alter table adresse
    add constraint FK_PLZ
        foreign key (FK_PLZ)
        references plz (PLZ);
go

It is important that the create database AND the use are in one document.

This is the full Code

create database DIGITECH;
Go

use DIGITECH;

/*Erstellen der Tabellen*/
create table produkt(
    PID int identity(1,1) NOT NULL,
    Produktname nvarchar(50) NULL,
    Spezifikationen nvarchar(100) NULL,
    Beschreibung nvarchar(200) NULL,
    Preis int NOT NULL,
    FK_HeID int NULL,
    FK_KatID int NULL,
    FK_StID int NULL
);
go

create table hersteller(
    HeID int identity(1,1) NOT NULL,
    Hersteller nvarchar(50) NOT NULL
);
go

create table kategorie(
    KatID int identity(1,1) NOT NULL,
    KategorieName nvarchar(20) NULL
);
go

create table stat_us(
    StID int identity(1,1) NOT NULL,
    Status nvarchar(20) NOT NULL
);
go

create table verbindungstabelle_produkt_order(
    FK_OderID int NOT NULL,
    FK_PID int NOT NULL
);
go

create table or_der(
    OrderID int identity(1,1) NOT NULL,
    Status char NOT NULL,
    FK_Kunde int NULL
);

create table kunde(
    KID int identity NOT NULL,
    Name nvarchar(50) NULL,
    Vorname nvarchar(50) NULL,
    Email nvarchar(70) NULL,
    Geschlecht char NULL,
    FK_AdID int NULL
);
go

create table adresse(
    AdID int identity(1,1) NOT NULL,
    Strasse nvarchar(50) NULL,
    Hausnummer int NULL,
    FK_PLZ int NULL
);
go

create table plz(
    PLZ int identity(1,1) NOT NULL,
    Ort nvarchar(60) NOT NULL
);
go

/* Primärschlussel*/

alter table produkt
    add constraint PK_produkt
        primary key (PID);
go

alter table kategorie
    add constraint PK_kategorie
        primary key (KatID);
go

alter table stat_us
    add constraint PK_status
        primary key (StID);
go

alter table hersteller
    add constraint PK_hersteller
        primary key (HeID);
go

alter table or_der
    add constraint PK_order
        primary key (OrderID);
go

alter table kunde
    add constraint PK_kunde
        primary key (KID);
go

alter table adresse
    add constraint PK_adresse
        primary key (KID);
go

alter table plz
    add constraint PK_plz
        primary key (PLZ);
go

/* Fremdkeys hinzufügen */

alter table produkt
    add constraint FK_kategorie
        foreign key  (FK_KatID)
        references kategorie (KatID);
go

alter table produkt
    add constraint FK_status
        foreign key (FK_StID)
        references stat_us (StID);
go

alter table produkt
    add constraint FK_hersteller
        foreign key (FK_HeID)
        references hersteller (HeID);
go

alter table verbindungstabelle_produkt_order
    add constraint FK_PID
        foreign key (FK_PID)
        references produkt (PID);
go

alter table verbindungstabelle_produkt_order
    add constraint FK_OrderID
        foreign key (FK_OrderID)
        references ord_er (OrderID);
go

alter table ord_er
    add constraint FK_JID
        foreign key (FK_KID)
        references kunde (KID);
go

alter table kunde
    add constraint FK_AdID
        foreign key (FK_AdID)
        references adresse (AdID);
go

alter table adresse
    add constraint FK_PLZ
        foreign key (FK_PLZ)
        references plz (PLZ);
go

Screenshot 1

What does *in one document* mean? Try to put a "GO" in a separate line between your `create` and your `use`. SSMS will find, that you cannot use a non-existend database... — Shnugo, Jun 02 '16 at 20:05

Siyual · Accepted Answer · 2016-06-02T21:21:50.747

You need to put a GO after your CREATE DATABASE statement. The GO command informs SSMS to separate the script into different batches, split on each GO. Since your attempt to select the database was in the same batch as creating it, it threw an error as it didn't yet exist at the time of execution.

create database DIGITECH;
Go

use DIGITECH;

/*Erstellen der Tabellen*/
create table produkt(
    PID int identity(1,1) NOT NULL,
    Produktname nvarchar(50) NULL,
    Spezifikationen nvarchar(100) NULL,
    Beschreibung nvarchar(200) NULL,
    Preis int NOT NULL,
    FK_HeID int NULL,
    FK_KatID int NULL,
    FK_StID int NULL
);
go

EDIT (New Information in Question)

You have three problems with your script:

...
alter table adresse
    add constraint PK_adresse
        primary key (AdID);     -- This was changed from KID
go
...

You had the PRIMARY KEY set as KID, which isn't a column on the table.

...
alter table verbindungstabelle_produkt_order
    add constraint FK_OrderID
        foreign key (FK_OderID) -- Changed from FK_OrderID
        references or_der (OrderID); -- Changed from ord_er
go
...

You had the FOREIGN KEY declared on FK_OrderID, which isn't a column on the table.

You are also trying to reference a table named ord_er, which doesn't exist.

Changing those to what they should be leaves us with one more error:

alter table or_der 
    add constraint FK_JID
        foreign key (FK_Kunde) -- Changed from FK_KID
        references kunde (KID);
go

All of this was easy to figure out by just running your script and reading the error messages...

FULL CORRECTED SCRIPT

create database DIGITECH;
Go

use DIGITECH;

/*Erstellen der Tabellen*/
create table produkt(
    PID int identity(1,1) NOT NULL,
    Produktname nvarchar(50) NULL,
    Spezifikationen nvarchar(100) NULL,
    Beschreibung nvarchar(200) NULL,
    Preis int NOT NULL,
    FK_HeID int NULL,
    FK_KatID int NULL,
    FK_StID int NULL
);
go

create table hersteller(
    HeID int identity(1,1) NOT NULL,
    Hersteller nvarchar(50) NOT NULL
);
go

create table kategorie(
    KatID int identity(1,1) NOT NULL,
    KategorieName nvarchar(20) NULL
);
go

create table stat_us(
    StID int identity(1,1) NOT NULL,
    Status nvarchar(20) NOT NULL
);
go

create table verbindungstabelle_produkt_order(
    FK_OderID int NOT NULL,
    FK_PID int NOT NULL
);
go

create table or_der(
    OrderID int identity(1,1) NOT NULL,
    Status char NOT NULL,
    FK_Kunde int NULL
);

create table kunde(
    KID int identity NOT NULL,
    Name nvarchar(50) NULL,
    Vorname nvarchar(50) NULL,
    Email nvarchar(70) NULL,
    Geschlecht char NULL,
    FK_AdID int NULL
);
go

create table adresse(
    AdID int identity(1,1) NOT NULL,
    Strasse nvarchar(50) NULL,
    Hausnummer int NULL,
    FK_PLZ int NULL
);
go

create table plz(
    PLZ int identity(1,1) NOT NULL,
    Ort nvarchar(60) NOT NULL
);
go

/* Primärschlussel*/

alter table produkt
    add constraint PK_produkt
        primary key (PID);
go

alter table kategorie
    add constraint PK_kategorie
        primary key (KatID);
go

alter table stat_us
    add constraint PK_status
        primary key (StID);
go

alter table hersteller
    add constraint PK_hersteller
        primary key (HeID);
go

alter table or_der
    add constraint PK_order
        primary key (OrderID);
go

alter table kunde
    add constraint PK_kunde
        primary key (KID);
go

alter table adresse
    add constraint PK_adresse
        primary key (AdID);     -- This was changed from KID
go

alter table plz
    add constraint PK_plz
        primary key (PLZ);
go

/* Fremdkeys hinzufügen */

alter table produkt
    add constraint FK_kategorie
        foreign key  (FK_KatID)
        references kategorie (KatID);
go

alter table produkt
    add constraint FK_status
        foreign key (FK_StID)
        references stat_us (StID);
go

alter table produkt
    add constraint FK_hersteller
        foreign key (FK_HeID)
        references hersteller (HeID);
go

alter table verbindungstabelle_produkt_order
    add constraint FK_PID
        foreign key (FK_PID)
        references produkt (PID);
go

alter table verbindungstabelle_produkt_order
    add constraint FK_OrderID
        foreign key (FK_OderID) -- Changed from FK_OrderID
        references or_der (OrderID);
go

alter table or_der 
add constraint FK_JID
    foreign key (FK_Kunde) -- Changed from FK_KID
    references kunde (KID);
go

alter table kunde
    add constraint FK_AdID
        foreign key (FK_AdID)
        references adresse (AdID);
go

alter table adresse
    add constraint FK_PLZ
        foreign key (FK_PLZ)
        references plz (PLZ);
go

This works i have tried,you may need to add more info on what is that you are facing when you have tried.for the code pasted above ,this works — TheGameiswar, Jun 02 '16 at 20:15
@Yannikhere, please describe *does not work* and please describe where and how you call your statement (via SSMS-window, application, dynamic SQL...)? — Shnugo, Jun 02 '16 at 20:23
@Shnugo: Following error I get (German) In "sysdatabases" wurde kein Eintrag für die Datenbank "DIGITECH" gefunden. Ein Eintrag mit diesem Namen existiert nicht. Stellen Sie sicher, dass der Name richtig eingeben wurde. Translated with googletransalte: In " sysdatabases " no entry for the database " DIGITECH " was found . An entry with this name does not exist . Make sure that the name was entered correctly . — Yannik, Jun 02 '16 at 20:25
@Yannikhere did you put the "GO" alone in one separate line? — Shnugo, Jun 02 '16 at 20:26
@Yannikhere, What happens, if you mark the full `create` statement and press `F5` and then mark the rest and press `F5`? — Shnugo, Jun 02 '16 at 20:44
@Shnugo MSSQL says me there are missing columns, i think ist my ssms — Yannik, Jun 02 '16 at 20:46
@Yannikhere, so you did not show the full statement but just a part of it? Where is the error thrown (try a double click on the error message)? — Shnugo, Jun 02 '16 at 20:48
@Shnugo if I select the create database statment and press F5 ist still work. But if the step done, there are other Problems: MSSQL tell me the column are allready created but there are NO COLUMNS! (sorry for that) — Yannik, Jun 02 '16 at 20:54
@Yannikhere We can't solve the problems with your SQL query if you don't share all of it. We can only see what you have posted. — Siyual, Jun 02 '16 at 20:55

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

0

You got error because you cant execute all statements in same batch.You have to seperate them.Go Command is used to segregate batches..Here are some interesting rules

CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch.

A table cannot be altered and then the new columns referenced in the same batch.

So if you do any of the above operations,then they must be seperated by GO

References:
In SQL Server, when should you use GO and when should you use semi-colon ;?
https://technet.microsoft.com/en-us/library/aa172435%28SQL.80%29.aspx

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 02 '16 at 20:13

TheGameiswar

27,855
8
56
94

At this Moment I use following Code: create database DIGITECH; go use DIGITECH; – Yannik Jun 02 '16 at 20:19
Following error I get (German) In "sysdatabases" wurde kein Eintrag für die Datenbank "DIGITECH" gefunden. Ein Eintrag mit diesem Namen existiert nicht. Stellen Sie sicher, dass der Name richtig eingeben wurde. Translated with googletransalte: In " sysdatabases " no entry for the database " DIGITECH " was found . An entry with this name does not exist . Make sure that the name was entered correctly . – Yannik Jun 02 '16 at 20:23

score 0 · Answer 3 · answered Jun 02 '16 at 21:14

Please add your "full code" into your question (via edit option) and delete your answer, as this is no answer...

Your code is full of typos and errors:

ord_er or or_der?
No FK_OderID column (should be FK_OrderID?)
You create an FK to or_der on a column FK_KID, which does not exist...

???

Please clean your script and try again...

SQL Server doesn't understand USE <>

3 Answers3