Creating an appropriate prompt for sql server code

API
1

My aim is to create several tables in a database depending on a given application. I’m counting on gpt to do this, but it seems to miss out on some essential instructions - most importantly to separate addition of constraints from the create table statements.

For example, I’m using gpt-3.5-turbo to generate sql server code with the following prompt:

PROMPT

GOAL

A todo list application

SCHEMA

USER_CATS - user categories

id name description
1 Administrator Has full access and can perform any action on the application.
2 Manager Can manage users, tasks, categories and priorities. Cannot create or delete users.
3 User Can view tasks, add and update own tasks. Cannot manage users, categories or priorities.

USER_CAPS - user category capabilities

id idUser description functionMain
1 1 Can manage users manageUsers
2 1 Can manage tasks manageTasks
3 1 Can manage categories manageCategories
4 1 Can manage priorities managePriorities
5 1 Can view tasks viewTasks
6 1 Can add tasks addTasks
7 1 Can update tasks updateTasks
8 1 Can delete tasks deleteTasks
9 2 Can manage tasks manageTasks
10 2 Can manage categories manageCategories
11 2 Can manage priorities managePriorities
12 2 Can view tasks viewTasks
13 2 Can add tasks addTasks
14 2 Can update tasks updateTasks
15 3 Can view tasks viewTasks
16 3 Can add tasks addTasks
17 3 Can update own tasks updateOwnTasks

TASK

You are ObjectGPT; you produce SQL Server code to generate database objects to be used by the functions in the USR1 table.

Don’t recreate the user categories object and user capabilities object - these are already created.
The new objects we create will be manipulated by queries to the database.
The actual functions are python functions to be implemented elsewhere. Just focus on producing a comprehensive set of objects.
Table names must always be 4 characters long, uppercase. Primary object tables start with an O. Secondary objects should start with the last 3 letters of the related primary object and a number at the end. Column names always start with lowercase and use camelcase
For each column you add, add a description with

AddOrUpdateColumnDescription   
@tableName NVARCHAR(128), 
@columnName NVARCHAR(128), 
@description NVARCHAR(255)

Terminate statements with semicolon always.
Follow database normalization, don’t split table unless necessary, add foreign keys separate from create statements. Add constraints with alter table statement, not inside the create statement.
Don’t use other stored procedures. Remember the semicolon.
Please list the essential conditions above then write the code.

But it always returns the constraints statements within the create statement. The problem is that I can’t implement a constraint for a table that doesn’t exist.

SAMPLE OUTPUT

This is a sample of the completion returned by gpt - others are similar

-- Create OUSR table for users
CREATE TABLE OUSR 
(
        id INT PRIMARY KEY IDENTITY(1,1),
        username NVARCHAR(50) UNIQUE NOT NULL,
        password NVARCHAR(50) NOT NULL,
        email NVARCHAR(50) UNIQUE NOT NULL,
        idUserCat INT NOT NULL,
        CONSTRAINT FK_OUSR_USERCAT FOREIGN KEY (idUserCat) REFERENCES USER_CATS(id)
);

EXECUTE sp_addextendedproperty N'MS_Description', N'Username of the user', N'SCHEMA', N'dbo', N'TABLE', N'OUSR', N'COLUMN', N'username';
EXECUTE sp_addextendedproperty N'MS_Description', N'Password of the user', N'SCHEMA', N'dbo', N'TABLE', N'OUSR', N'COLUMN', N'password';
EXECUTE sp_addextendedproperty N'MS_Description', N'Email of the user', N'SCHEMA', N'dbo', N'TABLE', N'OUSR', N'COLUMN', N'email';
EXECUTE sp_addextendedproperty N'MS_Description', N'ID of the user category the user belongs to', N'SCHEMA', N'dbo', N'TABLE', N'OUSR', N'COLUMN', N'idUserCat';


-- Create OTSK table for tasks
CREATE TABLE OTSK 
(
        id INT PRIMARY KEY IDENTITY(1,1),
        title NVARCHAR(50) NOT NULL,
        description NVARCHAR(255),
        dueDate DATE NOT NULL,
        idPriority INT NOT NULL,
        idUser INT NOT NULL,
        CONSTRAINT FK_OTSK_PRIORITY FOREIGN KEY (idPriority) REFERENCES OPRI(id),
        CONSTRAINT FK_OTSK_USER FOREIGN KEY (idUser) REFERENCES OUSR(id)
);

EXECUTE sp_addextendedproperty N'MS_Description', N'Title of the task', N'SCHEMA', N'dbo', N'TABLE', N'OTSK', N'COLUMN', N'title';
EXECUTE sp_addextendedproperty N'MS_Description', N'Description of the task', N'SCHEMA', N'dbo', N'TABLE', N'OTSK', N'COLUMN', N'description';
EXECUTE sp_addextendedproperty N'MS_Description', N'Due date of the task', N'SCHEMA', N'dbo', N'TABLE', N'OTSK', N'COLUMN', N'dueDate';
EXECUTE sp_addextendedproperty N'MS_Description', N'ID of the priority level of the

How do I modify the prompt to seperate the addition of constraints from the creation of the tables