DELETE

This command deletes rows from a table as shown below.

Este comando permite borrar renglones de una tabla como se muestra debajo.

Tip

When deleting rows from a table, any foreign key constraints will be checked. This means that trying to delete a row with references in a child table, using DELETE, will not be successful. You can either delete the child rows first, or you can use ON DELETE CASCADE (when creating the reference) to delete these rows.

Cuando se borran renglones de una tabla, todas las restricciones impuestas en la llave extranjera son verificadas. Esto quiere decir que al tratar de borrar renglones con referencias en una tabla hijo, usando DELETE no será exitoso. En este caso se necesitan borrar los renglones respectivos de la tabla hijo primero o se puede usar la opción ON DELETE CASCADE (al crear la referencia) para borrar estos renglones.

Cascade Delete flow

The flow of cascade delete is established between two tables. The flow is reverse at the direction of the reference as shown in the figure. To set up cascade delete, you must add the command ON DELETE CASCADE after the REFERENCES command.

El flujo del borrar en cascada se establece entre dos tablas. Este es inverso a la dirección de la referencia como se ilustra en la figura. Para activar el borrado en cascada, usted debe agregar el comando ON DELETE CASCADE déspues del comando REFERENCES.

Problem 1

city_bank > Indicate whether the following statement is true or false: If any row from the client table is deleted, then all references in the client_account table will be removed when the option ON DELETE CASCADE is used. However, none row from the account table will be deleted. This is because each account may belong to several clients.

Diga si es cierto o falso: Si se elimina cualquier cliente de la tabla client, todas las referencias en client_account serán eliminadas si se usa la opción de ON DELETE CASCADE. Sin embargo, ningún renglón de la tabla account será eliminado. Esto es debido a que cada cuenta puede pertenecer a varios clientes.

Problem 2

city_bank > Indicate whether the following statement is true or false: When one row is deleted from the client_account table, the account will not be deleted even if ON DELETE CASCADE is active.

Diga si es cierto o falso: Al borrar un reglón de la tabla client_account no se borrará la cuenta aun cuando ON DELETE CASCADE este activo.

Problem 3

city_bank > Indicate whether the following statement is true or false: In order to delete a row from the client_account table when ON DELETE CASCADE is not active, it is necessary to separately delete the rows from each table. Discuss your answer and provide valid arguments.

Diga si es cierto o falso: Para borrar un renglón de la tabla client_account cuando ON DELETE CASCADE no está activo es necesario borrar los renglones en cada tabla por separado. Discuta su respuesta y proporcione argumentos válidos.

Tip

When ON DELETE CASCADE is not active, it is not possible to delete from tables that have references represented by input arrows.

Cuando ON DELETE CASCADE no está activo, no es posible borrar de tablas que tengan referencias representadas por flechas entrantes.

Problem 4

city_bank > Suppose that the Bellavista branch is closing. Create an SQL file called bellavista_closing.sql to remove rows on the branch, client_account and account tables. (a) Assume that ON DELETE CASCADE is not active in any table (Use DELETE and SELECT). (b) Assume that ON DELETE CASCASE is active between the branch and account tables; and between the account and client_account tables.

Suponga que la sucursal Bellavista va a cerrar. Cree un archivo de SQL llamado bellavista_closing.sql para remover los registros apropiados de las tablas sucursal, cliente_cuenta y cuenta (Use DELETE y SELECT). (a) Sin ON DELETE CASCADE activo. (b) Asuma que ON DELETE CASCADE está activo entre las tablas sucursal y cuenta; y entre las tablas cuenta y cliente_cuenta.

Problem 5

city_bank > Suppose we would like to remove all clients with a negative balance. Create an SQL file called second_chance.sql to remove the respective rows only from the client table and the client_account table. (a) Assume that ON DELETE CASCADE is active between the client table and the client_account table. (b) Assume ON DELETE CASCADE is not active. Hint: Use sub-queries. Write a query to find the client_id of those clients with a negative balance.

