如何在 MySQL 中创建关系

小开

最佳答案

If the tables are innodb you can create it like this:

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;

You have to specify that the tables are innodb because myisam engine doesn't support foreign key. Look here for more info.

小开

Adding onto the comment by ehogue, you should make the size of the keys on both tables match. Rather than

customer_id INT( 4 ) NOT NULL ,

make it

customer_id INT( 10 ) NOT NULL ,

and make sure your int column in the customers table is int(10) also.

小开

as ehogue said, put this in your CREATE TABLE

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

alternatively, if you already have the table created, use an ALTER TABLE command:

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

One good way to start learning these commands is using the MySQL GUI Tools, which give you a more "visual" interface for working with your database. The real benefit to that (over Access's method), is that after designing your table via the GUI, it shows you the SQL it's going to run, and hence you can learn from that.

小开

Certain MySQL engines support foreign keys. For example, InnoDB can establish constraints based on foreign keys. If you try to delete an entry in one table that has dependents in another, the delete will fail.

If you are using a table type in MySQL, such as MyISAM, that doesn't support foreign keys, you don't link the tables anywhere except your diagrams and queries.

For example, in a query you link two tables in a select statement with a join:

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

小开

One of the rules you have to know is that the table column you want to reference to has to be with the same data type as The referencing table . 2 if you decide to use mysql you have to use InnoDB Engine because according to your question that’s the engine which supports what you want to achieve in mysql .

Bellow is the code try it though the first people to answer this question they 100% provided great answers and please consider them all .

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;

小开

Here are a couple of resources that will help get started: http://www.anchor.com.au/hosting/support/CreatingAQuickMySQLRelationalDatabase and http://code.tutsplus.com/articles/sql-for-beginners-part-3-database-relationships--net-8561

Also as others said, use a GUI - try downloading and installing Xampp (or Wamp) which run server-software (Apache and mySQL) on your computer. Then when you navigate to //localhost in a browser, select PHPMyAdmin to start working with a mySQL database visually. As mentioned above, used innoDB to allow you to make relationships as you requested. Makes it heaps easier to see what you're doing with the database tables. Just remember to STOP Apache and mySQL services when finished - these can open up ports which can expose you to hacking/malicious threats.

小开

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).

You have to ask yourself is this a 1 to 1 relationship or a 1 out of many relationship. That is, does every account have a customer and every customer have an account. Or will there be customers without accounts. Your question implies the latter.

If you want to have a strict 1 to 1 relationship, just merge the two tables.

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,
)

In the other case, the correct way to create a relationship between two tables is to create a relationship table.

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
}

Then if you have a customer_id and want the account info, you join on customersaccounts and accounts:

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

Because of indexing this will be blindingly quick.

You could also create a VIEW which gives you the effect of the combined customersaccounts table while keeping them separate

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;

小开

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)
);