A煤n no tienes acceso a esta clase

Crea una cuenta y contin煤a viendo este curso

Curso de SQL y MySQL

Curso de SQL y MySQL

Alberto Alcocer

Alberto Alcocer

Tipos de columnas usando / Creaci贸n de la tabla clientes

10/24
Recursos

Aportes 368

Preguntas 50

Ordenar por:

驴Quieres ver m谩s aportes, preguntas y respuestas de la comunidad? Crea una cuenta o inicia sesi贸n.

  1. EL no colocar AUTO_INCREMENT a la columna que es PRIMARY KEY simplemente vuelve el proceso de asignar id a una forma manual o se puede asignar desde otra capa de negocios.
    UNIQUE, la columna que tenga el constraint unique garantiza que el valor que se guarda en esa columna sea 煤nico

  2. -. TIMESTAMP
    Est谩 basado en el n煤mero epoch que es el 1 enero de 1970 hasta la fecha y es donde se determina el inicio de las computadoras y es un n煤mero entero que se guarda en segundos y permite hacer operaciones sobre el.

  3. -. DATETIME
    Este tipo de datos puede guardar cualquier valor de tipo fecha sin restricci贸n. Incluso anterior a nuestra era. es por eso que las fechas de nacimiento de usuarios debe utilizar este valor para garantizar que podemos registrarlos con la fecha adecuada.

  4. TIMESTAMP vs DATETIME: hay que resaltar que un, 1.TIMESTAMP 鈥淣O PUEDE HACER TODO LO DE DATETIME pero DATETIME S脥 PUEDE HACERLO DE UN TIMESTAMP鈥, 2.DATETIME no est谩 guardado en segundos y no es tan eficiente para hacer c谩lculos.

  5. -.Active
    Es buena pr谩ctica no eliminar registros de una bases de datos es por ello que se crea una columna como active que es un valor booleano dicho valor sirve para para decir si el registro est谩 activo o no.

  6. -. created_ad y updated_ad
    Es buena pr谩ctica tener una columna que permite saber el momento exacto en el que se crea un registro o se actualiza. Este tipo de dato se comporta m谩s como una meta-informaci贸n y nos puede ayudar por ejemplo a cu谩ntos usuarios fueron creados en una fecha en espec铆fico, saber cuando una tupla se actualiz贸

  7. created_ad Es una columna de buena pr谩ctica que permite saber cuando se cre贸 un registro. Est谩 utilizar谩 un conjunto de propiedades llamada entre ella se colocar谩 DEFAULT CURRENT_TIMESTAMP . Cuando se realiza un insert s铆 el valor de esta columna viene vac铆o colocar谩 en la tupla el valor de la fecha en que se cre贸 de manera autom谩tica .

 `created_at`    TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP,
  1. update_ad Es una columna de buena pr谩ctica que permite saber cuando un registro se actualiza se puede colocar ON UPDATE CURRENT_TIMESTAMP esto permite dejar un registro de la fecha actual cuando ocurre una actualizaci贸n
`updated_at`    TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP 
                       ON UPDATE CURRENT_TIMESTAMP, 
