sql - 具有外键的 Sybase 约束和针对外部表列值的条件检查

Question

是否可以在 Sybase 上定义一个约束，要求一个列作为外键，并且还满足基于外列值的条件，例如在下面的示例表中，是否可以在“product”表上创建一个约束，使得“product.code 是 brand.code 的外键，它具有 valid=1”？

CREATE TABLE brand (
    code        char(8)       NOT NULL,
    valid       int           NOT NULL,
    rowid       numeric(10,0) IDENTITY,
    CONSTRAINT brand_pk PRIMARY KEY (code),
    CONSTRAINT valid_check CHECK (valid IN (0,1))
)

CREATE TABLE product (
    code        char(8)       NOT NULL,
    CONSTRAINT product_pk PRIMARY KEY (code)
)

Answer 1

我认为最好稍微改变一下结构。

CREATE TABLE brand (
    code        char(8)       NOT NULL,
    valid       int           NOT NULL,
    rowid       numeric(10,0) IDENTITY,
    CONSTRAINT brand_pk PRIMARY KEY (code),

    -- The following UNIQUE constraint lets the pair of values be the target of 
    -- a foreign key reference.
    CONSTRAINT brand_is_valid UNIQUE (code, valid),

    CONSTRAINT valid_check CHECK (valid IN (0,1))
);

CREATE TABLE product (
    code        char(8)       NOT NULL,
    valid       int           NOT NULL,

    -- The column "code" is a PK in the referenced table, so this still works. It's 
    -- a 1:0 or 1:1 relationship.
    CONSTRAINT product_pk PRIMARY KEY (code),

    -- The next constraint requires a unique constraint on the pair of 
    -- columns in the table "brand".  By itself, it references every row
    -- in "brand". That's too many rows.
    CONSTRAINT product_fk FOREIGN KEY (code, valid) 
                          REFERENCES brand (code, valid),

    -- But this constraint restricts the foreign key references to only those 
    -- rows that have valid = 1 in the table "brand".
    CHECK (valid = 1)
);