Can I empty all tables of the database

Can I empty all tables of the database

  

Hello, 

IM doing the web developer track and make some dummy data. 

Now I wonder if I can empty all the tables in the database so I can start with a clean database for some tests are described in the challenge. 


Roelof

Solution

Hello Roelof,

Unless you have access to the database (to do a TRUNCATE directly on it), I think the easiest way will be to do an SQL and DELETE FROM all the tables, like this:

DELETE FROM {Table1};
DELETE FROM {Table2};
DELETE FROM {Table3};
...
DELETE FROM {TableN};

Remember that depending on the DELETE RULE you set in the Foreign Keys, that can be DELETE, PROTECTED (default) or IGNORE, you will need to delete FIRST the entities that no other entities point to them. 

Cheers.

Solution

Oke, 

So in the case of the booking challenge this could be a deleteting order: 

-  RoomAmentities  

- RoomDetail 

- Rooms 

- BookingDetail 

- Bookings 



If I remember, unless you have set the RoomId delete  rule in booking to something different than Protected, toy can't delete Room before booking...

oke, 

Another problem. 

I do in the SQL widget 


  DELETE FROM Room; 

  DELETE FROM Booking; 


but then I see a error message

Error in advanced query SQL1 in DeleteDatabase in Rooms in MainFlow in Bookings (DELETE FROM Room; DELETE FROM Booking; ): Invalid object name 'Room'.
 


Roelof


Hi,

You need to use DELETE FROM {Room}. Brackets are required when specifying entities in Advanced queries.

Cheers,

João

Thanks, 

The order I do now is Rooms , Bookings and then RoomAmentity but still a error : 

Error in advanced query SQL1 in DeleteDatabase in Rooms in MainFlow in Bookings (DELETE FROM {Room}; DELETE FROM {Booking}; DELETE FROM {RoomAmenity}; ): The DELETE statement conflicted with the REFERENCE constraint "OSFRK_OSUSR_V31_BOOKING_OSUSR_V31_ROOM_ROOMID". The conflict occurred in database "STDS1C011", table "dbo.OSUSR_V31_BOOKING", column 'ROOMID'. The statement has been terminated.

Roelof Wobben wrote:

Thanks, 

The order I do now is Rooms , Bookings and then RoomAmentity but still a error : 

Error in advanced query SQL1 in DeleteDatabase in Rooms in MainFlow in Bookings (DELETE FROM {Room}; DELETE FROM {Booking}; DELETE FROM {RoomAmenity}; ): The DELETE statement conflicted with the REFERENCE constraint "OSFRK_OSUSR_V31_BOOKING_OSUSR_V31_ROOM_ROOMID". The conflict occurred in database "STDS1C011", table "dbo.OSUSR_V31_BOOKING", column 'ROOMID'. The statement has been terminated.

The error is occurring because you are deleting rooms that are associated with bookings, and, because the delete rule is set to protect, triggers this error.

Now, you need to delete booking before deleting rooms or setting the delete rule on the RoomId in the Booking entity to Delete.

Thanks, I found out the right order. it is RoomAmentity,  Booking, Room.