Ddl Type Alternatives

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  status ENUM('pending', 'processing', 'shipped', 'delivered') NOT NULL
);

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  status VARCHAR(255) NOT NULL CHECK (status IN ('pending', 'processing', 'shipped', 'delivered'))
);

-- ENUM values are already stored as strings; direct copy is safe
INSERT INTO orders_new (id, status)
SELECT gen_random_uuid(), status
FROM orders;

CREATE TABLE user_preferences (
  id INT AUTO_INCREMENT PRIMARY KEY,
  permissions SET('read', 'write', 'delete', 'admin')
);

CREATE TABLE user_preferences (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  permissions TEXT  -- Stored as comma-separated: 'read,write,admin'
);

CREATE TABLE records (
  id INT AUTO_INCREMENT PRIMARY KEY,
  data TEXT,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE records (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  data TEXT,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

UPDATE records SET data = 'new_value', updated_at = CURRENT_TIMESTAMP
WHERE id = 'record-uuid';

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Create table with reference column (enforce integrity in application layer)
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id UUID NOT NULL
);

-- Create index for the reference column
CREATE INDEX ASYNC idx_orders_customer ON orders(customer_id);

-- Before INSERT: validate parent exists
SELECT id FROM customers WHERE id = 'customer-uuid';
-- MUST abort INSERT if parent not found

-- Before DELETE of parent: check for dependents
SELECT COUNT(*) as dependent_count FROM orders
WHERE customer_id = 'customer-uuid';
-- MUST abort DELETE if dependent_count > 0