在 SQL 中,您可以使用 ALTER TABLE
语句添加新列,并同时为该列定义约束。以下是几种常见的带约束添加新列的方法:
ALTER TABLE 表名
ADD 列名 数据类型 [约束];
ALTER TABLE employees
ADD email VARCHAR(100) NOT NULL;
ALTER TABLE employees
ADD employee_code VARCHAR(10) UNIQUE;
ALTER TABLE employees
ADD employee_id INT PRIMARY KEY;
ALTER TABLE orders
ADD customer_id INT,
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);
ALTER TABLE products
ADD price DECIMAL(10,2) CHECK (price > 0);
ALTER TABLE orders
ADD order_date DATE DEFAULT CURRENT_DATE;
您也可以一次添加多个带约束的列:
ALTER TABLE employees
ADD (
hire_date DATE NOT NULL,
salary DECIMAL(10,2) CHECK (salary > 0),
department_id INT,
CONSTRAINT fk_department
FOREIGN KEY (department_id) REFERENCES departments(id)
);
ALTER TABLE employees
ADD COLUMN phone VARCHAR(15) NOT NULL AFTER last_name;
ALTER TABLE employees
ADD COLUMN phone VARCHAR(15) NOT NULL;
ALTER TABLE employees
ADD phone VARCHAR(15) NOT NULL
CONSTRAINT DF_employees_phone DEFAULT 'N/A';
ALTER TABLE employees
ADD (phone VARCHAR2(15) NOT NULL);
根据您使用的具体数据库系统,可能需要调整语法细节。