如何在MySQL中准确设置一个外键方案来完成我的地址簿

  • Z时代
  • 2024-01-10
  • 分类：问答

我一直在创建一个数据库并学习。我最近开始使用InnoDB并使用外键将表连接在一起。如何在MySQL中准确设置一个外键方案来完成我的地址簿

但是，老实说，我可能会盲目地使我的外键。在创建外键时，我需要使用哪些正确的设置和检查列表。

我对外键的理解是我有一个主表，并且我的主表中的任何更改都反映到任何持有外键到其中的特定列的表中。

所以我目前的登录系统具有建立这样

users 
===== 
id PK 
username 
password

和我的其他表看起来像这样

contacts 
======== 
id PK 
user_id references `users`.`id` 
group 
name 
address 
groups 
====== 
id PK 
user_id 
group_name 
group_contacts 
============== 
id PK 
group_id references `group`.`id` 
contact_id references `contacts`.`id`

据我了解这些表可以被删除时，主表使用ON DELETE CASCADE选项是否正确删除？

我现在的问题是，我似乎无法使group_id和contact_id一个外键为groups。 id和contacts。 id与此设置。运行SQL语句时出现错误。

我试图让我的地址簿，以便当用户将联系人放入一个组变成全部自动化，我不必改变很多信息。 group_contact表是我想要查看每个联系人所属的位置时所想查询的内容。如果我改变一个组的名字，它会反映在所有的表上吗？这就是外键进来的地方，我正在把自己与这些键的行为方式混淆起来。

但就像我说过，我似乎无法使外键没有得到一个错误。

我知道我可以Google我的外键的问题，我有，但我似乎无法学习这种方式没有得到反馈和我的确切的情况;（

不要问太多而是因为我的困惑我也很难试图看看如何使PHP脚本来处理组名更改和查询数据库，以拉下属于特定组的联系人。

这真的会帮助我很多人，我希望学到一些东西！

我的查询是这样的：

ALTER TABLE `list_`.`groups_contacts` 
    ADD CONSTRAINT `group_id` FOREIGN KEY (`group_id`) REFERENCES `list_`.`groups` (`id`) 
    ADD CONSTRAINT `contact_id` FOREIGN KEY (`contact_id`) REFERENCES `list_`.`contacts` (`id`);

我的数据库看起来是这样的：

