Creating all database 'things' in one query

Question

So i designed a 'CD shop Database' and tried to write one query for all of it's elements but for some reason i can't create and add tables to that Database in one query. Here is my query :

    CREATE DATABASE CDCI
ON PRIMARY(
    Name = cddukkan_data,
    FileName = 'C:\Program Files\Microsoft SQL Server\MSSQL14.VERITABANIM\MSSQL\DATA\cdci.mdf',
    Size = 16 MB,
    FileGrowth = 1 MB,
    MaxSize = 1 GB
)
LOG ON(
    Name = cddukkan_log,
    FileName = 'C:\Program Files\Microsoft SQL Server\MSSQL14.VERITABANIM\MSSQL\DATA\cdci.ldf',
    Size = 8 MB,
    FileGrowth = 5%,
    MaxSize = 512 MB
)
USE CDCI >An error occurs here
GO
CREATE TABLE Turler(
    TurID INT IDENTITY(1, 1) NOT NULL,
    TurAdi NVARCHAR(10) NOT NULL,
    PRIMARY KEY(TurID),
    UNIQUE(TurID)
)
CREATE TABLE Yonetmenler(
    YonetmenID INT IDENTITY(1, 1) NOT NULL,
    YonetmenAdi NVARCHAR(10) NOT NULL,
    YonetmenSoyadi NVARCHAR(10) NULL,
    DogumYili NCHAR(4),
    CONSTRAINT DogumYili CHECK(DogumYili LIKE '19__' OR (DogumYili LIKE '20__' AND DogumYili < (YEAR(GETDATE()) - 18))),
    Cinsiyet NCHAR(5), 
    CONSTRAINT Cinsiyet CHECK(Cinsiyet LIKE '[Ee]%' OR Cinsiyet LIKE '[Kk]%'),
    PRIMARY KEY(YonetmenID),
    UNIQUE(YonetmenID) 
)
CREATE TABLE Raflar(
    RafID INT IDENTITY(1, 1) NOT NULL,
    RafTuru INT FOREIGN KEY REFERENCES Turler(TurID) NOT NULL,
    PRIMARY KEY(RafID),
    UNIQUE(RafID)
)
CREATE TABLE Filmler(
    FilmID INT IDENTITY(1, 1) NOT NULL,
    FilmAdi NVARCHAR(10) NOT NULL,
    TurID INT FOREIGN KEY REFERENCES Turler(TurID) NOT NULL,
    YonetmenID INT FOREIGN KEY REFERENCES Yonetmenler(YonetmenID) NOT NULL,
    RafID INT FOREIGN KEY REFERENCES Raflar(RafID) NOT NULL,
    Fiyat MONEY NOT NULL,
    PRIMARY KEY(FilmID),
    UNIQUE(FilmID)
)
CREATE TABLE Musteriler(
    MusteriID INT IDENTITY(1, 1) NOT NULL,
    MusteriAdi NVARCHAR(10) NOT NULL,
    MusteriSoyadi NVARCHAR(10) NOT NULL,
    Adresi NVARCHAR(20),
    Telefon NVARCHAR(10),
    CONSTRAINT Telefon CHECK(Telefon NOT LIKE '0%'),
    PRIMARY KEY(MusteriID)
)
CREATE TABLE Oyuncular(
    OyuncuID INT IDENTITY(1, 1) NOT NULL,
    OyuncuAdi NVARCHAR(10) NOT NULL,
    OyuncuSoyadi NVARCHAR(10) NULL,
    DogumYili NCHAR(4),
    CONSTRAINT DogumYili CHECK(DogumYili LIKE '19%' OR DogumYili < YEAR(GETDATE())),
    PRIMARY KEY(OyuncuID),
    UNIQUE(OyuncuID)
)
CREATE TABLE Satislar(
    SatisID INT IDENTITY(1, 1) NOT NULL,
    FilmID INT FOREIGN KEY REFERENCES Filmler(FilmID) NOT NULL,
    MusteriID INT FOREIGN KEY REFERENCES Musteriler(MusteriID) NOT NULL,
    SatisAdedi INT NOT NULL
)
CREATE TABLE FilmOyuncu(
    FilmID INT FOREIGN KEY REFERENCES Filmler(FilmID) NOT NULL,
    OyuncuID INT FOREIGN KEY REFERENCES Oyuncular(OyuncuID) NOT NULL
)
USE CDCI >An error occurs here
GO
CREATE TRIGGER [TelefonKontrol]
ON [Musteriler]
FOR INSERT
AS
DECLARE @tel NVARCHAR(10)
SELECT @tel = Telefon FROM INSERTED 
IF @tel LIKE '0%'
    BEGIN
    PRINT 'Lütfen telefon numarasýný baþýna 0 rakamý koymayýnýz.'
    ROLLBACK TRAN
