MYSQL外键-T-SQL

/***建立外键***/

CREATE TABLE `persons` (

  `Id_P` INT NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`Id_P`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


CREATE TABLE `group` (
  `group_id` int NOT NULL AUTO_INCREMENT,
  `group_name` varchar(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


CREATE TABLE Orders
(
 Id_O int NOT NULL AUTO_INCREMENT,
 Id_P INT,
/*`group_id` int unsigned,*/
PRIMARY KEY (Id_O),
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
/*FOREIGN KEY (group_id) REFERENCES `group`(group_id)*/
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;


ALTER TABLE company_detail_url ADD INDEX index_status(status)




/*
* The list of all members in the community
*/
DROP TABLE IF EXISTS members;
CREATE TABLE members
(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(30),
steamID3 INT NOT NULL UNIQUE,
PRIMARY KEY (id)
)ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;


/*
* Lists all servers in operation
*/
DROP TABLE IF EXISTS servers;
CREATE TABLE servers
(
sid INT NOT NULL AUTO_INCREMENT,
sname VARCHAR(30), -- name of server (short name)
PRIMARY KEY(sid)
)ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;


/*
* flags are used to give custom permissions to players.
* For example a record may be: (0, "VIP", "This play is very important")
*/
DROP TABLE IF EXISTS flags;
CREATE TABLE flags
(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(10),
description VARCHAR(100),
PRIMARY KEY(id)
)ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;




/*
* Maps a member and a server together along with a flag. 
* Record (0, 12, 1, 0) indicates member 12 has flag 0 on server 1.
*/
DROP TABLE IF EXISTS serverPermissions;
CREATE TABLE serverPermissions
(
mid INT,
sid INT,
flagid INT,
PRIMARY KEY(mid, sid, flagid),
FOREIGN KEY (mid) REFERENCES members(id),
FOREIGN KEY (sid) REFERENCES servers(sid),
FOREIGN KEY (flagid) REFERENCES flags(id)
)ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;








INSERT INTO persons (Id_P,name) VALUES(3,'tets')


INSERT INTO Orders (Id_P) VALUES(3)


delete from persons WHERE Id_P = 3
UPDATE persons SET Id_P = 6 WHERE Id_P = 3
 
ALTER TABLE Orders ADD CONSTRAINT orders_fk_test FOREIGN KEY (Id_P) REFERENCES persons(Id_P) ON DELETE CASCADE ON UPDATE CASCADE

ALTER TABLE Orders DROP FOREIGN KEY orders_ibfk_2