CREATE TABLE `list_`.`buyer` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `user_id` tinyint(11) NOT NULL, 
    `isClosed` tinyint(1) NOT NULL, 
    `display_limit` int(1), 
    `first_name` varchar(255) NOT NULL, 
    `last_name` varchar(255) NOT NULL, 
    `prop_address` varchar(255) NOT NULL, 
    `city` varchar(255) NOT NULL, 
    `state` varchar(11) NOT NULL, 
    `zip` varchar(5) NOT NULL, 
    `cell_phone` varchar(16) NOT NULL, 
    `home_phone` varchar(16) NOT NULL, 
    `other1` varchar(16) NOT NULL, 
    `other2` varchar(16) NOT NULL, 
    `comments` text NOT NULL, 
    `comment_exist` tinyint(1) NOT NULL, 
    `comment_date` text NOT NULL, 
    `date_added` date NOT NULL, 
    `date_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    PRIMARY KEY (`id`,`user_id`), 
    KEY `user_id` (`user_id`) 
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8 AUTO_INCREMENT=23 ; 
CREATE TABLE `list_`.`company` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `user_id` tinyint(11) NOT NULL DEFAULT '0', 
    `company_name` varchar(128) NOT NULL, 
    PRIMARY KEY (`id`, `user_id`), 
    KEY `user_id` (`user_id`) 
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ; 
CREATE TABLE `list_`.`contacts` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `user_id` tinyint(11) NOT NULL, 
    `group` varchar(128) NOT NULL, 
    `first_name` varchar(128) NOT NULL, 
    `last_name` varchar(128) NOT NULL, 
    `address` varchar(128) NOT NULL, 
    `city` varchar(128) NOT NULL, 
    `state` varchar(2) NOT NULL, 
    `zip` int(5) NOT NULL, 
    `phone_number` varchar(16) NOT NULL, 
    `cell_number` varchar(16) NOT NULL, 
    `work_number` varchar(16) NOT NULL, 
    `fax_number` varchar(16) NOT NULL, 
    `email` varchar(128) NOT NULL, 
    `company` varchar(55) NOT NULL, 
    `title` varchar(56) NOT NULL, 
    `notes` text NOT NULL, 
    `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    PRIMARY KEY (`id`,`user_id`), 
    KEY `user_id` (`user_id`), 
    KEY `group` (`group`) 
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ; 
CREATE TABLE `list_`.`groups` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `user_id` tinyint(11) NOT NULL, 
    `position` int(8) unsigned NOT NULL DEFAULT '0', 
    `name` varchar(128) NOT NULL, 
    `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    PRIMARY KEY (`id`,`user_id`), 
    KEY `user_id` (`user_id`), 
    KEY `name` (`name`) 
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8 AUTO_INCREMENT=32 ; 
CREATE TABLE `list_`.`prospect` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `user_id` tinyint(11) NOT NULL, 
    `isClosed` tinyint(1) DEFAULT '0', 
    `display_limit` int(1) DEFAULT '0', 
    `first_name` varchar(255) NOT NULL, 
    `last_name` varchar(255) NOT NULL, 
    `prop_address` varchar(255) NOT NULL, 
    `city` varchar(255) NOT NULL, 
    `state` varchar(11) NOT NULL, 
    `zip` varchar(5) NOT NULL, 
    `cell_phone` varchar(16) NOT NULL, 
    `home_phone` varchar(16) NOT NULL, 
    `other1` varchar(16) NOT NULL, 
    `other2` varchar(16) NOT NULL, 
    `comments` text NOT NULL, 
    `date_added` date NOT NULL, 
    `date_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    PRIMARY KEY (`id`,`user_id`), 
    KEY `user_id` (`user_id`) 
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ; 
CREATE TABLE `list_`.`seller` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `user_id` tinyint(11) NOT NULL, 
    `file` int(11) DEFAULT NULL, 
    `isClosed` tinyint(1) NOT NULL, 
    `display_limit` int(1) NOT NULL DEFAULT '0', 
    `first_name` varchar(255) NOT NULL, 
    `last_name` varchar(255) NOT NULL, 
    `prop_address` varchar(255) NOT NULL, 
    `city` varchar(255) NOT NULL, 
    `state` varchar(22) NOT NULL, 
    `zip` varchar(5) NOT NULL, 
    `cell_phone` varchar(16) NOT NULL, 
    `home_phone` varchar(16) NOT NULL, 
    `other1` varchar(16) NOT NULL, 
    `other2` varchar(16) NOT NULL, 
    `comments` text NOT NULL, 
    `comment_exist` tinyint(1) NOT NULL, 
    `comment_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 
    `date_added` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 
    `date_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    PRIMARY KEY (`id`,`user_id`), 
    KEY `user_id` (`user_id`) 
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ; 
CREATE TABLE `list_`.`settings` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `user_id` tinyint(11) NOT NULL, 
    `seller_display_limit` int(4) DEFAULT '0', 
    `buyer_display_limit` int(4) DEFAULT '0', 
    `prospect_display_limit` int(4) DEFAULT '0', 
    `property_display_limit` int(4) DEFAULT '0', 
    `date_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    PRIMARY KEY (`id`,`user_id`), 
    KEY `user_id` (`user_id`) 
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ; 
CREATE TABLE `list_`.`users` (
    `id` tinyint(11) NOT NULL AUTO_INCREMENT, 
    `md5_id` varchar(200) NOT NULL, 
    `username` varchar(255) NOT NULL, 
    `password` varchar(255) NOT NULL, 
    `user_level` tinyint(1) DEFAULT '1', 
    `first_name` varchar(200) NOT NULL, 
    `last_name` varchar(200) NOT NULL, 
    `email` varchar(200) DEFAULT NULL, 
    `approved` int(1) NOT NULL, 
    `banned` int(1) NOT NULL, 
    `date_joined` date NOT NULL, 
    `ip` varchar(15) DEFAULT NULL, 
    `activation_code` int(9) DEFAULT NULL, 
    `ckey` varchar(220) NOT NULL, 
    `ctime` varchar(220) NOT NULL, 
    `last_logged_in` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    `account_number` varchar(128) DEFAULT NULL, 
    PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ; 
ALTER TABLE `list_`.`buyer` 
    ADD CONSTRAINT `buyer_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE; 
-- 
-- Constraints for table `company` 
-- 
ALTER TABLE `list_`.`company` 
    ADD CONSTRAINT `company_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE; 
-- 
-- Constraints for table `contacts` 
-- 
ALTER TABLE `list_`.`contacts` 
    ADD CONSTRAINT `contacts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`), 
    ADD CONSTRAINT `group_ibfk_2` FOREIGN KEY (`group`) REFERENCES `groups` (`name`) ON UPDATE CASCADE; 
-- 
-- Constraints for table `groups` 
-- 
ALTER TABLE `list_`.`groups` 
    ADD CONSTRAINT `group_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`); 
-- 
-- Constraints for table `prospect` 
-- 
ALTER TABLE `list_`.`prospect` 
    ADD CONSTRAINT `prospect_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`); 
-- 
-- Constraints for table `seller` 
-- 
ALTER TABLE `list_`.`seller` 
    ADD CONSTRAINT `seller_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`); 
-- 
-- Constraints for table `settings` 
-- 
ALTER TABLE `list_`.`settings` 
    ADD CONSTRAINT `settings_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

回答：

我想你咯误解的Foreign Keys概念。更改组的名称不应该反映在任何其他表上，您只需更改组表。

假设你有这个简单的场景，其中一个Contact只能属于一个Group：

Groups 
id 
group_name 
Contacts 
id 
group_id -> Groups.id 
first_name 
...

你Contacts没有关于group_name的信息。您只需存储对您的Groups.id的引用。

如果你想查询你的接触和组的名称，你加入这两个表：

Select c.first_name, g.group_name 
From contacts c 
Join groups g On (g.id = c.group_id)

如果你想改变一个组的名称，你做一个简单的更新：

Update groups 
Set group_name = 'Your new group name' 
Where id = 99 --# The id of the group to rename

这只会改变你的Groups表，不改变你Contacts。

您对Contacts.group_id外键是有保证的referential integrity。这意味着，如果Groups与id=88中没有记录，则不允许与group_id=88联系。

使用ON DELETE CASCADE将删除属于某个组的所有Contacts，一旦删除该组。

以上是 如何在MySQL中准确设置一个外键方案来完成我的地址簿 的全部内容， 来源链接： utcz.com/qa/263907.html

回到顶部