CarHub-ASP.NET-API/SQLQuery3.sql at master · kerimli-veli/CarHub-ASP.NET-API · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
CREATE TABLE [dbo].[Users] (
    [Id]            INT            IDENTITY (1, 1) NOT NULL,
    [Name]          NVARCHAR (100) NOT NULL,
    [Surname]       NVARCHAR (100) NOT NULL,
    [Email]         NVARCHAR (255) NOT NULL,
    [Phone]         NVARCHAR (20)  NULL,
    [PasswordHash]  NVARCHAR (255) NOT NULL,
    [UserImagePath] NVARCHAR (500) NULL,
    [UserRole]      INT            NOT NULL,
    [CreatedBy]     INT            NULL,
    [UpdatedBy]     INT            NULL,
    [DeletedBy]     INT            NULL,
    [CreatedDate]   DATETIME       NULL,
    [UpdatedDate]   DATETIME       NULL,
    [DeletedDate]   DATETIME       NULL,
    [IsDeleted]     BIT            DEFAULT ((0)) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    UNIQUE NONCLUSTERED ([Email] ASC)
);


CREATE TABLE [dbo].[Cars] (
    [Id]             INT            IDENTITY (1, 1) NOT NULL,
    [Brand]          NVARCHAR (255) NOT NULL,
    [BrandImagePath] NVARCHAR (300) NULL,
    [Model]          NVARCHAR (255) NOT NULL,
    [Year]           INT            NOT NULL,
    [Price]          INT            NOT NULL,
    [Fuel]           INT            NOT NULL,
    [Transmission]   INT            NOT NULL,
    [Miles]          FLOAT (53)     NOT NULL,
    [Body]           INT            NOT NULL,
    [BodyTypeImage]  NVARCHAR (300) NULL,
    [Color]          NVARCHAR (255) NOT NULL,
    [VIN]            NVARCHAR (255) NOT NULL,
    [Text]           NVARCHAR (MAX) NOT NULL,
    [IsDeleted]      BIT            DEFAULT ((0)) NOT NULL,
    [CreatedDate]    DATETIME       NOT NULL,
    [CreatedBy]      INT            NULL,
    [UpdatedDate]    DATETIME       NULL,
    [UpdatedBy]      INT            NULL,
    [DeletedDate]    DATETIME       NULL,
    [DeletedBy]      INT            NULL,
    [IsFavorite] BIT DEFAULT ((0)) NOT NULL,
    [UserId]         INT            NOT NULL,
    CONSTRAINT [PK_Cars] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Cars_Users] FOREIGN KEY ([UserId]) REFERENCES [dbo].[Users] ([Id]) ON DELETE CASCADE
);


CREATE TABLE [dbo].[UserFavorite] (
    [Id]         INT IDENTITY (1, 1) NOT NULL,
    [UserId]     INT NOT NULL,
    [CarId]      INT NOT NULL,
    PRIMARY KEY CLUSTERED ([UserId] ASC, [CarId] ASC),
    FOREIGN KEY ([UserId]) REFERENCES [dbo].[Users] ([Id]),
    FOREIGN KEY ([CarId]) REFERENCES [dbo].[Cars] ([Id])
);

CREATE TABLE [dbo].[CarImage] (
    [Id]        INT            IDENTITY (1, 1) NOT NULL,
    [CarId]     INT            NOT NULL,
    [ImagePath] NVARCHAR (500) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    FOREIGN KEY ([CarId]) REFERENCES [dbo].[Cars] ([Id])
);


CREATE TABLE [dbo].[RefreshTokens] (
    [Id]             INT            IDENTITY (1, 1) NOT NULL,
    [Token]          NVARCHAR (255) NOT NULL,
    [UserId]         INT            NOT NULL,
    [ExpirationDate] DATETIME2 (7)  NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);