Testing AI-Generated DDL Against the Rules It Was Given
A developer pastes a paragraph of business rules into an AI coding agent and gets back three CREATE TABLE statements and a trigger. The script runs without an error. The reviewer now has to work out which of those rules the database enforces and which ones exist only in the comments and the notes that came with it.
So I tried it. I wrote a short schema description, had an AI coding agent turn it into DDL, deployed the unedited output to SQL Server 2019 and SQL Server 2025, and tried to break the rules in the description with INSERT, UPDATE and DELETE statements.
The Description and the Prompt
The schema is a booking system for a small physiotherapy clinic. This is the whole prompt, as given to the agent:
|
1 2 3 4 5 6 7 8 9 10 11 |
Write SQL Server CREATE TABLE statements for this schema. We run a small physiotherapy clinic and need a database for bookings. Patients: first name, last name, date of birth, email address and phone number. Every patient must have an email address, and no two patients can share one. Practitioners: first name, last name, email address and speciality. Practitioner email addresses are also unique. Practitioners who have left are marked inactive and can't be booked. Appointments: one patient with one practitioner, with a start time and an end time. The end must be after the start. Appointments are at least 15 minutes and at most 2 hours long. Status is one of booked, completed, cancelled or no-show. A practitioner can't have two appointments that overlap. Cancelled appointments are kept for our records and don't block the time slot. A patient who has appointments can't be deleted. All times are in the clinic's local time. |
I didn’t ask for naming or formatting conventions, and the prompt says nothing about collation, indexes or concurrency.
What the Agent Produced
The DDL came from a Claude model running as a sub-agent in GitHub Copilot CLI. I didn’t record the exact model ID. That session wasn’t blind. It already had my test brief in context, including the list of defects I expected to find, and it had my own T-SQL style instructions loaded. A fresh session with no brief, or a different model, could do better or worse. This is one sample from one session, and here’s its output, unedited, including its -- comments:
|
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 |
CREATE TABLE Patients ( PatientId INT IDENTITY(1,1) PRIMARY KEY, FirstName NVARCHAR(100) NOT NULL, LastName NVARCHAR(100) NOT NULL, DateOfBirth DATE NOT NULL, Email NVARCHAR(255) NOT NULL UNIQUE, Phone NVARCHAR(20) NULL, CreatedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME() ); CREATE TABLE Practitioners ( PractitionerId INT IDENTITY(1,1) PRIMARY KEY, FirstName NVARCHAR(100) NOT NULL, LastName NVARCHAR(100) NOT NULL, Email NVARCHAR(255) NOT NULL UNIQUE, Specialty NVARCHAR(100) NULL, IsActive BIT NOT NULL DEFAULT 1, CreatedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME() ); CREATE TABLE Appointments ( AppointmentId INT IDENTITY(1,1) PRIMARY KEY, PatientId INT NOT NULL, PractitionerId INT NOT NULL, StartTime DATETIME2 NOT NULL, EndTime DATETIME2 NOT NULL, Status NVARCHAR(20) NOT NULL DEFAULT 'Booked', Notes NVARCHAR(MAX) NULL, CreatedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(), CONSTRAINT FK_Appointments_Patients FOREIGN KEY (PatientId) REFERENCES Patients(PatientId), CONSTRAINT FK_Appointments_Practitioners FOREIGN KEY (PractitionerId) REFERENCES Practitioners(PractitionerId), CONSTRAINT CHK_Appointments_EndAfterStart CHECK (EndTime > StartTime), CONSTRAINT CHK_Appointments_Duration CHECK (DATEDIFF(MINUTE, StartTime, EndTime) BETWEEN 15 AND 120), CONSTRAINT CHK_Appointments_Status CHECK (Status IN ('Booked', 'Completed', 'Cancelled', 'NoShow')) ); CREATE INDEX IX_Appointments_Practitioner_Time ON Appointments (PractitionerId, StartTime, EndTime); GO -- Prevent overlapping appointments for the same practitioner. -- A CHECK constraint can't look at other rows, so this uses a trigger. -- Cancelled appointments are ignored so they don't block the slot. CREATE TRIGGER TR_Appointments_PreventOverlap ON Appointments AFTER INSERT, UPDATE AS BEGIN SET NOCOUNT ON; IF EXISTS ( SELECT 1 FROM Appointments a INNER JOIN inserted i ON a.PractitionerId = i.PractitionerId AND a.AppointmentId <> i.AppointmentId WHERE a.Status <> 'Cancelled' AND i.Status <> 'Cancelled' AND a.StartTime < i.EndTime AND a.EndTime > i.StartTime ) BEGIN RAISERROR('The appointment overlaps an existing appointment for this practitioner.', 16, 1); ROLLBACK TRANSACTION; RETURN; END END; GO |
The agent also listed which rule each object covers. It said that the inactive-practitioner rule isn’t enforced and should be checked in the application. It also said that CreatedAt is a UTC audit column. CreatedAt and Notes weren’t in the description.
That’s a reasonable first draft, and nothing in it looks wrong on a read-through.
How I Tested It
I deployed the script unchanged into a scratch database on SQL Server 2019 (15.0.4500.2) and SQL Server 2025 (17.0.5015.4), both using the instance default collation SQL_Latin1_General_CP1_CI_AS. I deployed it a third time on 2025 into a database created with Latin1_General_100_CS_AS, a case-sensitive collation. Then I ran one batch per test, each with a value chosen to break a rule. I tested on one workstation, and I sent the prompt once.
Results, Rule by Rule
SQL Server 2019 and 2025 gave the same result for every test in the case-insensitive databases. The case-sensitive database differed in two tests, noted in the table.
| Rule from the description | Test | Result |
|---|---|---|
| Every patient has an email | Insert '' |
Accepted |
| Patient email is unique | Same address, same case | Rejected |
| Patient email is unique | Ana.Silva@Contoso.example after ana.silva@contoso.example |
Rejected (CI), accepted (CS) |
| Patient email is unique | Same address with a trailing space | Rejected |
| Patient email is unique | Same address with a leading space | Accepted |
| End after start | End 30 minutes before start | Rejected |
| At least 15 minutes | 10 minutes | Rejected |
| At least 15 minutes | 08:00:59 to 08:15:00 (14 min 1 s) | Accepted |
| At most 2 hours | 12:00:00 to 14:00:59 (2 h 0 min 59 s) | Accepted |
| Status list | Pending |
Rejected |
| Status list | no-show, as the description spells it |
Rejected |
| Status list | cancelled, lower case |
Accepted (CI), rejected (CS) |
| No overlap | Overlapping single insert | Rejected |
| No overlap | Two overlapping rows in one INSERT |
Rejected |
| No overlap | Un-cancel a booking whose slot was rebooked | Rejected |
| Cancelled don’t block the slot | Book over a cancelled slot | Accepted |
| Inactive can’t be booked | Book the inactive practitioner | Accepted |
| Cancelled are kept | DELETE a cancelled appointment |
Accepted |
| Patient with appointments can’t be deleted | DELETE the patient |
Rejected |
Most of the tests went the way the rules say: 12 of the 19 in the case-insensitive databases.
Rules with no enforcement. Nothing stops a booking with an inactive practitioner, and the agent pointed me to the application for that one. For “kept for our records”, the Status column records the cancellation, but nothing stops a DELETE of the row. The agent didn’t mention that one.
The email rules depend on the database collation. The DDL has no COLLATE clause, so each column takes the database’s default collation.[1][2] A case-sensitive collation distinguishes upper case from lower case.[3] In the case-sensitive database, the mixed-case copy of an existing address went in as a second patient. The same DDL in the other database rejected it. A leading space created a second patient in both, and an empty string met the NOT NULL “must have an email” rule.
The duration CHECK doesn’t measure elapsed time. DATEDIFF counts datepart boundaries crossed between the two values, not elapsed time.[4] From 08:00:59 to 08:15:00 crosses 15 minute boundaries, so a 14-minute-1-second booking passed. A booking 59 seconds over two hours also passed. Comparing EndTime to DATEADD(minute, 15, StartTime) would test the actual length.
The status list doesn’t match the description. The description says “no-show”. The CHECK allows NoShow. Any code that sends the value as written in the requirements gets error 547. The description also writes all four statuses in lower case, and the CHECK lists them in mixed case. The case-insensitive databases accepted lower-case cancelled, but the case-sensitive database rejected it. I ran only cancelled and no-show there, but lower-case booked and completed go through the same IN list under the same collation, so all four statuses as the description writes them fail in that database.
The Overlap Trigger
The trigger rejected every overlap I tried from one session, including a multi-row insert and an UPDATE that changed a booking back from cancelled. Two things it does are still a problem.
It depends on the isolation level. I opened a transaction in one session, inserted a 14:00 to 15:00 booking, and waited eight seconds before committing. Two seconds in, a second session inserted 14:30 to 15:30 for the same practitioner. With the READ_COMMITTED_SNAPSHOT database option off, the second session waited about six seconds for the first to commit, then the trigger rejected its row. With it on, the second insert finished at once, both rows committed, and the practitioner had two overlapping bookings. Both instances behaved the same way, and I ran each case once. Under read committed snapshot isolation (RCSI), a statement reads the last committed version of each row, so the trigger couldn’t see the first session’s uncommitted booking.[5] With RCSI off, the trigger held for this one staggered timing. I didn’t test two inserts arriving at the same moment with RCSI off. Sessions that use SNAPSHOT isolation also read row versions, so they may have the same gap, but I didn’t test that either. RCSI is set per database, so the same DDL can behave differently from one database to the next. The same guide notes that RCSI is on by default in Azure SQL Database, which I didn’t test.
Its ROLLBACK ends the caller’s transaction. I opened a transaction, inserted a valid booking, then inserted an overlapping one. SQL Server returned error 3609, The transaction ended in the trigger. The batch has been aborted. The valid booking was gone, @@TRANCOUNT was 0, and the COMMIT never ran. Code that wraps several bookings in one transaction and expects to handle one failure loses all of them.
Things the Rules Didn’t Mention
Ten constraints have system-generated names. The primary keys, the two UNIQUE constraints, and the five defaults got names like UQ__Patients__A9D1053409E07876. The primary key and unique constraint suffixes were different in each of the three databases, including the two on the same instance. A later migration that drops or alters one of those constraints by name may work in one environment and fail in the next.
No index supports the patient foreign key. A foreign key constraint doesn’t create an index.[6] The agent’s only nonclustered index leads on PractitionerId. With 50,009 appointments loaded on SQL Server 2025, deleting one patient who had no appointments cost 470 logical reads on Appointments, because SQL Server scanned the table to check the foreign key. With an index on PatientId, the same delete cost 2 logical reads.
The trigger’s cost grows faster than the row count. The trigger compares every inserted row with every other appointment for the same practitioner. I timed single INSERT statements for one practitioner on SQL Server 2025, each rolled back afterward. Each timing is a single run:
| Rows in one INSERT | Elapsed ms | CPU ms |
|---|---|---|
| 1,000 | 409 | 398 |
| 2,000 | 1,246 | 1,224 |
| 4,000 | 3,457 | 3,454 |
| 8,000 | 14,185 | 13,995 |
My first data load put 50,000 appointments for one practitioner in a single statement. I stopped it after more than four minutes of CPU, spent inside the trigger according to sys.dm_exec_requests. Spreading the same number of rows across 500 practitioners took under two seconds. A clinic doesn’t book 8,000 appointments in one statement, but a data migration or an import from an old system might.
The data types were fine. Every column was either declared NULL or NOT NULL, or is one of the three IDENTITY primary key columns, and a primary key forces NOT NULL.[6] Session defaults didn’t matter. The time columns are datetime2 with no precision given, which is datetime2(7). Seven decimal places for appointment times is more than the clinic needs. It isn’t a correctness problem. The CreatedAt defaults use SYSUTCDATETIME(), while the description says all times are local. The agent disclosed that one.
What I’d Change Before Using It
These are ordinary DDL review items, and each one came from a test or a catalogue query. I haven’t deployed or tested any of these changes. I’d name every constraint, set a collation on the email columns or store a normalized copy with its own unique constraint, add a CHECK that rejects empty and padded addresses, and replace the duration check with a DATEADD comparison. I’d add an index on Appointments.PatientId, and decide what “kept for our records” means: a trigger that blocks deletes, or a permission that does. I’d make the status list match the description, or get the description changed to match the list, and settle on one spelling and case. For inactive practitioners, I’d check IsActive in the booking procedure or a trigger, or confirm that the application does it, as the agent suggested.
For the overlap rule, I’d decide which isolation level the application uses before I trusted the trigger, and I’d test it with two sessions, not one. I’d also test what a caller’s multi-statement transaction gets back when the trigger fires, because error 3609 rolls back the caller’s whole transaction.
Writing T-SQL with an AI Partner covers the review loop for code like this. Proving the Contract Holds: Testing Database Code covers turning tests like these into something that runs on every build. If a nullable column is part of your rules, The CHECK Constraint That Lets NULL Through covers a CHECK case this schema didn’t reach. And the naming and style rules I’d apply here are in Why I Keep a T-SQL Style Guide, and What Testing It Cost Me.
If you’ve tested generated DDL against its own requirements, I’d like to know which rules failed for you. Find me on Bluesky or LinkedIn.
References
- CREATE TABLE (Transact-SQL) - Microsoft Learn. States that a character column with no COLLATE clause is assigned the collation of its user-defined data type, if it has one, or the default collation of the database. ↩
- Set or Change the Column Collation - Microsoft Learn. States that a column uses the database collation unless a COLLATE clause in CREATE TABLE or ALTER TABLE overrides it. ↩
- Collation and Unicode support - Microsoft Learn. Defines the case-sensitive (_CS) collation option as one that distinguishes between uppercase and lowercase letters. ↩
- DATEDIFF (Transact-SQL) - Microsoft Learn. States that DATEDIFF returns the count of datepart boundaries crossed between the start and end values. ↩
- Transaction locking and row versioning guide - Microsoft Learn. States that with READ_COMMITTED_SNAPSHOT on, READ COMMITTED uses row versioning for statement-level read consistency, and that the option is on by default in Azure SQL Database. ↩
- Primary and foreign key constraints - Microsoft Learn. States that, unlike a primary key, a foreign key constraint doesn’t automatically create a corresponding index, and that columns in a primary key are set to NOT NULL when nullability isn’t specified. ↩