END
GO
CREATE TRIGGER [FilmRafTuruUyumu]
ON [Filmler]
FOR INSERT
AS
    DECLARE @filmTuru NVARCHAR(10)
    DECLARE @rafTuru NVARCHAR(10)
    DECLARE @rafID NVARCHAR(10)
        SELECT @rafID = RafID FROM INSERTED
        SELECT @filmTuru = TurID FROM INSERTED
        SELECT @rafTuru = Raflar.RafTuru FROM INSERTED 
        INNER JOIN Raflar ON Raflar.RafID = @rafID
IF @rafTuru <> @filmTuru
    BEGIN
    PRINT 'Lütfen film uygun rafa yerleþtiriniz!'
    ROLLBACK TRAN
END
GO

NOTE : I'm not new to the Database stuff because i got some classes of it but those were very basic level and theory-based classes. So you can consider me as 'starter'. Any helps, tips and suggestions are appriciated!

"An error occurs here" . What kind of error? What does the error say? — raven, Feb 09 '19 at 10:43
I normally work in something like DBeaver that is cross-vendor compatible. I would normally be looking in that kind of tool. But if you have SQL Server Explorer - that's equivalent... Do you have owner access to the schema? Perhaps something needs setting up for usage rights? You can normally see the system tables and see why some table/schema access works and some don't. See what needs to be added based on that logic. — JGFMK, Feb 09 '19 at 10:52
The fact that an error occurs at the `USE` statement *implies* you're `CREATE DATABASE` statement fails. Also, you can't use a database in the same batch you create it. You need to separate your `CREATE` and `USE` statements into separate batches — Thom A, Feb 09 '19 at 11:02
@raven it says "Could not locate entry in sysdatabases for database 'CDCI'. No entry found with that name. Make sure that the name is entered correctly." — , Feb 09 '19 at 15:28
@JGFMK it's a localhost so i'm able to do anything i want. This is very basic query i don't thing there should be anything set-up for this. — , Feb 09 '19 at 15:31
@leftOne yes, in SSMS and `sqlcmd` the word `GO` is a batch separator. — Thom A, Feb 09 '19 at 15:32
When you use the database it sounds like you don't have permission. — JGFMK, Feb 09 '19 at 15:42
`inserted` and `deleted` are tables so that they can represent the results of set operations. Designing a trigger on the assumption that it will always process exactly one row is generally a bad plan. If you are absolutely certain there will never be more than one row then _please_ add a check for the number of rows and use `RaIsError` or `Throw` to explicitly inform those who come along later that they have attempted to execute an unacceptable statement. (`if ( select Count(*) from inserted ) > 1 RaIsError( 'FooTable_Insert: No more than one row may be processed.', 25, 42 ) with log`) — HABO, Feb 09 '19 at 15:43
https://learn.microsoft.com/en-us/sql/t-sql/language-elements/use-transact-sql?view=sql-server-2017 Quote: "Requires CONNECT permission on the target database" https://www.ibm.com/support/knowledgecenter/en/SSMKFH/com.ibm.apmaas.doc/install/sql_config_agent_grant_permission_sqlserver.htm — JGFMK, Feb 09 '19 at 15:44
@HABO i assume you are talking about the trigger working with more than 1 values, isn't there a way to check for each value and then rollback just that values insertion, rest won't proceed and before will be inserted. — , Feb 09 '19 at 16:24
@JGFMK here is the thing; if a separately execute crete database and table queries it works. Thus no permission is required. — , Feb 09 '19 at 16:25
you need permission to use it afterwards. Crazy since you created it I know. But i think this is the issue. I daresay it's going to be setting up correlation between user/roles/database. What you can do with database.. i.e. insert, delete, update, select. Create tables, views etc. This is why I said look at the system tables - the stuff that contains metadata and if you have access to one database you can use already.. Compare that to the new one. https://stackoverflow.com/questions/887370/sql-server-extract-table-meta-data-description-fields-and-their-data-types — JGFMK, Feb 09 '19 at 16:29
https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms175808(v=sql.105) https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms187965%28v%3dsql.105%29 https://www.youtube.com/watch?v=Mkw7tRfh2_8 — JGFMK, Feb 09 '19 at 16:35
https://www.youtube.com/watch?v=EMATHDoIO8M - Just use your existing Windows profile - and do Windows Authentication in SQL Server Management Studio - be sure to search out your login. — JGFMK, Feb 09 '19 at 17:00
@leftOne A `for insert` trigger executes after the rows are inserted. It could `rollback` the transaction, thus removing _all_ of the new rows. You could use an `instead of insert` trigger to insert only the "good" rows and ignore the rest, but if it does a `rollback` then all of the rows will be affected. — HABO, Feb 09 '19 at 18:04
@HABO So instead of plus rollback will just roll back whole insertion even there is one that doesn't fit standarts, right? — , Feb 09 '19 at 19:59
@leftOne A `rollback` applies to an entire transaction which at a minimum is a single statement, e.g. `insert`. If one of 13 rows smelled a little funny how would you roll back only the changes that it made in an explicit transaction? Maybe a bank account balance was adjusted for 13 checks withdrawn and deposited in a single `update`. Now you want to kick that one row out of an `insert` within the same transaction. How does the database reckon all of the side effects attributed to just that one of 13? — HABO, Feb 10 '19 at 02:36
@HABO i was just wondering whether there is a way or not. But i think they could've have set it up in such way that transaction could be canceled, right? Or i'm a missing to whole point? — , Feb 10 '19 at 22:03
@leftOne The purpose of a _transaction_ is to group several operations into an _all or nothing_ action and control how that action interacts with other simultaneous activities via a specified [isolation level](https://learn.microsoft.com/en-us/sql/connect/jdbc/understanding-isolation-levels?view=sql-server-2017). (That does not mean that a `rollback` causes all evidence of the operations to vanish, e.g. if you `rollback` an `insert` it will leave a gap in the values in an `identity` column.) — HABO, Feb 10 '19 at 22:50

score 1 · Answer 1 · answered Feb 09 '19 at 11:05

You need to check your location path for mdf and ldf files.

     CREATE DATABASE CDCI
    ON PRIMARY(
        Name = cddukkan_data,
        FileName = 'C:\Program Files\Microsoft SQL Server\MSSQL14.VERITABANIM\MSSQL\DATA\cdci.mdf',
        Size = 16 MB,
        FileGrowth = 1 MB,
        MaxSize = 1 GB
    )
    LOG ON(
        Name = cddukkan_log,
        FileName = 'C:\Program Files\Microsoft SQL Server\MSSQL14.VERITABANIM\MSSQL\DATA\cdci.ldf',
        Size = 8 MB,
        FileGrowth = 5%,
        MaxSize = 512 MB
    )
    go
    USE CDCI    -->An error occurs here
    GO
    CREATE TABLE Turler(
        TurID INT IDENTITY(1, 1) NOT NULL,
        TurAdi NVARCHAR(10) NOT NULL,
        PRIMARY KEY(TurID),
        UNIQUE(TurID)
    )
    CREATE TABLE Yonetmenler(
        YonetmenID INT IDENTITY(1, 1) NOT NULL,
        YonetmenAdi NVARCHAR(10) NOT NULL,
        YonetmenSoyadi NVARCHAR(10) NULL,
        DogumYili NCHAR(4),
        CONSTRAINT DogumYili CHECK(DogumYili LIKE '19__' OR (DogumYili LIKE '20__' AND DogumYili < (YEAR(GETDATE()) - 18))),
        Cinsiyet NCHAR(5), 
        CONSTRAINT Cinsiyet CHECK(Cinsiyet LIKE '[Ee]%' OR Cinsiyet LIKE '[Kk]%'),
        PRIMARY KEY(YonetmenID),
        UNIQUE(YonetmenID) 
    )
    CREATE TABLE Raflar(
        RafID INT IDENTITY(1, 1) NOT NULL,
        RafTuru INT FOREIGN KEY REFERENCES Turler(TurID) NOT NULL,
        PRIMARY KEY(RafID),
        UNIQUE(RafID)
    )
    CREATE TABLE Filmler(
        FilmID INT IDENTITY(1, 1) NOT NULL,
        FilmAdi NVARCHAR(10) NOT NULL,
        TurID INT FOREIGN KEY REFERENCES Turler(TurID) NOT NULL,
        YonetmenID INT FOREIGN KEY REFERENCES Yonetmenler(YonetmenID) NOT NULL,
        RafID INT FOREIGN KEY REFERENCES Raflar(RafID) NOT NULL,
        Fiyat MONEY NOT NULL,
        PRIMARY KEY(FilmID),
        UNIQUE(FilmID)
    )
    CREATE TABLE Musteriler(
        MusteriID INT IDENTITY(1, 1) NOT NULL,
        MusteriAdi NVARCHAR(10) NOT NULL,
        MusteriSoyadi NVARCHAR(10) NOT NULL,
        Adresi NVARCHAR(20),
        Telefon NVARCHAR(10),
        CONSTRAINT Telefon CHECK(Telefon NOT LIKE '0%'),
        PRIMARY KEY(MusteriID)
    )
    CREATE TABLE Oyuncular(
        OyuncuID INT IDENTITY(1, 1) NOT NULL,
        OyuncuAdi NVARCHAR(10) NOT NULL,
        OyuncuSoyadi NVARCHAR(10) NULL,
        DogumYili NCHAR(4),
        CONSTRAINT DogumYili CHECK(DogumYili LIKE '19%' OR DogumYili < YEAR(GETDATE())),
        PRIMARY KEY(OyuncuID),
        UNIQUE(OyuncuID)
    )
    CREATE TABLE Satislar(
        SatisID INT IDENTITY(1, 1) NOT NULL,
        FilmID INT FOREIGN KEY REFERENCES Filmler(FilmID) NOT NULL,
        MusteriID INT FOREIGN KEY REFERENCES Musteriler(MusteriID) NOT NULL,
        SatisAdedi INT NOT NULL
    )
    CREATE TABLE FilmOyuncu(
        FilmID INT FOREIGN KEY REFERENCES Filmler(FilmID) NOT NULL,
        OyuncuID INT FOREIGN KEY REFERENCES Oyuncular(OyuncuID) NOT NULL
    )
    USE CDCI -->An error occurs here
    GO
    CREATE TRIGGER [TelefonKontrol]
    ON [Musteriler]
    FOR INSERT
    AS
    DECLARE @tel NVARCHAR(10)
    SELECT @tel = Telefon FROM INSERTED 
    IF @tel LIKE '0%'
        BEGIN
        PRINT 'Lütfen telefon numarasýný baþýna 0 rakamý koymayýnýz.'
        ROLLBACK TRAN
    END
    GO
    CREATE TRIGGER [FilmRafTuruUyumu]
    ON [Filmler]
    FOR INSERT
    AS
        DECLARE @filmTuru NVARCHAR(10)
        DECLARE @rafTuru NVARCHAR(10)
        DECLARE @rafID NVARCHAR(10)
            SELECT @rafID = RafID FROM INSERTED
            SELECT @filmTuru = TurID FROM INSERTED
            SELECT @rafTuru = Raflar.RafTuru FROM INSERTED 
            INNER JOIN Raflar ON Raflar.RafID = @rafID
    IF @rafTuru <> @filmTuru
        BEGIN
        PRINT 'Lütfen film uygun rafa yerleþtiriniz!'
        ROLLBACK TRAN
    END
    GO

You may create your database at any location as long as read write permission is given. — DarkRob, Feb 11 '19 at 04:15

Creating all database 'things' in one query

1 Answers1