Re: Question about modeling

From: <guntermann_at_verizon.net>
Date: 25 Jan 2005 13:16:56 -0800
Message-ID: <1106687816.186483.228680_at_c13g2000cwb.googlegroups.com>

The constraint you mention can be structurally enforced with the use of superkeys as alternative keys.

The following is a possibility in abbreviated notation. There is another, possibly more efficient way to model it as well, but I'll forego that for now. I am also explicitly not addressing referential integrity rules (ON UPDATE, DELETE, etc.).

Let me know if you need clarification.
CLIENTS(cl_id PK);

DEPTS(d_id PK, cl_id FK references CLIENTS, AK (d_id, cl_Id));

CONTRACTS(cn_id PK, cl_id FK references CLIENTS, AK(cn_id, cl_id));

DEPTS_CONTRACTS(d_id, cn_id, cl_id, PK (d_pd, cn_id), FK (d_id, cl_id) REFERENCES DEPTS, FK (cn_id, cl_id) REFERENCES CONTRACTS));

• Example Implementation Follows** SQL> _at_client_contracts SQL> SET ECHO On SQL> SQL> SQL> DROP TABLE department_contracts;

Table dropped.

SQL> DROP TABLE depts;

Table dropped.

SQL> DROP TABLE contracts;

Table dropped.

SQL> DROP TABLE clients;

Table dropped.

SQL>
SQL>
SQL> CREATE TABLE clients (client_id CHAR(2) PRIMARY KEY);

Table created.

SQL>
SQL> CREATE TABLE contracts

2  (	contract_id CHAR(2) PRIMARY KEY,
3  	client_id CHAR(2)  NOT NULL
4  	  REFERENCES clients (client_id),
5  	  CONSTRAINT uq_contractclient_contracts
6  	     UNIQUE (contract_id, client_id)

7 );

Table created.

SQL>
SQL> CREATE TABLE depts
2 (

3  	department_id  CHAR(2) PRIMARY KEY,
4  	client_id CHAR(2) NOT NULL
5  	  REFERENCES CLIENTS (client_id),
6  	CONSTRAINT uq_deptsclients
7  	   UNIQUE (department_id, client_id)

8 );

Table created.

SQL>
SQL> CREATE TABLE department_contracts

2  (	department_id  CHAR(2),
3  	contract_id    CHAR(2),
4  	client_id      CHAR(2) NOT NULL,
5  	CONSTRAINT pk_departmentcontracts
6  	    PRIMARY KEY (department_id, contract_id),
7  	CONSTRAINT fk_departcontracts
8  	  FOREIGN KEY (department_id, client_id)
9  	    REFERENCES depts (department_id, client_id),
10  	CONSTRAINT fk_contractclient
11  	    FOREIGN KEY (contract_id, client_id)
12  	    REFERENCES contracts (contract_id, client_id)

13 );

Table created.

SQL>
SQL> -- populate client table
SQL> INSERT INTO clients (client_id)

2 VALUES ('C1'); 1 row created.

SQL>
SQL> INSERT INTO clients (client_id)
2 VALUES ('C2'); 1 row created.

SQL>
SQL> INSERT INTO clients (client_id)
2 VALUES ('C3'); 1 row created.

SQL>
SQL> -- populate depts table
SQL> INSERT INTO depts (department_id, client_id)

2 VALUES ('D1', 'C1'); 1 row created.

SQL>
SQL> INSERT INTO depts (department_id, client_id) 2 VALUES ('D2', 'C2'); 1 row created.

SQL>
SQL> INSERT INTO depts (department_id, client_id) 2 VALUES ('D3', 'C2'); 1 row created.

SQL>
SQL> INSERT INTO depts (department_id, client_id) 2 VALUES ('D4', 'C3'); 1 row created.

SQL>
SQL> INSERT INTO depts (department_id, client_id) 2 VALUES ('D5', 'C1'); 1 row created.

SQL>
SQL> -- populate contracts table
SQL> INSERT INTO contracts (contract_id, client_id)

2 VALUES ('CA', 'C1'); 1 row created.

SQL>
SQL> INSERT INTO contracts (contract_id, client_id) 2 VALUES ('CB', 'C1'); 1 row created.

SQL>
SQL> INSERT INTO contracts (contract_id, client_id) 2 VALUES ('CC', 'C1'); 1 row created.

SQL>
SQL> INSERT INTO contracts (contract_id, client_id) 2 VALUES ('CD', 'C2'); 1 row created.

SQL>
SQL> INSERT INTO contracts (contract_id, client_id) 2 VALUES ('CE', 'C3'); 1 row created.

SQL>
SQL> -- show clients data
SQL> SELECT *

2 FROM clients;

CL

--

C1

C2

C3


SQL>
SQL> -- show depts data
SQL> SELECT *
2   FROM depts;

DE CL

-- --

D1 C1

D2 C2

D3 C2

D4 C3

D5 C1


SQL>
SQL> -- show contracts data
SQL> SELECT *
2   FROM contracts;

CO CL

-- --

CA C1

CB C1

CC C1

CD C2

CE C3


SQL>
SQL> -- populate department_contracts data
SQL> -- D1 and CA belong to C1 client - good.
SQL> INSERT INTO department_contracts (department_id, contract_id,
client_id)
2    VALUES ('D1', 'CA', 'C1');

1 row created.

SQL>
SQL> -- D1 and CB belong to C1 client - good
SQL> INSERT INTO department_contracts (department_id, contract_id,
client_id)
2    VALUES ('D1', 'CB', 'C1');

1 row created.

SQL>
SQL> -- D1 and CC belong to C1 client - good
SQL> INSERT INTO department_contracts (department_id, contract_id,
client_id)
2    VALUES ('D1', 'CC', 'C1');

1 row created.

SQL>
SQL> -- D1 and CC belongs to C1 client, but client does not match.
SQL> INSERT INTO department_contracts (department_id, contract_id,
client_id)
2    VALUES ('D1', 'CC', 'C2');
INSERT INTO department_contracts (department_id, contract_id,
client_id)


*

ERROR at line 1:
ORA-00001: unique constraint (GUNTERMANND.PK_DEPARTMENTCONTRACTS)
violated


SQL>
SQL> -- D1 belongs to C1 and CD belongs to C2.	Does not work.
SQL> INSERT INTO department_contracts (department_id, contract_id,
client_id)
2    VALUES ('D1', 'CD', 'C1');
INSERT INTO department_contracts (department_id, contract_id,
client_id)


*

ERROR at line 1:
ORA-02291: integrity constraint (GUNTERMANND.FK_CONTRACTCLIENT)
violated -
parent key not found


SQL>
SQL> -- D1 belongs to C1 and CD belongs to C2.	C3 does not work.
SQL> INSERT INTO department_contracts (department_id, contract_id,
client_id)
2    VALUES ('D1', 'CD', 'C3');
INSERT INTO department_contracts (department_id, contract_id,
client_id)


*

ERROR at line 1:
ORA-02291: integrity constraint (GUNTERMANND.FK_CONTRACTCLIENT)
violated -
parent key not found


SQL>
SQL> -- D2 belongs to C2, CD belongs to C2 -- good.
SQL> INSERT INTO department_contracts (department_id, contract_id,
client_id)
2    VALUES ('D2', 'CD', 'C2');

1 row created.

SQL>
SQL> -- show depart_contracts data
SQL> SELECT *
2   FROM department_contracts;

DE CO CL

-- -- --

D1 CA C1

D1 CB C1

D1 CC C1

D2 CD C2
SQL> 
SQL> spool off

HTH,

- Dan

Received on Tue Jan 25 2005 - 22:16:56 CET