如何在MySQL中创建关系

如果表是innodb,您可以这样创建它:

CREATE TABLE accounts(
    account_id INT NOT NULL AUTO_INCREMENT,
    customer_id INT( 4 ) NOT NULL ,
    account_type ENUM( 'savings', 'credit' ) NOT NULL,
    balance FLOAT( 9 ) NOT NULL,
    PRIMARY KEY ( account_id ), 
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) 
) ENGINE=INNODB;

您必须指定表是innodb,因为myisam引擎不支持外键。看 here 更多信息。

正如ehogue所说,将其放在CREATE表中

FOREIGN KEY (customer_id) REFERENCES customers(customer_id) 

或者,如果已经创建了表,请使用ALTER table命令:

ALTER TABLE `accounts`
  ADD CONSTRAINT `FK_myKey` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) ON DELETE CASCADE ON UPDATE CASCADE;

开始学习这些命令的一个好方法是使用 MySQL GUI Tools ,这为您提供了一个更“可视化”的界面,用于处理数据库。这种方法的真正好处(相对于Access的方法)是,在通过GUI设计表之后,它会向您显示它将要运行的SQL,因此您可以从中学习。

CREATE TABLE accounts(
    account_id INT NOT NULL AUTO_INCREMENT,
    customer_id INT( 4 ) NOT NULL ,
    account_type ENUM( 'savings', 'credit' ) NOT NULL,
    balance FLOAT( 9 ) NOT NULL,
    PRIMARY KEY ( account_id )
)

and

CREATE TABLE customers(
    customer_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    address VARCHAR(20) NOT NULL,
    city VARCHAR(20) NOT NULL,
    state VARCHAR(20) NOT NULL,
)

How do I create a 'relationship' between the two tables? I want each account to be 'assigned' one customer_id (to indicate who owns it).

如果您想要有一个严格的1对1关系,只需合并这两个表。

CREATE TABLE customers(
    customer_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    address VARCHAR(20) NOT NULL,
    city VARCHAR(20) NOT NULL,
    state VARCHAR(20) NOT NULL,
    account_type ENUM( 'savings', 'credit' ) NOT NULL,
    balance FLOAT( 9 ) NOT NULL,
)

CREATE TABLE customersaccounts(
    customer_id INT NOT NULL,
    account_id INT NOT NULL,
    PRIMARY KEY (customer_id, account_id)
    FOREIGN KEY customer_id references customers (customer_id) on delete cascade,
    FOREIGN KEY account_id  references accounts  (account_id) on delete cascade
}

然后,如果您有客户id并需要帐户信息,请加入CustomerAccounts和accounts:

SELECT a.*
    FROM customersaccounts ca
        INNER JOIN accounts a ca.account_id=a.account_id
            AND ca.customer_id=mycustomerid;

您还可以创建一个视图,该视图提供组合CustomerAccounts表的效果,同时将它们分开

CREATE VIEW customeraccounts AS 
    SELECT a.*, c.* FROM customersaccounts ca
        INNER JOIN accounts a ON ca.account_id=a.account_id
        INNER JOIN customers c ON ca.customer_id=c.customer_id;

添加到ehogue的注释中,应该使两个表上的键大小匹配。而不是

customer_id INT( 4 ) NOT NULL ,

customer_id INT( 10 ) NOT NULL ,

并确保customers表中的int列也是int(10)。

某些MySQL引擎支持外键。例如,InnoDB可以基于外键建立约束。如果尝试删除一个表中的某个条目,而该条目在另一个表中具有从属项,则删除操作将失败。

例如,在查询中,您使用联接将select语句中的两个表链接起来:

SELECT a, b from table1 LEFT JOIN table2 USING (common_field);

以下是一些有助于入门的资源: http://www.anchor.com.au/hosting/support/CreatingAQuickMySQLRelationalDatabase http://code.tutsplus.com/articles/sql-for-beginners-part-3-database-relationships--net-8561

然后,当您在浏览器中导航到//localhost时,选择PHPMyAdmin以直观地开始使用mySQL数据库。如上所述,使用innoDB可以根据需要建立关系。使堆更容易查看您对数据库表所做的操作。请记住在完成时停止Apache和mySQL服务-这些服务可能会打开端口,使您面临黑客/恶意威胁。

引用表。2如果你决定使用mysql,你必须使用InnoDB引擎,因为根据你的问题,这是支持你想要在mysql中实现的目标的引擎。

下面是代码,尽管第一个回答这个问题的人尝试一下

CREATE TABLE accounts(
    account_id INT NOT NULL AUTO_INCREMENT,
    customer_id INT( 4 ) NOT NULL ,
    account_type ENUM( 'savings', 'credit' ) NOT NULL,
    balance FLOAT( 9 ) NOT NULL,
    PRIMARY KEY (account_id)
)ENGINE=InnoDB;

CREATE TABLE customers(
    customer_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    address VARCHAR(20) NOT NULL,
    city VARCHAR(20) NOT NULL,
    state VARCHAR(20) NOT NULL,
     PRIMARY KEY ( account_id ), 
FOREIGN KEY (customer_id) REFERENCES customers(customer_id) 
)ENGINE=InnoDB; 

create table departement(
    dep_id      int primary key auto_increment,
    dep_name    varchar(100) not null,
    dep_descriptin      text,
    dep_photo       varchar(100) not null,
    dep_video       varchar(300) not null
);

create table newsfeeds(
    news_id         int primary key auto_increment,
    news_title      varchar(200) not null,
    news_description    text,
    news_photo          varchar(300) ,
    news_date           varchar(30) not null,
    news_video          varchar(300),
    news_comment        varchar(200),
    news_departement    int foreign key(dep_id) references departement(dep_id)
);