Last active
November 22, 2023 03:38
-
-
Save kiramishima/31decf478999e6dd3438e8f4af809b4e to your computer and use it in GitHub Desktop.
Constraints 2
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE SCHEMA `empresa_x`; | |
USE `empresa_x`; | |
CREATE TABLE IF NOT EXISTS `Empleados` ( | |
Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, | |
Nombre VARCHAR(30) NOT NULL CHECK ( Nombre != "" ), | |
ApellidoPaterno VARCHAR(30) NOT NULL CHECK ( ApellidoPaterno != "" ), | |
ApellidoMaterno VARCHAR(30) DEFAULT "X", | |
Sexo CHAR(1) NOT NULL CHECK(sexo IN ("H", "M")), | |
Edad INT NOT NULL CHECK ( Edad >= 18 ), | |
CURP CHAR(18) NOT NULL UNIQUE CHECK ( CURP REGEXP '^[A-Z]{4}+[0-9]{6}+[A-Z]{6}+[0-9]{2}$' AND LENGTH(CURP) = 18), | |
EstadoCivil ENUM("Soltero", "Casado", "Viudo", "Divorciado") NOT NULL CHECK ( EstadoCivil IN ("Soltero", "Casado", "Viudo", "Divorciado") ), | |
Salario FLOAT NOT NULL DEFAULT 10000 CHECK ( Salario BETWEEN 10000 AND 20000), | |
Estatus BIT NOT NULL CHECK ( Estatus IN (0, 1) ) | |
); | |
-- No inserta nulos | |
INSERT INTO Empleados (Nombre, ApellidoPaterno, Sexo, Edad, CURP, EstadoCivil, Salario, Estatus) | |
VALUES (NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL); | |
-- Falla insertar: Edad menor a 18 | |
INSERT INTO Empleados (Nombre, ApellidoPaterno, Sexo, Edad, CURP, EstadoCivil, Salario, Estatus) | |
VALUES ("Zury", "Colin", "M", 15, "ABCDFEGHIJ1234567", "Soltero", 2000, 1); | |
-- Falla insertar: CURP menor a 18 caracteres | |
INSERT INTO Empleados (Nombre, ApellidoPaterno, Sexo, Edad, CURP, EstadoCivil, Salario, Estatus) | |
VALUES ("Zury", "Colin", "M", 18, "ABCDFEGHIJ1234567", "Soltero", 2000, 1); | |
-- Falla insertar CURP no cumple la expresión regular | |
INSERT INTO Empleados (Nombre, ApellidoPaterno, Sexo, Edad, CURP, EstadoCivil, Salario, Estatus) | |
VALUES ("Zury", "Colin", "M", 18, "ABCDFEGHIJ12345678", "Soltero", 2000, 1); | |
-- Falla insertar CURP no cumple la expresión regular | |
INSERT INTO Empleados (Nombre, ApellidoPaterno, Sexo, Edad, CURP, EstadoCivil, Salario, Estatus) | |
VALUES ("Zury", "Colin", "M", 18, "A1CD123456FEGHIJ7B", "Soltero", 15000, 1); | |
-- Falla insertar: Salario no cumple | |
INSERT INTO Empleados (Nombre, ApellidoPaterno, Sexo, Edad, CURP, EstadoCivil, Salario, Estatus) | |
VALUES ("Zury", "Colin", "M", 18, "ABCD123456FEGHIJ78", "Soltero", 2000, 1); | |
-- Falla insertar: Estatus fuera de 0 & 1 | |
INSERT INTO Empleados (Nombre, ApellidoPaterno, Sexo, Edad, CURP, EstadoCivil, Salario, Estatus) | |
VALUES ("Zury", "Colin", "M", 18, "ABCD123456FEGHIJ78", "Soltero", 15000, 2); | |
-- Insert con default OK | |
INSERT INTO Empleados (Nombre, ApellidoPaterno, Sexo, Edad, CURP, EstadoCivil, Salario, Estatus) | |
VALUES ("Zury", "Colin", "M", 18, "ABCD123456FEGHIJ78", "Soltero", 15000, 1); | |
-- Error: CURP Duplicada | |
INSERT INTO Empleados (Nombre, ApellidoPaterno, Sexo, Edad, CURP, EstadoCivil, Salario, Estatus) | |
VALUES ("Zura", "Guzman", "M", 18, "ABCD123456FEGHIJ78", "Soltero", 15000, 1); | |
-- Insert todos los campos OK | |
INSERT INTO Empleados (Nombre, ApellidoPaterno, ApellidoMaterno, Sexo, Edad, CURP, EstadoCivil, Salario, Estatus) | |
VALUES ("Martha", "Orozco", "Arizpe", "M", 34, "MOAR123456FEGHIJ78", "Casado", 18000, 1); | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment