# Exploring many-to-one/one-to-many relationships

## Deleting data in referential databases
1. Try to run the command `DELETE FROM restaurants WHERE id = '1000';`. Paste the error code below. Why do you think this happened?

```sql
ERROR:  update or delete on table "restaurants" violates foreign key constraint "grades_restaurant_id_fkey" on table "grades"
DETAIL:  Key (id)=(1000) is still referenced from table "grades".
```

Postgres stops you from deleting the restaurant because of the foreign key constraint.
Foreign key constraints stop you from destroying referential integrity of a relational database.

##  Find data on a given table
2. Run `\d restaurants` and `\d grades` and you'll see data about the table.

Specifically:
* table column, type, collation, nullable, default
* Indexes
* Referenced by:

```sql
\d restaurants
                                         Table "public.restaurants"
         Column          |      Type       | Collation | Nullable |                 Default
-------------------------+-----------------+-----------+----------+-----------------------------------------
 id                      | integer         |           | not null | nextval('restaurants_id_seq'::regclass)
 name                    | text            |           | not null |
 nyc_restaurant_id       | integer         |           |          |
 borough                 | borough_options |           |          |
 cuisine                 | text            |           |          |
 address_building_number | text            |           |          |
 address_street          | text            |           |          |
 address_zipcode         | text            |           |          |
Indexes:
    "restaurants_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "grades" CONSTRAINT "grades_restaurant_id_fkey" FOREIGN KEY (restaurant_id) REFERENCES restaurants(id)

```

On grades, note the foreign-key constraints:
```sql
\d grades
                                        Table "public.grades"
    Column     |           Type           | Collation | Nullable |              Default
---------------+--------------------------+-----------+----------+------------------------------------
 id            | integer                  |           | not null | nextval('grades_id_seq'::regclass)
 date          | timestamp with time zone |           | not null |
 grade         | text                     |           | not null |
 score         | integer                  |           | not null |
 restaurant_id | integer                  |           |          |
Indexes:
    "grades_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "grades_restaurant_id_fkey" FOREIGN KEY (restaurant_id) REFERENCES restaurants(id)

```

## Creating a table with foreign-key constraints:

This is one of the methods to ensure referential integrity. Kind of like throw an error if someone tries to delete a table when it has a relation with another database.
```sql
CREATE TABLE grades (
    id serial PRIMARY KEY,
    "date" timestamptz NOT NULL,
    grade text NOT NULL,
    score integer NOT NULL,
    restaurant_id integer REFERENCES restaurants
);
```

##  Create a table with`CASCADE` alternative

Another strategy is to create a table with a cascade, which will cause the related entity to be dropped.

```sql
CREATE TABLE grades (
    id serial PRIMARY KEY,
    "date" timestamp NOT NULL,
    grade text NOT NULL,
    score integer NOT NULL,
    restaurant_id integer REFERENCES restaurants ON DELETE CASCADE
);
```

## Alter existing `REFERENCES` table to `CASCADE`

```sql
ALTER TABLE grades
    DROP CONSTRAINT grades_restaurant_id_fkey,
    ADD CONSTRAINT grades_restaurant_id_fkey
        FOREIGN KEY (restaurant_id)
        REFERENCES restaurants(id)
        ON DELETE CASCADE;
```

### Try it out:

1. `SELECT count(*) FROM grades;`
```sql
count
-------
 93450
(1 row)
```
2. `DELETE FROM restaurants WHERE id = 1000;`
```sql
DELETE 1
```
3. `dev-restaurants-app=# SELECT count(*) from grades;`
 ```sql
 count
-------
 93446
(1 row)
```

## Alternatively, set `foreign-key value` to `NULL`

```sql
ALTER TABLE grades
    DROP CONSTRAINT grades_restaurant_id_fkey,
    ADD CONSTRAINT grades_restaurant_id_fkey
        FOREIGN KEY (restaurant_id)
        REFERENCES restaurants(id)
        ON DELETE SET NULL;
```

Try out commands:

```sql
SELECT * FROM grades WHERE id = 15; -- it has restaurant_id of 10
DELETE FROM restaurants WHERE id = 10;
SELECT * FROM grades WHERE id = 15; -- restaurant_id is null
```