CODEWITHSUNDEEP
sqlpostgresql
user1475412
user1475412

Reputation: 1799

What is the best way to enforce constraints across tables?

I often find myself running into situations like this one (which is contrived but illustrative of the problem):

CREATE TABLE customer (
  id   SERIAL PRIMARY KEY,
  type TEXT
  -- other columns...
);

CREATE TABLE product_order (
  id          SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customer (id),
  type        TEXT REFERENCES customer (type), -- not actually legitimate
  -- other columns...
  CHECK (type = 'business')
);

Of course, the foreign key constraint on product_order.type doesn't work because customer.type is not UNIQUE or a primary key (and I can't use a CHECK CONSTRAINT on a column that only exists in another table). However, I would only like product_order entries for type = 'business' customers.

I could make customer.id and customer.type a composite primary key, but then any other tables that want to reference just customer.id must also reference customer.type unnecessarily.

What's the best approach in this situation?

EDIT: Forgot the foreign key constraint product_order.customer_id!

Upvotes: 1

Views: 184

Answers (4)

user330315
user330315

Reputation:

If you create a unique constraint on the customer.type you can reference it from the product_order table:

CREATE TABLE customer (
  id   SERIAL PRIMARY KEY,
  type TEXT,
  -- other columns...
  constraint unique_cust_type unique (id, type) -- this makes the combination id/type "referencable"
);


CREATE TABLE product_order 
(
  id          SERIAL PRIMARY KEY,
  customer_id INTEGER,
  type        TEXT default 'business',
  CHECK (type = 'business'),
  foreign key (customer_id, type) references customer (id, type)
);

Upvotes: 2

rurouni88
rurouni88

Reputation: 1173

You could make a lookup table for types, and use the FKEY relationship to enforce

CREATE TABLE type (
  id   integer, PRIMARY KEY,
  name TEXT
);

CREATE TABLE customer (
  id   SERIAL PRIMARY KEY,
  type_id INTEGER, NOT NULL
  -- other columns...
  FOREIGN KEY (type_id) REFERENCES type(id)
);

CREATE TABLE product_order (
  id   SERIAL PRIMARY KEY,
  type_id INTEGER, NOT NULL
  -- other columns...
  FOREIGN KEY (type_id) REFERENCES type(id)
);

Upvotes: 2

Tim3880
Tim3880

Reputation: 2583

I think you need another type for the type. This sample is in MS SQL format:

    CREATE TABLE TYPES (ctype varchar(10) NOT NULL PRIMARY KEY,
         name varchar(50) not null
    );

    CREATE TABLE product_order (
           id   SERIAL PRIMARY KEY,
           type varchar(10) NOT NULL REFERENCE TYPES (ctype) , 
           ....
    );

So your product_order must have types defined in Types table. In your sample, you have only one entry 'business' but you can add as many as you want.

Upvotes: 0

maxymoo
maxymoo

Reputation: 36555

You could access product_order as a view:

create view product_order_vw
as
select po.* 
from product_order po
join customer c
on c.customerid = po.customerid
where c.type = 'business'

Upvotes: 0

Related Questions