Strategies to avoid constraint creation would fails?

[janpio]

The README states:

If you have a unique index on a foreign key column then there are chance the constraint creation would fail, since mockd doesn't pick up unique value for foriegn key value it picks up random values from the reference table.

For me this manifested in this query being listed in a log file:

ALTER TABLE public.playlisttrack ADD CONSTRAINT pk_playlisttrack PRIMARY KEY (playlistid, trackid);

On checking the data manually, I see this:

So yes indeed, this PK could not be recreated.

---

Are there any strategies to avoid this?

[janpio]

One random idea:

A param that activates a step before re-adding the constraints that deletes entries with problematic values. In this case a query like select *, count(*) as count from playlisttrack p group by playlistid, trackid having count(*) > 1; could show the affected entries.

This would get lose of some of the generated data, but would enable us to re-enable the constraint and add the Primary Key back.

[faisaltheparttimecoder]

I had the same plan before of deleting anything that doesn't match the constraint, but went against it. I guess I will relook into this and seems to be the best solution for composite constraints keys, possibly tomorrow or next week.

[janpio]

Another way would be to update one of the two columns to another value, but this could of course pick another combination that fails, requiring to recheck and again, and again... leading to the requirement of a escape hatch if it fails more than x times. Not sure that is worth the effort.

[faisaltheparttimecoder]

The issue is the same I have described on the README.

we have a primary key being created

ALTER TABLE "public"."PlaylistTrack" ADD CONSTRAINT PK_PlaylistTrack PRIMARY KEY ("PlaylistId", "TrackId");

On top of a foreign key

ALTER TABLE "PlaylistTrack" ADD CONSTRAINT "FK_PlaylistTrackPlaylistId"
  FOREIGN KEY ("PlaylistId") REFERENCES "Playlist" ("PlaylistId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "PlaylistTrack" ADD CONSTRAINT "FK_PlaylistTrackTrackId"
  FOREIGN KEY ("TrackId") REFERENCES "Track" ("TrackId") ON DELETE NO ACTION ON UPDATE NO ACTION;
CREATE INDEX "IFK_PlaylistTrackTrackId" ON "PlaylistTrack" ("TrackId");

The problem is we fix PK first then the FK as mentioned here (https://github.com/pivotal-gss/mock-data/blob/master/constraintsRestore.go#L20) , so we had all the PK sorted, but the FK then pull in data from reference table which can be duplicate, after all that is done we recreate them at one go

https://github.com/pivotal-gss/mock-data/blob/master/constraintsRestore.go#L45

The issue here is I use one column from the composite key to sort it, possibly I guess I need to also check in the second column as well, I will have a look tomorrow.

Or in this case , add more rows while mocking like 50 using ( -r ) so chancing of picking random value is greater.

[faisaltheparttimecoder]

fixed on via #15 and new release is published at https://github.com/pivotal-gss/mock-data/releases/tag/v2.3