Search code examples
constraintsuniquesubclasssubtype

Oracle SQL classify Subtype

CREATE TABLE Customer
(
CustomerID NUMBER NOT NULL,
AccountID NUMBER(10) NOT NULL,
CustomerType VARCHAR(10) NOT NULL,
CustomerStatus VARCHAR(10) NOT NULL,
CONSTRAINT customer_pk PRIMARY KEY (CustomerID),
CONSTRAINT check_customer_status CHECK(CustomerStatus IN ('Ineligible', 'Eligible')),
CONSTRAINT check_customer_type CHECK(CustomerType IN ('NonResident', 'Residential'))
);

Alter table CUSTOMER add CONSTRAINT res_customer_type UNIQUE (CustomerID, CustomerType)

CREATE TABLE nonresidential
(
CustomerID NUMBER NOT NULL,
CustomerType VARCHAR(10) NOT NULL,
BusinessName VARCHAR(10) NOT NULL,
ABNNumber NUMBER(10),
Email VARCHAR(35),
CONSTRAINT pk_nrtable PRIMARY KEY (CustomerID),
CONSTRAINT nonres_type CHECK (CustomerType = 'nonresident'),
CONSTRAINT res_customer_type UNIQUE (CustomerID, CustomerType) REFERENCES Customer(CustomerID, CustomerType)
);

The last line keeps coming up as missing parenthesis (around the references line along the unique constraint). I'd like to specify the unique constraint to create a subtype table under customer.

P.S I'm using oracle sql, thanks

Solution

  • You use the Unique keyword where you should use the Foreign key keyword.

    CONSTRAINT res_customer_type UNIQUE (CustomerID, CustomerType) REFERENCES Customer(CustomerID, CustomerType)

    should be

    CONSTRAINT res_customer_type FOREIGN KEY (CustomerID, CustomerType) REFERENCES Customer(CustomerID, CustomerType)