SQL Table Constraints: Protect Keys and Required Values

A query can detect bad records after they arrive. A table constraint can prevent some invalid records from being stored in the first place. Constraints express specific data rules; they do not decide every business question or guarantee that entered facts are true.

Practice setup: Use the fictional database in the SQL practice lab. Run that setup once in a fresh SQLite practice database. Customers are Amina (1, Nairobi), Brian (2, Mombasa) and Chao (3, Nairobi). Orders are 101 (customer 1, amount 1200), 102 (customer 2, amount 800), 103 (customer 1, amount 600) and 104 (customer 3, amount 1500). Amounts are fictional units. The examples in this series use SQLite syntax; check your database's documentation before adapting them elsewhere.

Create a separate practice table

Use a fresh disposable database, or a table name that does not already exist. This example does not modify the shop tables.

CREATE TABLE practice_products (
  product_id INTEGER PRIMARY KEY,
  code TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  price INTEGER NOT NULL CHECK (price >= 0)
);
INSERT INTO practice_products(product_id, code, name, price)
VALUES (1, 'P101', 'Notebook', 120),
       (2, 'P102', 'Pen', 25);
SELECT product_id, code, name, price
FROM practice_products
ORDER BY product_id;

You should see two rows. product_id identifies each product. code must be present and unique. name must not be NULL. price must be present and nonnegative. The example uses whole fictional units to keep the arithmetic simple.

Separate the roles of the rules

PRIMARY KEY identifies records. UNIQUE prevents repeated non-NULL codes here. NOT NULL rejects a missing value. CHECK tests a condition such as price being at least zero. These rules work together; one does not automatically replace another.

In SQLite, UNIQUE alone can allow multiple NULL values. That is why code also has NOT NULL. CHECK expressions also have NULL-related behaviour, so a required numeric field should not rely on a nonnegative check alone to reject missing values.

Try invalid inputs deliberately

In the practice database, attempt another product using code P101. It should fail the unique rule. Try a negative price and it should fail the check. Try NULL for name and it should fail the required-value rule. Read the actual error to see which rule rejected the record.

Do not add those invalid rows by weakening the constraints simply to complete the exercise. An error can be the expected correct behaviour when the input violates the design.

Understand what still gets through

An empty text string is not NULL. The current name rule permits an empty string unless you add a suitable additional check. A price of 999999 is nonnegative, so it passes even if it is a typing mistake. Constraints validate defined rules, not real-world accuracy.

Foreign keys express relationships between tables. SQLite foreign-key enforcement must be enabled for the connection when you rely on it. A relationship written in a schema should not be assumed enforced without checking the database configuration.

Design from the data's meaning

Decide which fields identify records, which may be missing and what values are valid before writing CREATE TABLE. Avoid using a person's name as a unique key merely because the small sample has no repeated names.

Run setup scripts once or start fresh when learning. If you see table already exists, do not keep inserting the sample rows and then blame duplicate errors on the database. The existing state matters.

Try it yourself

Would the current table accept price 0? Would it accept a second product with code P102? Would it accept a blank name made of two single quotes? Explain each answer.

Keep exploring: SQL learning path.