Where did I make mistake in making this SQL?

Question

I made a SQL file which makes me tables etc. There is one problem with it. It makes clustered key to table Product BUT refuses to make it to rest of tables.

Can someone please help me out?

IF OBJECT_ID(N'[dbo].[FK_Product_ProductList]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Product] DROP CONSTRAINT [FK_Product_ProductList];
GO
IF OBJECT_ID(N'[dbo].[FK_ProductList_User]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ProductList] DROP CONSTRAINT [FK_ProductList_User];
GO
IF OBJECT_ID(N'[dbo].[Product]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Product];
GO
IF OBJECT_ID(N'[dbo].[ProductList]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ProductList];
GO
CREATE TABLE [dbo].[Product] (
    [Id] int  NOT NULL,
    [ProductListId] int  NOT NULL,
    [Name] nvarchar(50)  NOT NULL,
    [Weight] decimal(10,2)  NOT NULL,
    [Price] decimal(10,2)  NOT NULL,
    [Amount] int  NOT NULL
);
GO

-- Creating table 'ProductList'
CREATE TABLE [dbo].[ProductList] (
    [Id] int  NOT NULL,
    [UserId] int  NOT NULL,
    [Name] nvarchar(50)  NOT NULL
);
ALTER TABLE [dbo].[Product]
ADD CONSTRAINT [PK_Product]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

ALTER TABLE [dbo].[ProductList]
ADD CONSTRAINT [PK_ProductList]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO
ALTER TABLE [dbo].[Product]
ADD CONSTRAINT [FK_Product_ProductList]
    FOREIGN KEY ([ProductListId])
    REFERENCES [dbo].[ProductList]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

CREATE INDEX [IX_FK_Product_ProductList]
ON [dbo].[Product]
    ([ProductListId]);
GO

ALTER TABLE [dbo].[ProductList]
ADD CONSTRAINT [FK_ProductList_User]
    FOREIGN KEY ([UserId])
    REFERENCES [dbo].[User]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

CREATE INDEX [IX_FK_ProductList_User]
ON [dbo].[ProductList]
    ([UserId]);
GO

In practise, everything should be working fine but still I feel like i'm missing something out there.

I also tried to fix it manually from database but when I tried to save the file, I've got the message "Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created or enabled the option Prevent saving changes that require the table to be re-created.

*For purpose of this post I removed creating and editing Users table as its going same way as Product List.

User Product List"

Problem Solved. Everything was bugging because of this small change:

CREATE TABLE [dbo].[ProductList] (
    [Id] int  IDENTITY (1,1) NOT NULL,
    [UserId] int  NOT NULL,
    [Name] nvarchar(50)  NOT NULL

I'm really grateful for Your help!

This SQL looks like from SQL Server or Sybase. Which one is it? — The Impaler, Nov 04 '20 at 23:13
Every chunk of lines ending with `GO` is a separate SQL Server operation. Issue those chunks one at a time; cut and paste them into SSMS and press Execute. When one fails, please [edit] your question to tell us which one failed and the error message it gave. — O. Jones, Nov 04 '20 at 23:29
@O.Jones I did everything as you said. After first executing i dont have any error messages, everything goes as smooth as possible. — Michał Grabarz, Nov 04 '20 at 23:57
@madreflection while executing i dont have any errors. But still Id's doesnt have identity as Id if that makes sense. When i'm trying to do it manually it says that "Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created or enabled the option Prevent saving changes that require the table to be re-created. User Product List" — Michał Grabarz, Nov 04 '20 at 23:59
Please make your Question (heading) descriptive of the issue. It will aid others in searching and better attract those that might be able to answer your question. — Jon P, Nov 05 '20 at 00:06
In SSMS (SQL Server Management Studio) go to tools -> Options -> Designers then untick 'Prevent saving changes that require table re-creation'. Certain types of changes cannot be done directly to the table, and the table gets dropped and re-created in the background. By default this option is ticked preventing that - I typically turn it off. See https://stackoverflow.com/questions/6810425/sql-server-saving-changes-is-not-permitted-error-prevent-saving-changes-that — seanb, Nov 05 '20 at 00:09
The IDs don't *"have identity"* because you don't have the `IDENTITY` keyword on them, e.g. `[Id] int IDENTITY NOT NULL`. — madreflection, Nov 05 '20 at 00:49
@madreflection excacly that was the case of whole problem. Thank you kindly! — Michał Grabarz, Nov 05 '20 at 00:55
Please add the solution as an answer, rather than editing the question. — Dale K, Nov 05 '20 at 02:36

Where did I make mistake in making this SQL?

0 Answers0