-- https://www.phpmyadmin.net/
--
-- 主機： localhost:3306
-- 產生時間： 2025 年 03 月 23 日 00:10
-- 伺服器版本： 10.11.11-MariaDB-0+deb12u1
-- PHP 版本： 8.2.26

START TRANSACTION;



--
-- 資料庫： `user_auth`
--
-- Create the database
CREATE DATABASE user_auth;

-- Use the new database
USE user_auth;

-- --------------------------------------------------------

--
-- 資料表結構 `AssignedHW`
--

CREATE TABLE `AssignedHW` (
  `assignmentID` int(11) NOT NULL,
  `userID_parent` varchar(10) NOT NULL,
  `userID_child` varchar(10) NOT NULL,
  `hwType` varchar(50) NOT NULL,
  `characters` varchar(1000) DEFAULT NULL,
  `systemSuggestion` varchar(1000) DEFAULT NULL,
  `times` int(11) NOT NULL,
  `assign_time` datetime NOT NULL,
  `duration` int(11) NOT NULL,
  `startDate` datetime NOT NULL,
  `dueDate` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- 資料表結構 `AssignedQuiz`
--

CREATE TABLE `AssignedQuiz` (
  `assignmentID` int(11) NOT NULL,
  `userID_parent` varchar(10) NOT NULL,
  `userID_child` varchar(10) NOT NULL,
  `quizType` varchar(50) NOT NULL,
  `characters` varchar(1000) DEFAULT NULL,
  `systemSuggestion` varchar(1000) DEFAULT NULL,
  `times` int(11) NOT NULL,
  `assign_time` datetime NOT NULL,
  `duration` int(11) NOT NULL,
  `startDate` datetime NOT NULL,
  `dueDate` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- 資料表結構 `children`
--

CREATE TABLE `children` (
  `id` int(11) NOT NULL,
  `accountID` varchar(10) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role_id` char(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- 觸發器 `children`
--
DELIMITER $$
CREATE TRIGGER `after_child_insert` AFTER INSERT ON `children` FOR EACH ROW BEGIN
    INSERT INTO Points (userID, points)
    VALUES (NEW.accountID, 0);
END
$$
DELIMITER ;

-- --------------------------------------------------------

--
-- 資料表結構 `HWAssignment`
--

CREATE TABLE `HWAssignment` (
  `hwID` int(11) NOT NULL,
  `assignmentID` int(11) NOT NULL,
  `progress` enum('Incomplete','In Progress','Complete') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- 資料表結構 `HWExerciseRecord`
--

CREATE TABLE `HWExerciseRecord` (
  `resultID` int(11) NOT NULL,
  `userID_child` varchar(10) NOT NULL,
  `assignmentID` int(11) NOT NULL,
  `recordTime` datetime NOT NULL,
  `characters` text NOT NULL,
  `times` int(11) NOT NULL,
  `duration` int(11) NOT NULL,
  `totalStroke` int(11) NOT NULL,
  `totalMistake` int(11) NOT NULL,
  `score` float NOT NULL,
  `comments` text NOT NULL,
  `feedback` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- 資料表結構 `HWRecord`
--

CREATE TABLE `HWRecord` (
  `recordID` int(11) NOT NULL,
  `userID` varchar(10) NOT NULL,
  `recordTime` datetime NOT NULL,
  `wordSearched` mediumtext NOT NULL,
  `times` int(11) NOT NULL,
  `totalStroke` int(11) NOT NULL,
  `totalMistake` int(11) NOT NULL,
  `score` float NOT NULL,
  `comments` varchar(10000) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- 資料表結構 `HWResult`
--

CREATE TABLE `HWResult` (
  `resultID` int(11) NOT NULL,
  `userID_child` varchar(10) NOT NULL,
  `characters` varchar(1000) NOT NULL,
  `progress` enum('Incomplete','In Progress','Complete') NOT NULL,
  `score` float NOT NULL,
  `finish_time` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- 資料表結構 `HZQuizResult`
--

CREATE TABLE `HZQuizResult` (
  `resultID` int(11) NOT NULL,
  `userID_child` varchar(10) NOT NULL,
  `assignmentID` int(11) NOT NULL,
  `recordTime` datetime NOT NULL,
  `characters` varchar(1000) NOT NULL,
  `times` int(11) NOT NULL,
  `totalStroke` int(11) NOT NULL,
  `totalMistake` int(11) NOT NULL,
  `score` float NOT NULL,
  `comments` varchar(1000) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- 資料表結構 `linkedAccounts`
--

CREATE TABLE `linkedAccounts` (
  `actionID` int(11) NOT NULL,
  `parent_id` varchar(10) NOT NULL,
  `child_id` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- 資料表結構 `LinkingRequests`
--

CREATE TABLE `LinkingRequests` (
  `id` int(11) NOT NULL,
  `requesting_account_id` int(11) DEFAULT NULL,
  `target_account_id` int(11) DEFAULT NULL,
  `status` enum('pending','accepted','declined') DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- 資料表結構 `notifications`
--

CREATE TABLE `notifications` (
  `id` int(11) NOT NULL,
  `userID_child` varchar(10) NOT NULL,
  `userID_parent` varchar(10) NOT NULL,
  `assignmentID` int(11) NOT NULL,
  `message` text NOT NULL,
  `is_read` tinyint(4) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- 替換檢視表以便查看 `ParentChildView`
-- (請參考以下實際畫面)
--
CREATE TABLE `ParentChildView` (
`parent_id` int(11)
,`parent_accountID` varchar(10)
,`parent_username` varchar(50)
,`child_id` int(11)
,`child_accountID` varchar(10)
,`child_username` varchar(50)
);

-- --------------------------------------------------------

--
-- 資料表結構 `parents`
--

CREATE TABLE `parents` (
  `id` int(11) NOT NULL,
  `accountID` varchar(10) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `emailAdd` varchar(100) NOT NULL,
  `role_id` char(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



-- --------------------------------------------------------

--
-- 資料表結構 `Points`
--

CREATE TABLE `Points` (
  `userID` varchar(10) NOT NULL,
  `points` int(11) NOT NULL DEFAULT 0,
  `last_updated` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- 資料表結構 `profiles`
--

CREATE TABLE `profiles` (
  `id` int(11) NOT NULL,
  `user_id` varchar(10) NOT NULL,
  `user_type` enum('parent','child') NOT NULL,
  `sex` enum('male','female','other') NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `icon` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- 資料表結構 `QuizAssignment`
--

CREATE TABLE `QuizAssignment` (
  `quizID` int(11) NOT NULL,
  `assignmentID` int(11) NOT NULL,
  `progress` enum('Incomplete','In Progress','Complete') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- 資料表結構 `SystemSuggestion`
--

CREATE TABLE `SystemSuggestion` (
  `suggestionID` int(11) NOT NULL,
  `taskType` varchar(200) NOT NULL,
  `taskLevel` varchar(10) NOT NULL,
  `taskDescription` varchar(600) NOT NULL,
  `taskContent` varchar(1000) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- 資料表結構 `TTSExerciseRecord`
--

CREATE TABLE `TTSExerciseRecord` (
  `recordID` int(11) NOT NULL,
  `userID` varchar(10) NOT NULL,
  `assignmentID` int(11) NOT NULL,
  `recordTime` datetime NOT NULL,
  `times` int(11) NOT NULL,
  `audio` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- 資料表結構 `TTSQuizResult`
--

CREATE TABLE `TTSQuizResult` (
  `resultID` int(11) NOT NULL,
  `userID_child` varchar(10) NOT NULL,
  `assignmentID` int(11) NOT NULL,
  `recordTime` datetime NOT NULL,
  `times` int(11) NOT NULL,
  `audio` varchar(255) DEFAULT NULL,
  `score` float NOT NULL,
  `translateResult` varchar(1000) NOT NULL,
  `correctWords` text DEFAULT NULL,
  `incorrectWords` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- 資料表結構 `TTSRecord`
--

CREATE TABLE `TTSRecord` (
  `recordID` int(11) NOT NULL,
  `userID_child` varchar(10) DEFAULT NULL,
  `assignmentID` int(11) NOT NULL,
  `recordTime` datetime NOT NULL,
  `wordScanned` varchar(10000) NOT NULL,
  `audio` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- 資料表結構 `user_roles`
--

CREATE TABLE `user_roles` (
  `id` char(1) NOT NULL,
  `role_name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- 傾印資料表的資料 `user_roles`
--

INSERT INTO `user_roles` (`id`, `role_name`) VALUES
('c', 'child'),
('p', 'parent');

-- --------------------------------------------------------

--
-- 資料表結構 `user_time`
--

CREATE TABLE `user_time` (
  `id` int(11) NOT NULL,
  `user_id` varchar(10) NOT NULL,
  `dateOfVisit` date NOT NULL,
  `start_timestamp` time DEFAULT current_timestamp(),
  `end_timestamp` timestamp NULL DEFAULT NULL,
  `duration` int(11) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- 檢視表結構 `ParentChildView`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `ParentChildView`  AS SELECT `p`.`id` AS `parent_id`, `p`.`accountID` AS `parent_accountID`, `p`.`username` AS `parent_username`, `c`.`id` AS `child_id`, `c`.`accountID` AS `child_accountID`, `c`.`username` AS `child_username` FROM ((`parents` `p` join `linkedAccounts` `la` on(`p`.`id` = `la`.`parent_id`)) join `children` `c` on(`la`.`child_id` = `c`.`id`)) ;

--
-- 已傾印資料表的索引
--

--
-- 資料表索引 `AssignedHW`
--
ALTER TABLE `AssignedHW`
  ADD PRIMARY KEY (`assignmentID`),
  ADD KEY `userID_child` (`userID_child`),
  ADD KEY `userID_parent` (`userID_parent`);

--
-- 資料表索引 `AssignedQuiz`
--
ALTER TABLE `AssignedQuiz`
  ADD PRIMARY KEY (`assignmentID`),
  ADD KEY `userID_child` (`userID_child`),
  ADD KEY `userID_parent` (`userID_parent`);

--
-- 資料表索引 `children`
--
ALTER TABLE `children`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `accountID` (`accountID`),
  ADD UNIQUE KEY `username` (`username`),
  ADD KEY `role_id` (`role_id`);

--
-- 資料表索引 `HWAssignment`
--
ALTER TABLE `HWAssignment`
  ADD PRIMARY KEY (`hwID`),
  ADD KEY `assignmentID` (`assignmentID`);

--
-- 資料表索引 `HWExerciseRecord`
--
ALTER TABLE `HWExerciseRecord`
  ADD PRIMARY KEY (`resultID`),
  ADD KEY `userID_child` (`userID_child`),
  ADD KEY `assignmentID` (`assignmentID`);

--
-- 資料表索引 `HWRecord`
--
ALTER TABLE `HWRecord`
  ADD PRIMARY KEY (`recordID`),
  ADD KEY `userID` (`userID`);

--
-- 資料表索引 `HWResult`
--
ALTER TABLE `HWResult`
  ADD PRIMARY KEY (`resultID`),
  ADD KEY `userID_child` (`userID_child`);

--
-- 資料表索引 `HZQuizResult`
--
ALTER TABLE `HZQuizResult`
  ADD PRIMARY KEY (`resultID`),
  ADD KEY `userID_child` (`userID_child`),
  ADD KEY `assignmentID` (`assignmentID`);

--
-- 資料表索引 `linkedAccounts`
--
ALTER TABLE `linkedAccounts`
  ADD PRIMARY KEY (`actionID`),
  ADD KEY `parent_id` (`parent_id`),
  ADD KEY `child_id` (`child_id`);

--
-- 資料表索引 `LinkingRequests`
--
ALTER TABLE `LinkingRequests`
  ADD PRIMARY KEY (`id`);

--
-- 資料表索引 `notifications`
--
ALTER TABLE `notifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `userID_child` (`userID_child`),
  ADD KEY `userID_parent` (`userID_parent`);

--
-- 資料表索引 `parents`
--
ALTER TABLE `parents`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `accountID` (`accountID`),
  ADD UNIQUE KEY `username` (`username`),
  ADD UNIQUE KEY `emailAdd` (`emailAdd`),
  ADD KEY `role_id` (`role_id`);

--
-- 資料表索引 `Points`
--
ALTER TABLE `Points`
  ADD PRIMARY KEY (`userID`);

--
-- 資料表索引 `profiles`
--
ALTER TABLE `profiles`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `user_id` (`user_id`,`user_type`);

--
-- 資料表索引 `QuizAssignment`
--
ALTER TABLE `QuizAssignment`
  ADD PRIMARY KEY (`quizID`),
  ADD KEY `assignmentID` (`assignmentID`);

--
-- 資料表索引 `SystemSuggestion`
--
ALTER TABLE `SystemSuggestion`
  ADD PRIMARY KEY (`suggestionID`);

--
-- 資料表索引 `TTSExerciseRecord`
--
ALTER TABLE `TTSExerciseRecord`
  ADD PRIMARY KEY (`recordID`),
  ADD KEY `userID` (`userID`),
  ADD KEY `assignmentID` (`assignmentID`);

--
-- 資料表索引 `TTSQuizResult`
--
ALTER TABLE `TTSQuizResult`
  ADD PRIMARY KEY (`resultID`),
  ADD KEY `userID_child` (`userID_child`),
  ADD KEY `assignmentID` (`assignmentID`);

--
-- 資料表索引 `TTSRecord`
--
ALTER TABLE `TTSRecord`
  ADD PRIMARY KEY (`recordID`),
  ADD KEY `userID_child` (`userID_child`),
  ADD KEY `assignmentID` (`assignmentID`);

--
-- 資料表索引 `user_roles`
--
ALTER TABLE `user_roles`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `role_name` (`role_name`);

--
-- 資料表索引 `user_time`
--
ALTER TABLE `user_time`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`);

--
-- 在傾印的資料表使用自動遞增(AUTO_INCREMENT)
--

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `AssignedHW`
--
ALTER TABLE `AssignedHW`
  MODIFY `assignmentID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `AssignedQuiz`
--
ALTER TABLE `AssignedQuiz`
  MODIFY `assignmentID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `children`
--
ALTER TABLE `children`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `HWAssignment`
--
ALTER TABLE `HWAssignment`
  MODIFY `hwID` int(11) NOT NULL AUTO_INCREMENT;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `HWExerciseRecord`
--
ALTER TABLE `HWExerciseRecord`
  MODIFY `resultID` int(11) NOT NULL AUTO_INCREMENT;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `HWRecord`
--
ALTER TABLE `HWRecord`
  MODIFY `recordID` int(11) NOT NULL AUTO_INCREMENT;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `HWResult`
--
ALTER TABLE `HWResult`
  MODIFY `resultID` int(11) NOT NULL AUTO_INCREMENT;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `HZQuizResult`
--
ALTER TABLE `HZQuizResult`
  MODIFY `resultID` int(11) NOT NULL AUTO_INCREMENT;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `linkedAccounts`
--
ALTER TABLE `linkedAccounts`
  MODIFY `actionID` int(11) NOT NULL AUTO_INCREMENT;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `LinkingRequests`
--
ALTER TABLE `LinkingRequests`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `notifications`
--
ALTER TABLE `notifications`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `parents`
--
ALTER TABLE `parents`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=26;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `profiles`
--
ALTER TABLE `profiles`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `QuizAssignment`
--
ALTER TABLE `QuizAssignment`
  MODIFY `quizID` int(11) NOT NULL AUTO_INCREMENT;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `SystemSuggestion`
--
ALTER TABLE `SystemSuggestion`
  MODIFY `suggestionID` int(11) NOT NULL AUTO_INCREMENT;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `TTSExerciseRecord`
--
ALTER TABLE `TTSExerciseRecord`
  MODIFY `recordID` int(11) NOT NULL AUTO_INCREMENT;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `TTSQuizResult`
--
ALTER TABLE `TTSQuizResult`
  MODIFY `resultID` int(11) NOT NULL AUTO_INCREMENT;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `TTSRecord`
--
ALTER TABLE `TTSRecord`
  MODIFY `recordID` int(11) NOT NULL AUTO_INCREMENT;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `user_time`
--
ALTER TABLE `user_time`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- 已傾印資料表的限制式
--

--
-- 資料表的限制式 `AssignedHW`
--
ALTER TABLE `AssignedHW`
  ADD CONSTRAINT `AssignedHW_ibfk_1` FOREIGN KEY (`userID_child`) REFERENCES `children` (`accountID`),
  ADD CONSTRAINT `AssignedHW_ibfk_2` FOREIGN KEY (`userID_parent`) REFERENCES `parents` (`accountID`);

--
-- 資料表的限制式 `AssignedQuiz`
--
ALTER TABLE `AssignedQuiz`
  ADD CONSTRAINT `AssignedQuiz_ibfk_1` FOREIGN KEY (`userID_child`) REFERENCES `children` (`accountID`),
  ADD CONSTRAINT `AssignedQuiz_ibfk_2` FOREIGN KEY (`userID_parent`) REFERENCES `parents` (`accountID`);

--
-- 資料表的限制式 `children`
--
ALTER TABLE `children`
  ADD CONSTRAINT `children_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `user_roles` (`id`) ON DELETE CASCADE;

--
-- 資料表的限制式 `HWAssignment`
--
ALTER TABLE `HWAssignment`
  ADD CONSTRAINT `HWAssignment_ibfk_1` FOREIGN KEY (`assignmentID`) REFERENCES `AssignedHW` (`assignmentID`);

--
-- 資料表的限制式 `HWExerciseRecord`
--
ALTER TABLE `HWExerciseRecord`
  ADD CONSTRAINT `HWExerciseRecord_ibfk_1` FOREIGN KEY (`userID_child`) REFERENCES `children` (`accountID`),
  ADD CONSTRAINT `HWExerciseRecord_ibfk_2` FOREIGN KEY (`assignmentID`) REFERENCES `AssignedHW` (`assignmentID`);

--
-- 資料表的限制式 `HWRecord`
--
ALTER TABLE `HWRecord`
  ADD CONSTRAINT `HWRecord_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `children` (`accountID`);

--
-- 資料表的限制式 `HWResult`
--
ALTER TABLE `HWResult`
  ADD CONSTRAINT `HWResult_ibfk_1` FOREIGN KEY (`userID_child`) REFERENCES `children` (`accountID`);

--
-- 資料表的限制式 `HZQuizResult`
--
ALTER TABLE `HZQuizResult`
  ADD CONSTRAINT `HZQuizResult_ibfk_1` FOREIGN KEY (`userID_child`) REFERENCES `children` (`accountID`),
  ADD CONSTRAINT `HZQuizResult_ibfk_2` FOREIGN KEY (`assignmentID`) REFERENCES `AssignedQuiz` (`assignmentID`);

--
-- 資料表的限制式 `linkedAccounts`
--
ALTER TABLE `linkedAccounts`
  ADD CONSTRAINT `linkedAccounts_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parents` (`accountID`) ON DELETE CASCADE,
  ADD CONSTRAINT `linkedAccounts_ibfk_2` FOREIGN KEY (`child_id`) REFERENCES `children` (`accountID`) ON DELETE CASCADE;

--
-- 資料表的限制式 `notifications`
--
ALTER TABLE `notifications`
  ADD CONSTRAINT `notifications_ibfk_1` FOREIGN KEY (`userID_child`) REFERENCES `children` (`accountID`) ON DELETE CASCADE,
  ADD CONSTRAINT `notifications_ibfk_2` FOREIGN KEY (`userID_parent`) REFERENCES `parents` (`accountID`) ON DELETE CASCADE;

--
-- 資料表的限制式 `parents`
--
ALTER TABLE `parents`
  ADD CONSTRAINT `parents_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `user_roles` (`id`) ON DELETE CASCADE;

--
-- 資料表的限制式 `Points`
--
ALTER TABLE `Points`
  ADD CONSTRAINT `Points_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `children` (`accountID`) ON DELETE CASCADE;

--
-- 資料表的限制式 `QuizAssignment`
--
ALTER TABLE `QuizAssignment`
  ADD CONSTRAINT `QuizAssignment_ibfk_1` FOREIGN KEY (`assignmentID`) REFERENCES `AssignedQuiz` (`assignmentID`);

--
-- 資料表的限制式 `TTSExerciseRecord`
--
ALTER TABLE `TTSExerciseRecord`
  ADD CONSTRAINT `TTSExerciseRecord_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `children` (`accountID`),
  ADD CONSTRAINT `TTSExerciseRecord_ibfk_2` FOREIGN KEY (`assignmentID`) REFERENCES `AssignedHW` (`assignmentID`);

--
-- 資料表的限制式 `TTSQuizResult`
--
ALTER TABLE `TTSQuizResult`
  ADD CONSTRAINT `TTSQuizResult_ibfk_1` FOREIGN KEY (`userID_child`) REFERENCES `children` (`accountID`),
  ADD CONSTRAINT `TTSQuizResult_ibfk_2` FOREIGN KEY (`assignmentID`) REFERENCES `AssignedQuiz` (`assignmentID`);

--
-- 資料表的限制式 `TTSRecord`
--
ALTER TABLE `TTSRecord`
  ADD CONSTRAINT `TTSRecord_ibfk_1` FOREIGN KEY (`userID_child`) REFERENCES `children` (`accountID`),
  ADD CONSTRAINT `TTSRecord_ibfk_2` FOREIGN KEY (`assignmentID`) REFERENCES `AssignedHW` (`assignmentID`);

--
-- 資料表的限制式 `user_time`
--
ALTER TABLE `user_time`
  ADD CONSTRAINT `user_time_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `children` (`accountID`) ON DELETE CASCADE;
COMMIT;