Suponga que se desea remover todos los clientes que tienen un balance negativo. Cree un archivo SQL llamado second_chance.sql usando sub-consultas para remover los registros apropiados de las tablas client y client_account (sin borrar las cuentas aunque se quede basura). (a) Asuma que ON DELETE CASCADE está activo entre las tablas client y client_account. (b) Sin ON DELETE CASCADE activo. Sugerencia: use sub-consultas. Escriba una consulta para encontrar el client_id de aquellos clientes con un balance negativo.

DROP

To delete a table use the DROP command as shown in the example below.

Para borrar una tabla use el comando DROP como se muestra en el ejemplo de abajo.

SQL

DROP TABLE patient;

Tip

You may use the DROP command to remove a constraint from a table, see example.

Usted puede usar el comando DROP para borrar una restricción de una tabla, vea el ejemplo.

SQL

CREATE TABLE patient
(
     patient_id NUMBER NOT NULL,
     name NVARCHAR(20) NOT NULL,
     age INT CONSTRAINT verify_age CHECK (age BETWEEN 0 AND 100)
);
GO

ALTER TABLE PATIENT
DROP CONSTRAINT verify_age;

Tip

The following example illustrates how to activate ON DELETE CASCADE.

El siguiente ejemplo ilustra como activar ON DELETE CASCADE.

city_bank.sql

...

CREATE TABLE dbo.account
(
     account_id INT NOT NULL PRIMARY KEY,
     balance MONEY NOT NULL,
     type CHAR(1) CHECK( type = 'S' OR type = 'C'),
     branch_id INT NOT NULL REFERENCES branch(branch_id) ON DELETE CASCADE
);
GO

CREATE TABLE dbo.client_account
(
     client_id INT NOT NULL REFERENCES client(client_id) ON DELETE CASCADE,
     account_id INT NOT NULL REFERENCES account(account_id) ON DELETE CASCADE,
     PRIMARY KEY (client_id, account_id)
);
GO

Problem 6

city_bank > Write all the SQL statement to destroy the database, including data and tables. You will not be able to remove data/tables if you have references to other tables. You cannot use the DROP DATABASE command. Suppose that ON DELETE CASCADE is not active. Remember that a table can be deleted when the table does not have any input arrow (references). Use the DROP TABLE command.

Sin usar el comando DROP DATABASE, cree un script SQL para destruir todas las tablas de la base de datos y su contenido. Recuerde que no es posible eliminar una tabla/datos si existen referencias a otras tablas. Suponga que ON DELETE CASCADE no está activo. Recuerde que una tabla puede ser borrada cuando la tabla no tiene flechas entrantes (referencias). Use el comando DROP TABLE.

Problem 7

motorola > Write all the SQL statement to destroy the database, including data and tables. You will not be able to remove data/tables if you have references to other tables. You cannot use the DROP DATABASE command. Suppose that ON DELETE CASCADE is not active. Use the DROP TABLE command.

Problem 8

circuit_city > Write all the SQL statement to destroy the database, including data and tables. You will not be able to remove data/tables if you have references to other tables. You cannot use the DROP DATABASE command. Suppose that ON DELETE CASCADE is not active. Use the DROP TABLE command.

Problem 9

circuit_city > Suppose that ON DELETE CASCADE is active in all references in the tables in this database, indicate from what tables, data will be deleted when data is deleted from: (a) the item table, (b) the client table, (c) the orderx table, (d) the ord_det table, and (e) the credit_client table.

Suponga que ON DELETE CASCADE está activo en todas las referencias en las tablas de esta base de datos, indica de que tablas se borrarán datos cuando se borran datos en: (a) la tabla item, (b) la tabla client, (c) la tabla orderx, (d) la tabla ord_det, y (e) la tabla credit_client.

DELETE

DELETE

Cascade Delete flow

DROP

© Copyright 2000-2021 Wintempla selo. All Rights Reserved. Jul 22 2021. Home