I need a composite key for a database of 10 fields, in the database schools are registered through its cct (which would be the primary key) but I also need to be able to register the same cct but with a different shift ( field that must be related to the cct), any comment is appreciated.
If you want the tuple (cct, turn) to be unrepeatable, you should only define it as the primary key in this way:
CREATE TABLE IF NOT EXISTS 'escuela' (
'cct' INT NOT NULL,
'turno' INT NOT NULL,
/** las demás columnas... */
PRIMARY KEY ('cct', 'turno'))
ENGINE = InnoDB
If the problem is that you already have the created table, then you can edit the primary key in this way:
ALTER TABLE 'escuela'
DROP PRIMARY KEY,
ADD PRIMARY KEY ('cct', 'turno');
So you can have the following validation in your records:
cct | turno | ...
1 | 1 | ... => Si
2 | 1 | ... => Si
1 | 2 | ... => Si
1 | 1 | ... => No (Conflicto con el primer registro ingresado)
2 | 2 | ... => Si