两个相互引用的postgresql表

-- 创建 users 表
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

-- 创建 orders 表
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 插入用户数据
INSERT INTO users (username) VALUES ('Alice');
INSERT INTO users (username) VALUES ('Bob');

-- 插入订单数据
INSERT INTO orders (user_id, order_date) VALUES (1, '2023-01-01'); -- 成功
INSERT INTO orders (user_id, order_date) VALUES (3, '2023-01-02'); -- 失败，因为 user_id 3 不存在

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

-- 删除用户前先删除其订单
DELETE FROM orders WHERE user_id = 1;
DELETE FROM users WHERE user_id = 1;