1

This is my SQL code:

-- DROP the database if it exists
DROP DATABASE IF exists labtest;

-- CREATE THE DATABASE
CREATE DATABASE labtest;

-- SHOW DATABASES;
SHOW DATABASES;

-- Use the database
USE labtest;

-- Create Table 1 
CREATE TABLE user(
    userid INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50));

-- Create Table 2
CREATE TABLE sale(
    saleID  INT AUTO_INCREMENT PRIMARY KEY,
    userid  INT NOT NULL,
    item VARCHAR(50),
    FOREIGN KEY (userid) REFERENCES user (userid) ON DELETE RESTRICT);

-- Create Table 3

SHOW TABLES;

-- INSERT 
INSERT INTO user(name)
VALUES ('A'),
('B'),
('C'),
('D'),
('E');

INSERT INTO sale(userid,item)
VALUES (2,'item1'),
(3,'item2'),
(1,'item3'),
(4,'item4'),
(4,'item5');

-- SELECT (READ)
SELECT saleID,s.userid,name,item
FROM user as u,sale as s
WHERE u.userid = s.userid;


-- DELETE (DELETE)
DELETE FROM user
WHERE name='D';

When I try to delete I don't get an error like I'm supposed to, even though I'm deleting a parent record. How do I impose a restrict action? Adding ON DELETE RESTRICT doesn't help either.

1
  • This might be of help! p.s. welcome to the forum! – Vérace Jun 29 '19 at 23:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.