CODEWITHSUNDEEP
sqlpostgresqlforeign-keys
Simon Ludwig
Simon Ludwig

Reputation: 1804

SQL - Create Foreign Key on multiple columns with different count of columns

I'm trying to create a foreign key on one column of a table to point to two columns of another table, but I seem to get an error.

Is this possible:

ALTER TABLE table_a add CONSTRAINT table_a_table_b FOREIGN KEY
(table_a.id,false) REFERENCES table_b(table_b.id,some_boolean);

It should not be allowed that table_a reference to an entity in table_b where 'some_boolean' is true.

Upvotes: 1

Views: 6189

Answers (2)

Simon Ludwig
Simon Ludwig

Reputation: 1804

I've found a solution by myself, but it seems that this way is also not 'clean':

I've created two CHECK CONSTRAINT on both tables.

CREATE FUNCTION isSomeBooleanSet(BIGINT) RETURNS BOOLEAN AS 
'select some_boolean from table_b where id = $1'
LANGUAGE SQL IMMUTABLE
RETURNS NULL ON NULL INPUT;

ALTER TABLE table_a ADD CONSTRAINT
some_boolean_true_is_not_allowed CHECK (isSomeBooleanSet(table_b_id)=false);

CREATE OR REPLACE FUNCTION tableAReferenceToInvalidTableBEntity(BIGINT, BOOLEAN) 
RETURNS BOOLEAN
AS 'select count(*)>1 from table_a inner join table_b on table_b.id = table_a.table_b_id where table_a.table_b_id = $1 and $2 = true;'
LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT;

ALTER TABLE table_b add CONSTRAINT table_a_cannot_realte_to_table_b_some_boolean_set CHECK (tableAReferenceToInvalidTableBEntity(id,some_boolean)=false);

Upvotes: 2

John D
John D

Reputation: 1637

One way would be to add a dummy column some_bool to table_a with a default value of false, then make your FK constraint reference both columns:

create table table_a ( id varchar, some_bool bool default false);
create table table_b ( id varchar, some_bool bool);

alter table table_b add constraint table_b_unique unique( id, some_bool);
ALTER TABLE table_a add CONSTRAINT table_a_table_b 
FOREIGN KEY (id,some_bool) 
REFERENCES table_b(id,some_bool);

Upvotes: 3

Related Questions