[Bug]: SQL link tables allow duplicate entries in unique collections #414

What type of issue is this?

• Bug report
• Feature request

Prerequisites

• I have written a descriptive issue title
• I have verified that I am running the latest version of the SysML2.NET
• I have searched open and closed issues to ensure it has not already been reported

Description

The 7 stored multi-valued properties are all ordered and unique: Element::ownedRelationship, Element::aliasIds, Relationship::ownedRelatedElement, Relationship::source, Relationship::target, Dependency::client and Dependency::supplier. Their link tables (schema.generated.sql:650-736) are keyed by (project_id, version_id, ordinal) only, so one version can list the same target twice. The link rows can also disagree with the element's stored_json.

Expected: a target appears at most once per collection per version.

Fix:

• Add UNIQUE (project_id, version_id, target_identity) to the six reference link tables, and UNIQUE (project_id, version_id, value) to element_alias_ids.
• Generate the constraint from isUnique in the template.

Steps to Reproduce

1. Insert an element_owned_relationship row for an existing version with an ordinal not yet used and a target_identity already listed: accepted.

System Configuration

• SysML2.NET version: PR [ADD] Sql schema #344 (feat/sql-schema-and-more, d5a90f2), SysML2.NET.CodeGenerator
• Environment (Operating system, version and so on): Windows 11, PostgreSQL 17 (Docker)
• .NET Framework version: .NET 10
• Additional information: uniqueness taken from Resources/KerML_only_xmi.uml