This message was deleted.

Hello Ryan. I was trying to understand this issue as our organization is looking into DBT, but I am not sure I fully understand the problem from the Redshift side. I can create Redshift tables without PK/FK constraints, and then add them after the fact.

create table foo (id bigint not null);
create table bar (id bigint not null, foo_id bigint);

insert into foo values (1);
insert into foo values (2);
insert into foo values (3);

insert into bar values (1, 1);
insert into bar values (2, 1);
insert into bar values (3, 1);


alter table foo add primary key(id);
alter table bar add primary key(id);
alter table bar add foreign key(foo_id) references foo(id);

This works fine natively in Redshift. So then I wonder, is this more of a DBT limitation? Or am I miss understanding the problem you are describing?

Hello @dazzling-wolf-90175 🙂 Your code definitely works as written, although if you omit the "not null" constraints when creating the tables, it produces the same error I'm now getting: you can't make a nullable column the primary key. Unfortunately, the "not null" constraint is not something you can tell DBT to apply when it initializes a table. Like I said, there are potential workarounds, but they all kind of suck. (side note, I do hope y'all end up going forward with DBT...it's been a game changer for us)

Thanks for the feedback Ryan. I like to understand these gotchas whenever I can, really helps out when we start implementing.

Hey @adamant-forest-45768 this is a reasonable request, just to make sure that I’m understanding the scope correctly, you’d have the ability in the UI to specify that a column is a primary key, as well as the foreign keys that are associated to the primary key?

Hey @elegant-house-93198 thanks for getting back! Yes, that's correct. I'd love to be able to: 1. Specify primary/foreign keys from within the UI 2. Always have the option to display the columns in each table (when viewing the ERD). Sonam recently told me in another thread: "[Secoda] will hide that toggle, if the columns on the table do not detect any references to the Table Nodes currently on the Lineage View. This is to cut down on the clutter." Personally, I'd like to have that option always available.