SQL中数据约束与操作的深入解析

### SQL 中数据约束与操作的深入解析 #### 1. 非空约束（NOT NULL Constraint） 非空约束用于确保表中的某列不包含空值。当定义了非空约束并尝试向表中插入包含空值的行时，这些行将被拒绝，表会恢复到操作前的状态。 **示例代码**： ```sql PROC SQL; INSERT INTO CUSTOMER_CITY VALUES(101,'La Mesa Computer Land') VALUES(1301,'Spring Valley Byte Center') VALUES(1801,''); QUIT; ``` **SAS 日志结果**： ```plaintext PROC SQL; INSERT INTO CUSTOMER_CITY VALUES(101,'La Mesa Computer Land') VALUES(1301,'Spring Valley Byte Center') VALUES(1801,''); ERROR: Add/Update failed for data set WORK.CUSTOMER_CITY because data value(s) do not comply with integrity constraint _NM0001_. NOTE: This insert failed while attempting to add data from VALUES clause 3 to the data set. NOTE: Deleting the successful inserts before error noted above to restore table to a consistent state. QUIT; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used: real time 0.02 seconds cpu time 0.00 seconds ``` 此外，也可以使用 `ALTER TABLE` 语句为已有表的列添加非空约束，但前提是该列不能包含空值。 **示例代码**： ```sql PROC SQL; ALTER TABLE CUSTOMERS ADD CONSTRAINT NOT_NULL_CUSTCITY NOT NULL(CUSTCITY); QUIT; ``` **SAS 日志结果**： ```plaintext PROC SQL; ALTER TABLE CUSTOMERS ADD CONSTRAINT NOT_NULL_CUSTCITY NOT NULL(CUSTCITY); ERROR: Integrity constraint NOT_NULL_CUSTCITY was rejected because 1 observations failed the constraint. QUIT; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used: real time 0.01 seconds cpu time 0.00 seconds ``` #### 2. 唯一约束（UNIQUE Constraint） 唯一约束用于防止表中某列出现重复值。在创建表时，可以使用 `CREATE TABLE` 语句为不希望出现重复数据的列添加唯一约束。 **示例代码**： ```sql PROC SQL; CREATE TABLE CUSTOMER_CITY (CUSTNUM NUM UNIQUE, CUSTCITY CHAR(20)); QUIT; ``` 当定义了唯一约束并尝试插入重复值时，这些行将被拒绝，表会恢复到操作前的状态。 **示例代码**： ```sql PROC SQL; INSERT INTO CUSTOMER_CITY VALUES(101,'La Mesa Computer Land') VALUES(1301,'Spring Valley Byte Center') VALUES(1301,'Chula Vista Networks'); QUIT; ``` **SAS 日志结果**： ```plaintext PROC SQL; INSERT INTO CUSTOMER_CITY VALUES(101,'La Mesa Computer Land') VALUES(1301,'Spring Valley Byte Center') VALUES(1301,'Chula Vista Networks'); ERROR: Add/Update failed for data set WORK.CUSTOMER_CITY because data value(s) do not comply with integrity constraint _UN0001_. NOTE: This insert failed while attempting to add data from VALUES clause 3 to the data set. NOTE: Deleting the successful inserts before error noted above to restore table to a consistent state. QUIT; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used: real time 1.12 seconds cpu time 0.06 seconds ``` #### 3. 检查约束（CHECK Constraint） 检查约束用于在向表中插入数据之前，验证数据值是否符合指定的条件，例如值列表、最小值和最大值、值范围等。可以使用 `CREATE TABLE` 或 `ALTER TABLE` 语句为需要数据验证的列添加检查约束。 **示例代码**： ```sql PROC SQL; ALTER TABLE PRODUCTS ADD CONSTRAINT CHECK_PRODUCT_TYPE CHECK (PRODTYPE IN ('Laptop', 'Phone', 'Software', 'Workstation')); QUIT; ``` 在插入数据时，每行数据必须满足检查约束的条件，否则插入操作将失败，表会恢复到操作前的状态。 **示例代码**： ```sql PROC SQL; INSERT INTO PRODUCTS VALUES(5005,'Internet Software',500,'Software',99.) VALUES(1701,'Elite Laptop',170,'Laptop',3900.) VALUES(2103,'Digital Cell Phone',210,'Fone',199.); QUIT; ``` **SAS 日志结果**： ```pl ```