请求为CUSTOMER表添加Check Constraint：客户需年满18岁

Got it, let's work through this together. You've got an existing CREATE TABLE statement for your CUSTOMER table, and you need to add a check constraint to make sure every customer is at least 18 years old. First, let's fix a tiny syntax error in your original code, then add the required constraint properly.

先修正原语句的语法问题 #

Your original definition for Number_adopted has incorrect syntax: "Number_adopted" (number) (1) isn't valid in Oracle. It should be "Number_adopted" NUMBER(1) instead.

添加年龄校验的CHECK约束 #

To enforce that customers are 18+, we can use Oracle's ADD_MONTHS function to check if adding 18 years to the birth date falls on or before the current date. This approach avoids edge cases with leap years or partial months that can come from calculating raw age via MONTHS_BETWEEN.

完整的CREATE TABLE语句 #

CREATE TABLE "CUSTOMER" (
    "CUS_ID" VARCHAR2(9),
    "NAME" VARCHAR2(30),
    "EMAIL" VARCHAR2(30),
    "BIRTH_DATE" DATE,
    "Mobile_number" NUMBER(10),
    "Number_adopted" NUMBER(1),
    CONSTRAINT "CUSTOMER_PK" PRIMARY KEY ("CUS_ID") ENABLE,
    CONSTRAINT "Email_uni" UNIQUE ("EMAIL") ENABLE,
    CONSTRAINT "Mobile_uni" UNIQUE ("Mobile_number") ENABLE,
    CONSTRAINT "adopted_range_ck" CHECK ("Number_adopted" >= 1 AND "Number_adopted" <= 4) ENABLE,
    CONSTRAINT "age_min_18_ck" CHECK (ADD_MONTHS("BIRTH_DATE", 18 * 12) <= SYSDATE) ENABLE
);

关键说明 #

• 约束逻辑：ADD_MONTHS("BIRTH_DATE", 18*12) calculates the date when the customer turns 18. Checking if this date is <= SYSDATE ensures they're already 18 or older.
• 约束命名：I named the new constraint "age_min_18_ck" to make its purpose clear—this makes debugging and maintenance easier later on.
• 语法修正：Fixed the Number_adopted column definition to follow Oracle's standard data type syntax.

内容的提问来源于stack exchange，提问作者Fido Dido