/*Tabla clientes*/
CREATE TABLE `clients` (
  `client_id`       int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name`           varchar(50) DEFAULT NULL,
  `email`           varchar(100) NOT NULL,
  `birthdate`       date DEFAULT NULL,
  `gender` enum('M','F') DEFAULT NULL,
  `active`           TINYINT(1) NOT NULL DEFAULT 1,
 `created_at`    TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at`    TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP 
                       ON UPDATE CURRENT_TIMESTAMP, 
  PRIMARY KEY (`client_id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;
/*Tabla transactions*/
CREATE TABLE `transactions` (
  `transaction_id`      int(10) unsigned NOT NULL AUTO_INCREMENT,
  `book_id`             int(10) unsigned NOT NULL,
  `client_id`           int(10) unsigned NOT NULL,
  `type` enum('lend','sell') NOT NULL,
  `created_at`          timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified_at`         timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `finished`            tinyint(1) NOT NULL DEFAULT '0',
  `active`        TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`transaction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Les recomiendo esta pagina, que sirve para hacer inspeccion del codigo.

https://www.eversql.com/sql-syntax-check-validator/

Hola amigos de platzi aqu铆 les dejo un aporte para que aprendan a como crear un usuario,listarlo y eliminarlo sigan los pasos tal como esta en la imagen espero les sirva.

CREATE TABLE operations(
    operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
    book_id INTEGER UNSIGNED NOT NULL, 
    client_id INTEGER UNSIGNED NOT NULL,
    type ENUM('V','P', 'D' ) NOT NULL COMMENT 'V: Vendido -P:Prestado -D: Devuelto',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    finished TINYINT(1) NOT NULL
    
);```

Ac谩 como lo 鈥渟olucion茅鈥, nota de color interesante para la Base de Datos no es lo mismo usar ( ` ) en los nombres que usar ( 鈥 ), lo aprend铆 a las malas.

CREATE TABLE `operation` (
    `operation_id` int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `book_id` INT(10) unsigned NOT NULL,
    `client_id` INT(10) unsigned NOT NULL,
    `operation_type` enum('PRESTAMO', 'DEVOLUCI脫N', 'VENTA'),
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `finished` TINYINT(1) NOT NULL,
    UNIQUE KEY `operation_id` (`operation_id`)
);```

En Colombia se le suele decir a las tuplas -> registros y a las columnas -> campos.

CREATE TABLE clients (
    client_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    birthdate DATETIME,
    gender ENUM('M', 'F', 'ND') NOT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
        ON UPDATE CURRENT_TIMESTAMP
    
);```

Algo tan simple como DATETIME y TIMESTAMP puede explicarse con un esquema de forma muy simple, no hay necesidad de tanta palabrer铆a.

Me encanta este profe!

Resumen de la clase:
Tips:
Auto Increment es una buena pr谩ctica
Siempre crear columna created_at cuando se tiene una base de datos catalogo - operaci贸n.
Ninguna tupla se borra jam谩s, se usa columna active con bandera booleana

Tipo de Datos:
UNIQUE: valor de llave donde el valor de la columna es 煤nico.
TIMESTAMP: basado en EPOC, guarda enteros el valor de 1970 a la fecha.
DATETIME: ideal para cunpleanos, cualquier valor, no tan eficiente al calcular.
ENUM: enumeraci贸n de datos, solo se recibe una de las opciones.

C贸digo Reto:

CREATE TABLE IF NOT EXISTS operations (
    operation_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    book_id INT UNSIGNED,
    client_id INT UNSIGNED,
    `type` ENUM('borrowed', 'sold', 'returned') NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    finished TINYINT(1) NOT NULL
);

created_ad y updated_ad
Es buena pr谩ctica tener una columna que permite saber el momento exacto en el que se crea un registro o se actualiza. Este tipo de dato se comporta m谩s como una meta-informaci贸n y nos puede ayudar por ejemplo a cu谩ntos usuarios fueron creados en una fecha en espec铆fico, saber cuando una tupla se actualiz贸


CREATE TABLE IF NOT EXISTS authors (
    author_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    nationality VARCHAR(3)
);

//MOSTRAR LAS COLUMNAS DE LAS tablas
describe authors; 
desc books;
// MOSTRAR COMENTARIOS
show full comuns from books

//nombre de una columa y reservarla con comillas `integer_column `
`year`

CREATE TABLE clients(
    id_client INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    birthdate DATETIME,
    gender ENUM('M','F', 'ND') NOT NULL,
    
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
********NOTA*************
DATETIME cualquier fecha como 1800-01-01 00:00:00
TIMESTAMP es un valor desde 1970 hasta ahora, global. Para calculos ('yyyy-mm-dd hh:mm:ss')
The DATETIME type is used when you need values that contain both date and time information. MySQL 
retrieves 
and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is 
'1000-01-01 00:00:00' to '9999-12-31 23:59:59'.


The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. 
It has varying properties, depending on the MySQL version and the SQL mode the server is running in.
...
ENUM ( '','','') 
An ENUM is a string object with a value chosen from a list of permitted values 
that are enumerated explicitly in the column specification at table creation time.
********END NOTA**********```

Ahi les dejo el significado de los tipos de datos con fecha o tiempo
Tipo Fecha
DATE : Fecha con formato AAAA-MM-DD
TIME : Hora con formato hh:mm:ss
DATETIME : Fecha y hora con formato AAAA-MM-DD hh:mm:ss
TIMESTAMP : Lapso de tiempo con formato AAAA-MM-DD-hh-mm-ss.
YEAR : A帽o con formato AAAA

CREATE TABLE IF NOT EXISTS operations (
	operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	book_id INTEGER UNSIGNED,
	client_id INTEGER UNSIGNED,
	type ENUM("Vendido", "Prestado", "Devuelto"),
	created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
	ON UPDATE CURRENT_TIMESTAMP,
	finshed TINYINT(1) NOT NULL DEFAULT 1
	);



Dato curioso: El Timestamp llega hasta 2038-01-19 03:14:07 porque hasta ah铆 llega el contador de ((2^31) - 1) bits que tienen los sistemas de 32 bits.

Todos los programas que usen esta medida de 32 bits de tiempo volver谩n (Inform谩ticamente) al a帽o 1900, cosa que posiblemente cause muchos problemas si no se previene antes de esa fecha.

El nombre que se le dio a este problema es Y2K38 (Year 2038), aunque tambi茅n hay unos muy parecidos llamados Y2K36 y Y2K que siguen la misma l贸gica.

Buen D铆a

Quisiera saber a que se debe este error鈥

mysql> CREATE TABLE clients (
-> client_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(50) NOT NULL,
-> email VARCHAR(100) NOT NULL UNIQUE,
-> birthdate DATETIME,
-> gender ENUM(鈥楳鈥, 鈥楩鈥, 鈥楴D鈥) NOT NULL,
-> active TINYINT(1) NOT NULL DEFAULT 1,
-> created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
-> updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
-> ON UPDATE CURRENT_TIMESTAMP
-> );
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
mysql>

Seg煤n veo la sintaxis es la misma que usa el profesor y no creo que el sistema operativo ponga problemas estoy usando un Windows 10, debo hacer alguna conf adicional.

Quedo atento.

Reto cumplido!

![](

CREATE TABLE clients (
-> client_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(50) NOT NULL,
-> email VARCHAR(100) NOT NULL UNIQUE,
-> birthdate DATETIME,
-> gender ENUM( 鈥楳鈥 , 鈥楩鈥 , 鈥楴D鈥) NOT NULL,
-> active TINYINT(1) NOT NULL DEFAULT 1,
-> created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
-> updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.33 sec)

Aqui ta uwu:

CREATE TABLE IF NOT EXISTS operations(
    
    `operation_id` INTEGER UNSIGNED NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
    `book_id` INTEGER UNSIGNED NOT NULL,
    `client_id` INTEGER UNSIGNED NOT NULL,
    `type` ENUM('Prestado', 'Vendido', 'Devuelto') NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `finished` TINYINT NOT NULL,

    FOREIGN KEY(`book_id`) REFERENCES books(`book_id`),
    FOREIGN KEY(`client_id`) REFERENCES clients(`client_id`)
    
);

Como utilizo mariaDB puede ser m铆nimamente diferente pero es mas de los mismo al final c:

<
 CREATE TABLE IF NOT EXISTS operations(
     `operation_id` INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
     `book_id` INTEGER UNSIGNED,
     `client_id` INTEGER UNSIGNED,
     `type`ENUM('L', 'R', 'S') NOT NULL COMMENT 'L = lent, R = returned, S = Sold',
     `finished` SMALLINT(1), 
     `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
     `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()
 );
>

Reto completado 馃槃

CREATE TABLE IF NOT EXISTS operations (
    operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    book_id INTEGER UNSIGNED, 
    client_id INTEGER UNSIGNED,
    type ENUM('SELLED', 'BORROWED', 'RETURNED') NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    finished TINYINT(1) NOT NULL DEFAULT '0'
);
CREATE TABLE IF NOT EXISTS operations(
	operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	book_id INTEGER UNSIGNED NOT NULL,
	client_id INTEGER UNSIGNED NOT NULL
	operation ENUM("V","P","D") UNSIGNED,
	created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	finished TINYINT(1) NOT NULL
);

Les dejo un cuadro de los tipos de Datos de fecha y hora

updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
finshed TINYINT(1) NOT NULL

Existe algo as铆 en SQL Servers? Esto es INCRE脥BLE!!

Veo que algunos permiten null en book_id y client_id, lo que no es viable porque a ninguna operaci贸n le puede faltar el libro y cliente correspondientes.

CREATE TABLE IF NOT EXISTS operations (
	operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	book_id INTEGER UNSIGNED NOT NULL,
	client_id INTEGER UNSIGNED NOT NULL,
	`type` ENUM('P', 'D', 'V') NOT NULL, 
	created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
		ON UPDATE CURRENT_TIMESTAMP,
	finished TINYINT(1) NOT NULL
);

CREATE TABLE IF NOT EXISTS operations (
operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
book_id INTEGER UNSIGNED,
client_id INTEGER UNSIGNED,
type ENUM(鈥楶鈥,鈥楧鈥,鈥榁鈥),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
finshed TINYINT(1) NOT NULL

Buen d铆a, esta es mi propuesta para generar la tabla de operations:

CREATE TABLE IF NOT EXISTS operations(
    operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    book_id INTEGER UNSIGNED NOT NULL,
    client_id INTEGER UNSIGNED NOT NULL,
    type ENUM('B','R','S') NOT NULL COMMENT 'Borrowed, Returned, Sold',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    finished TINYINT(1) NOT NULL
);
CREATE TABLE IF NOT EXISTS operations (
  operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  book_id INTEGER UNSIGNED,
  client_id INTEGER UNSIGNED,
  type ENUM('PRESTADO', 'DEVUELTO', 'VENDIDO') NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP,
  finshed TINYINT(1) NOT NULL
);

Mi tabla qued贸 de la siguiente forma.

<CREATE TABLE IF NOT EXISTS operations (
	operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	book_id INTEGER UNSIGNED,
	client_id INTEGER UNSIGNED,
	type ENUM('PRESTADO', 'DEVUELTO', 'VENDIDO'),
	create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	update_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	finshed TINYINT(1) NOT NULL
	);>

Profe no todos los estudiantes son de despues de los 70

CREATE TABLE IF NOT EXISTS operations (
`operation_id` INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
`books_id` INTEGER UNSIGNED,
`client_id` INTEGER UNSIGNED,
`type` ENUM ('Disponible', 'Prestamo', 'Vendido', 'Devuelto' ),
`created_at` TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
`upadte_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE  CURRENT_TIMESTAMP,
`finished` TINYINT (1) NOT NULL
);

Yo lo dej茅 as铆:

CREATE TABLE IF NOT EXISTS operations(
	operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	book_id INTEGER UNSIGNED,
	client_id INTEGER UNSIGNED,
	op_type ENUM('VTA', 'PRE', 'DEV') NOT NULL DEFAULT 'VTA',
	created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	finished TINYINT(1) NOT NULL DEFAULT(0)
);

As铆 qued贸 la tabla:

CREATE TABLE IF NOT EXISTS operations (
	operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  	book_id INTEGER UNSIGNED NOT NULL,
  	client_id INTEGER UNSIGNED NOT NULL,
	type ENUM("borrowed","returned","sold") NOT NULL,
  	created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  	updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  	finished TINYINT(1) NOT NULL
); ```

Hay dos tipos de datos de DATE:

  • DATETIME: no tiene restricciones a la hora de indicar cualquier a帽o de cualquier fecha.
  • TIMESTAMP: esta basada en el n煤mero epoch, n煤mero de segundos desde 1 de Enero de 1970.

LO HICE ASI PERO ME DA ERROR, ME CORRIGEN

CREATE TABLA IF NOT EXISTS operations (
operation_id INTEGER UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
book_id INTEGER UNSIGNED NOT NULL,
cliente_id INTEGER UNSIGNED NOT NULL,
typy ENUM(鈥榩restado鈥, 鈥榙evuelto鈥, 鈥榲endido鈥),
create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
finshed TYNYINT (1) NOT NULL DEFAULT
);

created_ad y updated_ad, Este tipo de dato se comporta m谩s como una meta-informaci贸n y nos puede ayudar por ejemplo a cu谩ntos usuarios fueron creados en una fecha en espec铆fico o en qu茅 fecha los datos de los usuarios fueron actualizados.

CREATE TABLE IF NOT EXISTS operations(
   operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    book_id INTEGER UNSIGNED,
    client_id INTEGER UNSIGNED,
    `type` ENUM('prestado','devuelto','vendido') NOT NULL,
    create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 	CURRENT_TIMESTAMP,
    finshed TINYINT(1) NOT NULL
);

Gracias por aceptarme.Yo nac铆 antes de 1970 y antes de los 60.Sesgado el curso? o el conocimiento?

No conoc铆a la diferencia entre TIMESTAMP y DATETIME ahora la tendr茅 en cuenta a la hora de crear tablas.gracias 馃槂

creacion de tabla operations

CREATE TABLE IF NOT EXISTS operations(
  operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  book_id INTEGER UNSIGNED NOT NULL,
  client_id INTEGER UNSIGNED NOT NULL,
  `type` ENUM('Lend','Return','Sell') NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  finshed TINYINT(1) NOT NULL DEFAULT '0',
);```
CREATE TABLE IF NOT EXISTS operations (
    operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    book_id INTEGER UNSIGNED,
    client_id INTEGER UNSIGNED,
    type ENUM('prestado','devuelto','vendido') NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP,
    finished TINYINT(1) NOT NULL DEFAULT 0
);```

En active utilice el tipo de dato boolean.

CREATE TABLE `clients` (
  `client_id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `email` VARCHAR(100) NOT NULL UNIQUE,
  `birthdate` DATETIME,
  `gender` ENUM('M', 'F', 'ND') NOT NULL,
  `active` BOOLEAN DEFAULT true,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);```
CREATE TABLE `opeations` (
  `opeartion_id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  `book_id` INT UNSIGNED,
  `user_id` INT UNSIGNED,
  `type` ENUM('borrowed', 'sold', 'returned') NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `finished` TINYINT(1) NOT NULL DEFAULT 0
);```

el curso va excelente pero tengo un problema yo estoy haciendo el proceso con SQL server pero tengo un problema con el Columna **Update_at ** tabla **operations **no he podido crear esta tabla esta esta parte ya sql creo que es diferente agredesco si alguien sabe como se maneja esta parte en SQL server.

CREATE TABLE operations ( 
operation_id INT(10) unsigned PRIMARY KEY AUTO_INCREMENT,
book_id INT(10) unsigned NOT NULL,
client_id INT(10) unsigned NOT NULL,
type ENUM('prestado','devuelto','vendido') NOT NULL,
created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
finished tinyint(1) NOT NULL DEFAULT 0);```

Me gusta los trucos que d谩 el profe, enserio es un buen masestro, aq铆 dejo el reto, no es complicado todo ya lo vimos y me queda claro

CREATE TABLE IF NOT EXISTS operations(
    operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT, 
    book_id INTEGER UNSIGNED, 
    client_id INTEGER UNSIGNED, 
    `type` ENUM('PRESTADO', 'DEVUELTO', 'VENDIDO') NOT NULL, 
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    finshed TINYINT(1) NOT NULL DEFAULT 0
);

en mi caso siempre me acostumbre a crear los campos de las tablas con el prefijo del nombre de la tabla ejemplo:
authors_name porque asi me evito siempre el no usar palabras reservadas o que puedan repetirse en otras tablas y cuando haces un select de varias tablas se de que tabla es que campo.

CREATE TABLE IF NOT EXISTS author(
    idAuthor INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    national VARCHAR(3)
);

CREATE TABLE IF NOT EXISTS book(
    idBook INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    idAuthor INTEGER,
    title VARCHAR(100) NOT NULL,
    year INTEGER UNSIGNED NOT NULL DEFAULT 0,
    language varchar(2) NOT NULL DEFAULT 'es' COMMENT 'ISO 639-1 language',
    cover_url VARCHAR(500),
    price DOUBLE(6,2) NOT NULL DEFAULT 10.0,
    sellable TINYINT(1) DEFAULT 1,
    copies INTEGER NOT NULL DEFAULT 1,
    description TEXT
);

CREATE TABLE IF NOT EXISTS client(
    idClient INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(5) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    birthdate DATETIME,
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    active TINYINT(1) NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS operation(
    idOperation INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    idBook INTEGER,
    idClient INTEGER,
    `type` ENUM("S", "L", "R") NOT NULL COMMENT 'S: SALE, L: LEND, R: RETURNED',
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    active TINYINT(1) NOT NULL DEFAULT 1
);

Mi tabla quedo as铆

CREATE TABLE IF NOT EXISTS operations (
    operation_id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
    book_id INTEGER UNSIGNED,
    client_id INTEGER UNSIGNED,
    type ENUM('B','R','S') NOT NULL COMMENT 'Borrow, Return, Sell ',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    finish TINYINT(1) NOT NULL 
);

CREATE TABLE IF NOT EXISTS operations(
operation_id INT PRIMARY KEY AUTO_INCREMENT,
book_id INT AUTO_INCREMENT,
client_id INT AUTO_INCREMENT,
type ENUM(鈥榁鈥, 鈥楶鈥, 鈥楧鈥) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT
CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT
CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
finished TINYINT(1) NOT NULL
)

Listos, est谩 muy interesante este curso 馃槃

Aqu铆 mi tarea:

CREATE TABLE IF NOT EXISTS operations (
	operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	book_id INTEGER UNSIGNED,
	client_id INTEGER UNSIGNED,
	type ENUM('P', 'V', 'D') NOT NULL,
	create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	finished TINYINT(1) NOT NULL
);

el Query que utilice para crear la table de opraciones es el siguiente:

CREATE TABLE IF NOT EXISTS operations (
	opration_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	book_id INTEGER UNSIGNED,
	client_id INTEGER UNSIGNED,
	type ENUM('Prestado','Devuelto','Vendido')NOT NULL,
	created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	finished TINYINT(1) NOT NULL
);
CREATE TABLE IF NOT EXISTS operations (
	`operation_id` INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	`book_id` INTEGER UNSIGNED,
	`cliente_id` INTEGER UNSIGNED,
	`type` ENUM('lend', 'sell', 'retu') NOT NULL,
	`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`finshed` TINYINT(1) NOT NULL
);

CREATE TABLE IF NOT EXISTS operations(
op_id INTEGER(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
book_id INTEGER NOT NULL,
client_id INTEGER NOT NULL,
Tipo ENUM(鈥榁鈥,鈥楻鈥,鈥楶鈥) NOT NULL COMMENT 鈥 Vendido Prestado Regresado鈥,
Costo DOUBLE(4,2),
Creado_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
Actualizdo_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
Finishied TINYINT(1) NOT NULL

);

// Asi me qued贸

script para la tabla del reto 鈥渙perations鈥:

CREATE TABLE IF NOT EXISTS operations (
    operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    book_id INTEGER UNSIGNED,
    client_id INTEGER UNSIGNED,
    type ENUM('lend', 'return', 'sell') NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    finished TINYINT(1) NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS operations (
	operation_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	book_id INT UNSIGNED,
	client_id INT UNSIGNED, 
	`type` ENUM('B','R','S') NOT NULL COMMENT 'B = BORROWED, R = RETURNED, S = SOLD', 
	created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
	finished TINYINT(1) NOT NULL,
	active TINYINT(1) NOT NULL DEFAULT 1
);
CREATE TABLE IF NOT EXISTS operations(
    operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    book_id INTEGER UNSIGNED,
    client_id INTEGER UNSIGNED,
    type ENUM('b', 'r','s' ) NOT NULL COMMENT 'borrowed, return, sold' 
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
    ON UPDATE CURRENT_TIMESTAMP 
    fineshed TINYINT(1) NOT NULL 
);

https://databaseandtech.wordpress.com/2008/05/06/comandos-basicos-para-bases-de-datos-mysql/
En esa liga pueden ver los comandos m谩s utilizados para que sea un poco m谩s digerible la informaci贸n.

CREATE TABLE IF NOT EXISTS operations (
    operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    book_id INTEGER UNSIGNED,
    client_id INTEGER UNSIGNED,
    `type` ENUM('loaned', 'returned', 'sold') NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    finished TINYINT(1) NOT NULL
);

CREATE TABLE IF NOT EXISTS operations(
-> operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> book_id INTEGER UNSIGNED,
-> client_id INTEGER UNSIGNED,
-> type ENUM(鈥楶鈥, 鈥楧鈥, 鈥榁鈥) NOT NULL COMMENT 鈥楶=Prestado, D=Devuelto, V= Vendido鈥,
-> created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
-> updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> finished TINYINT(1) NOT NULL
-> );

CREATE TABLE IF NOT EXISTS operations(
operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
book_id INTEGER UNSIGNED,
client_id INTEGER UNSIGNED,
type ENUM('Borrowed', 'Returned', 'Sold') NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
finshed TINYINT(1) NOT NULL DEFAULT 1
);

Soluci贸n Reto

REATE TABLE IF NOT EXISTS operations (
operations_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
book_ID INTEGER UNSIGNED,
cliente_id INTEGER UNSIGNED,
type ENUM (鈥楶鈥,鈥楧鈥,鈥榁鈥) NOT NULL COMMENT 鈥楶-> Prestado/D->devuelto/V->Vendido鈥,
create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
finished TINYINT (1) NOT NULL
);

CREATE TABLE IF NOT EXISTS operations(
operation_id,
book_id,
client_id,
type,-- pueden ser dos opciones (vendido, prestado o devuelto)
created_at,
updated_at,
finished TINYINT(1) NOT NULL , 鈥 prestado (no terminado hasta que sea devuelto), vendido (terminado)
);

CREATE TABLE IF NOT EXISTS operations (
    operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    book_id INTEGER UNSIGNED,
    client_id INTEGER UNSIGNED,
    operation_type ENUM('borrowed','returned','sold') NOT NULL COMMENT 'Prestado , Devuelto , Vendido',
    created_at_operation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at_operation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    finished TINYINT(1) NOT NULL
);

Una enorme pregunta. C贸mo saber el lugar correcto en la sintaxis, de cada argumento. Por ejemplo. 驴Como saber si va primero Primary Key, o Integer?

Yo pensaba que el orden no afecta, pero al parecer s铆. Realic茅 mi c贸digo, y me lanzaba error, hasta que lo cambie de orden, y pude crear la tabla.

Error:

Orden correcto:

Listo. Tarea cumplida.

Estimados les dejo mi aporte.

Al momento de crear las tablas, les saldra un warning que dice lo siguiente:

Integer display width is deprecated and will be removed in a future release.

Este error es debido a que a la palabra reservada TINYINT les estan colocando como parametro el valor 1.

CREATE TABLE IF NOT EXISTS operations(
operation_id INTEGER unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
book_id INTEGER unsigned not NULL,
client_id INTEGER unsigned NOT NULL,
`type` ENUM('Sold','Borrowed','Returned'),
creted_at timestamp not null DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp not null DEFAULT current_timestamp on update current_timestamp,
finished TINYINT (1) NOT NULL
);

casilla de e-mail debe ser UNIQUE para poder confirmar en el futuro si una persona ya estaba registrada previamente

CREATE TABLE operations(
    operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
    book_id INTEGER UNSIGNED NOT NULL, 
    client_id INTEGER UNSIGNED NOT NULL,
    type ENUM('Vendido','Prestado', 'Devuelto' ) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    finished TINYINT(1) NOT NULL
    
);
CREATE TABLE IF NOT EXISTS operations (
    operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    book_id INTEGER UNSIGNED,
    client_id INTEGER UNSIGNED,
    `type` ENUM ('B', 'R', 'S') NOT NULL COMMENT 'B=Borrowed , R=Returned, S=Sold',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    udpated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 	CURRENT_TIMESTAMP,
    finished TINYINT(1) NOT NULL![Screenshot_2.png]

CREATE TABLE IF NOT EXISTS operations (
operation_id int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
book_id INT(10) unsigned NOT NULL,
client_id INT(10) unsigned NOT NULL,
operation_type enum(鈥楶RESTAMO鈥, 鈥楧EVOLUCI脫N鈥, 鈥榁ENTA鈥)NOT NULL,
created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
finished TINYINT(1) NOT NULL,
UNIQUE KEY operation_id (operation_id)
);

Buenas tardes, envi贸 mi tabla operations

<CREATE TABLE IF NOT EXISTS operations(
    operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    book_id INTEGER UNSIGNED NOT NULL, 
    client_id INTEGER UNSIGNED NOT NULL, 
    operation_type ENUM('L', 'R', 'S') NOT NULL COMMENT 'L = Loan, R = Returned, S = Sold', 
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
      ON UPDATE CURRENT_TIMESTAMP, 
    finished TINYINT(1) NOT NULL 
);> 
create table if not exists operations (
	id integer unsigned primary key auto_increment,
    book_id integer unsigned not null,
    client_id integer unsigned,
    type enum("Prestado", "Vendido", "Devuelto") not null,
    created_at timestamp default current_timestamp not null,
    updated_at timestamp default current_timestamp not null on update current_timestamp,
    finished tinyint(1) not null
);

TIMESTAMP: Es un valor creado de 1970 a la fecha.
DATETIME: Es un valor cualquiera, incluso anterior a nuestra era.

Mi soluci贸n al reto!

CREATE TABLE IF NOT EXISTS `operations` (
    `operation_id` INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `book_id` INTEGER UNSIGNED NOT NULL,
    `client_id` INTEGER UNSIGNED NOT NULL,
    `type` ENUM('S','B','R') NOT NULL COMMENT 'S = Sold, B = Borrowed, R = Returned',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
	ON UPDATE CURRENT_TIMESTAMP,
    `finished` TINYINT(1) NOT NULL DEFAULT 0
);

Ojala actualicen este curso, hago los queries como el profesor, pero la version de su SQL es 5 y la mia 8, supongo que por eso no me acepta los queries a la primera y me toca estar buscando queries de los comentarios para usarlos y asi poder seguir el curso

Listo!

CREATE TABLE IF NOT EXISTS operations (
	operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	book_id INTEGER UNSIGNED NOT NULL,
	client_id INTEGER UNSIGNED NOT NULL,
	operation ENUM('V', 'P', 'D'),
	created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	finshed TINYINT(1) NOT NULL
);

CREATE TABLE IF NOT EXISTS operations (
operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
book_id INTEGER(10) UNSIGNED,
client_id INTEGER(10) UNSIGNED,
type ENUM(鈥淏ORROWED鈥, 鈥淩ETURNED鈥, 鈥淪OLD鈥),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
finished TINYINT (1) NOT NULL
);

mi solucion al reto:

CREATE TABLE IF NOT EXISTS operation(
  operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  book_id INTEGER UNSIGNED NOT NULL,
  client_id INTEGER UNSIGNED NOT NULL,
  `type` enum('S', 'B', 'R') NOT NULL COMMENT 'S= sell, B= borrow, R= returned',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  finished TINYINT(1) NOT NULL
);

Muy buen consejo para todos.
El concepto de borrado l贸gico de los datos y no f铆sico.
si yo tengo una tabla donde inserto que le preste un libro a un cliente y cuando lo regresa BORRO el Registro. como podre consultar despu茅s la historia de cuantas veces le preste el mismo libro? tener un campo
BANDERA en la tabla llamado Activo, permite que con un simpre UPDATE Apague l贸gicamente el campo y pueda conservar los datos en la tabla.

Aqui mi script de la tabla de operations

CREATE TABLE IF NOT EXISTS operations (
  operation_id` integer unsigned PRIMARY KEY AUTO_INCREMENT,
  book_id integer unsigned NOT NULL,
  client_id integer unsigned NOT NULL,
  `type` enum('Borrowed','Returned','Sold') NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `finished` tinyint(1) NOT NULL DEFAULT '0'
) ;

Me sale este error seta que tengo otra version??
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 鈥)鈥 at line 12??

Ayuda}}!!!

muy bien explicado lo de las fechas

Hice esto:

CREATE TABLE IF NOT EXISTS operations(
	operation_id INTEGER USIGNED PRIMARY KEY AUTO_INCREMENT,
	book_id INTEGER USIGNED,
	client_id INTEGER USIGNED,
	type  ENUM('Vendido', 'Prestado', 'Devuelto') NOT NULL,
	created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	finshed TINYINT(1) NOT NULL

);
CREATE TABLE IF NOT EXISTS operations (
  operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  book_id INTEGER UNSIGNED,
  client_id INTEGER UNSIGNED,
  `type` ENUM('p', 'd', 'v') NOT NULL,
  create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  finished TINYINT(1) NOT NULL
);```

CREATE TABLE IF NOT EXISTS operations(
operations_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
book_id INTEGER UNSIGNED,
client_id INTEGER UNSIGNED,
type ENUM(鈥楶鈥,鈥楧鈥,鈥榁鈥),/libro prestado, devuelto o vendido/
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
finshed TINYINT(1) NOT NULL

);

Hola para entender un poco m谩s esto hice un Diagrama ER como en la clase de fundamentos de Base de datos espero sirva