valores únicos en una columna, pero solo únicos para cada key externa asociada

Me disculpo por el título confuso pero realmente no estoy seguro de cómo describir este problema en una oración.

Tengo una database SQLite para una competencia general de propinas. Para aquellos que no están familiarizados con el concepto, la gente "propina" quién creen que ganará en cada partido de cada ronda de una competencia deportiva, y la persona con los consejos más correctos al final de la competencia gana (el premio puede ser dinero, orgullo , etc.) Es muy popular en Australia con la AFL.

La estructura básica es que los usuarios se registren como propietarios, y luego pueden crear tantas competiciones como deseen, y cada competencia está vinculada a ellos con una key externa. Esta es la parte relevante del esquema de la database:

create table owner ( owner_id INTEGER NOT NULL, owner_name VARCHAR NOT NULL, owner_nick VARCHAR UNIQUE NOT NULL, owner_email VARCHAR NOT NULL, owner_password VARCHAR NOT NULL, CONSTRAINT owner_pk PRIMARY KEY (owner_id) ); create table comp ( comp_id INTEGER NOT NULL, comp_owner_id INTEGER NOT NULL, comp_name VARCHAR UNIQUE NOT NULL, CONSTRAINT comp_pk PRIMARY KEY (comp_id), CONSTRAINT comp_owner_fk FOREIGN KEY (comp_owner_id) REFERENCES owner (owner_id) NOT DEFERRABLE ); 

La esencia del problema es esta: quiero imponer una restricción única en el campo comp_name de la tabla comp (como se muestra arriba) porque un solo propietario no debe tener dos o más concursos con el mismo nombre. Sin embargo, es muy posible que dos propietarios diferentes quieran dos competiciones diferentes con el mismo nombre (por ejemplo, dos oficinas que ofrecen competiciones de propina para la misma liga deportiva). ¿Es posible aplicar esto usando SQL, o necesito hacer la aplicación manualmente en otro lugar?

Algunas notas:

  • Estoy usando SQLite 3.6.20, que tiene una correcta aplicación de restricciones de key externa
  • La database nunca se usará en una aplicación de nivel de producción. Es un proyecto privado.
  • La database se está utilizando en una aplicación PHP basada en la web.
  • Si esto no se puede lograr con SQLite, ¿se puede lograr con MySQL?

EDITAR: Siento que debo señalar que soy consciente de que SQLite tiene un soporte de tabla alternativo muy limitado, y borrar la database y empezar de nuevo es una opción.

Simplemente cree una restricción UNIQUE en esos dos campos, de esta manera:

 create table comp ( comp_id INTEGER NOT NULL, comp_owner_id INTEGER NOT NULL, comp_name VARCHAR NOT NULL, CONSTRAINT comp_unq UNIQUE (comp_owner_id, comp_name), CONSTRAINT comp_pk PRIMARY KEY (comp_id), CONSTRAINT comp_owner_fk FOREIGN KEY (comp_owner_id) REFERENCES owner (owner_id) NOT DEFERRABLE ); 

eso debería hacer lo que quieras.

Puede crear una restricción UNICA compuesta con esta syntax:

 create table comp ( comp_id INTEGER NOT NULL, comp_owner_id INTEGER NOT NULL, comp_name VARCHAR, CONSTRAINT comp_pk PRIMARY KEY (comp_id), CONSTRAINT comp_unique UNIQUE (comp_owner_id, comp_name), CONSTRAINT comp_owner_fk FOREIGN KEY (comp_owner_id) REFERENCES owner (owner_id) NOT DEFERRABLE ); 

Si su preocupación es agregar la restricción a la tabla existente, puede hacerlo con:

  CREATE UNIQUE INDEX comp_unique (comp_owner_id, comp_name) ON comp 

Además, puede considerar perder la columna owner_id y simplemente usar owner_name como key principal en la tabla de propietarios. Del mismo modo, es posible que no necesite el comp_id ya que tiene otra key candidata en esa tabla.