主页 / user-40950

Jakubee's questions

Martin Hope
Jakubee
Asked: 2019-11-15 03:25:36 +0800 CST
  • 0

我在两个 Windows 服务器上有 2 个 SQL Server 实例，即：

  • D-MSSQL01-DE (v.2014)
  • D-MSSQL02-DE\SQL2019 (v.2019)

并为 AG 提供单独的服务器：

  • D-MSSQL02-DE (v.2014)
  • D-MSSQL02-DE\SQL2019 (v.2019)

听众：

  • D-AGSQL01-PP（v.2014 监听器）
  • D-AGSQL04-PP（v.2019 监听器）

我在设置新的 AG 之后，它是 SQL Server 2019 上的侦听器，具有新的 IP：networkteam 分配的端口。现在，当我连接到这个新创建的侦听器时，不知何故在“始终处于高可用性”下，我看到了 2014 AG 实例！为什么这个连接到 SQL2014 而不是 2019？

在此处输入图像描述

sql-server availability-groups
Martin Hope
Jakubee
Asked: 2015-02-21 08:43:21 +0800 CST
  • 0

我的问题是我不知道如何从给定集中选择随机数据。例如：我有一个 Node 列需要生成不同的插入，但只能从这些数字：16、17、18、22、24。我看过很多不同的帖子，但找不到我要找的东西。任何提示、提示或非常简单的示例查询将不胜感激。

我正在使用 SQL Server Management Studio 2014

sql-server random
Martin Hope
Jakubee
Asked: 2014-06-13 07:01:48 +0800 CST
  • 2

我的错误代码是 1215，它发现外键约束有问题。我知道所有字段都是相同的数据类型。我看不出问题出在哪里。任何帮助将非常感激！谢谢！

这是ERD：在此处输入图像描述

和架构：

=== 交易表 ===

CREATE TABLE IF NOT EXISTS `test`.`Transaction` (
  `transaction_id` SMALLINT(5) NOT NULL,
  `customer_id` SMALLINT NULL,
  `copy_id` DECIMAL(5) NOT NULL,
  `transaction_date` DATE NOT NULL,
  `transaction_amount` DECIMAL(3,2) NULL,
  `transaction_comment` VARCHAR(60) NULL,
  `previous_transaction_id` SMALLINT(5) NOT NULL,
  `transaction_type_code` VARCHAR(10) NOT NULL,
  `employee_emp_id` TINYINT NOT NULL,
  INDEX `fk_Copy_Id_idx` (`copy_id` ASC),
  INDEX `fk_Transaction_Transaction_Type1_idx` (`transaction_type_code` ASC),
  PRIMARY KEY (`transaction_id`),
  INDEX `fk_Previous_Transaction_Id_idx` (`previous_transaction_id` ASC),
  INDEX `fk_Transaction_Emp_idx` (`employee_emp_id` ASC),
  INDEX `fk_Transaction_Customer_Id_idx` (`customer_id` ASC),
  CONSTRAINT `fk_Copy_Id`
    FOREIGN KEY (`copy_id`)
    REFERENCES `test`.`Customer_Rentals` (`copy_id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Previous_Transaction_Id`
    FOREIGN KEY (`previous_transaction_id`)
    REFERENCES `test`.`Transaction` (`transaction_id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Transaction_Type_Code`
    FOREIGN KEY (`transaction_type_code`)
    REFERENCES `test`.`Transaction_Type` (`transaction_type_code`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Transaction_Emp`
    FOREIGN KEY (`employee_emp_id`)
    REFERENCES `test`.`Employee` (`emp_Id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Transaction_Customer_Id`
    FOREIGN KEY (`customer_id`)
    REFERENCES `test`.`Customer` (`custumer_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

=== 客户租金 ===

CREATE TABLE IF NOT EXISTS `test`.`Customer_Rentals` (
  `copy_id` DECIMAL(5) NOT NULL,
  `customer_id` SMALLINT NOT NULL,
  `movie_id` SMALLINT NULL,
  `status_code` VARCHAR(10) NOT NULL,
  `date_out` DATETIME NOT NULL,
  `date_returned` DATETIME NOT NULL,
  `amount_due` DECIMAL(4,2) NOT NULL DEFAULT 00.00,
  PRIMARY KEY (`copy_id`),
  INDEX `fk_Customer_Id_idx` (`customer_id` ASC),
  INDEX `fk_Movie_Id_idx` (`movie_id` ASC),
  INDEX `fk_Rent_Status_Code_idx` (`status_code` ASC),
  CONSTRAINT `fk_Customer_Id`
    FOREIGN KEY (`customer_id`)
    REFERENCES `test`.`Customer` (`custumer_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Movie_Id`
    FOREIGN KEY (`movie_id`)
    REFERENCES `test`.`Movie` (`movie_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Rent_Status_Code`
    FOREIGN KEY (`status_code`)
    REFERENCES `test`.`Rent_Status` (`rental_status_code`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

=== 交易类型表 ===

CREATE TABLE IF NOT EXISTS `test`.`Transaction_Type` (
  `transaction_type_code` VARCHAR(10) NOT NULL,
  `transaction_type_desc` ENUM('Payment','Refund') NULL,
  PRIMARY KEY (`transaction_type_code`),
  INDEX `transaction_type_code` (`transaction_type_code` ASC))
ENGINE = InnoDB;

=== 员工 ===

CREATE TABLE IF NOT EXISTS `test`.`Employee` (
  `emp_Id` TINYINT NOT NULL AUTO_INCREMENT,
  `emp_DoB` DATE NULL,
  `first_name` VARCHAR(45) NOT NULL,
  `last_name` VARCHAR(45) NULL,
  `hire_date` DATE NOT NULL,
  PRIMARY KEY (`emp_Id`))
ENGINE = InnoDB;

=== 客户表 ===

CREATE TABLE IF NOT EXISTS `test`.`Customer` (
  `custumer_id` SMALLINT NOT NULL AUTO_INCREMENT,
  `membership` VARCHAR(15) NOT NULL,
  `name` VARCHAR(20) NOT NULL,
  `surname` VARCHAR(20) NOT NULL,
  `address` VARCHAR(45) NOT NULL,
  `custumer_email` VARCHAR(45) NOT NULL,
  `dataOfBirth` DATE NULL,
  `sex` TINYTEXT NULL,
  `dateOfRegistration` DATETIME NOT NULL,
  `active` TINYINT(1) NOT NULL,
  PRIMARY KEY (`custumer_id`),
  INDEX `lastname` (`surname` ASC))
ENGINE = InnoDB;

======================= 编辑=========================== =====

似乎外键选项不能未定义！

CONSTRAINT `fk_Rent_Status_Code`
    FOREIGN KEY (`status_code`)
    REFERENCES `test`.`Rent_Status` (`rental_status_code`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)

必须改为：

CONSTRAINT `fk_Rent_Status_Code`
    FOREIGN KEY (`status_code`)
    REFERENCES `test`.`Rent_Status` (`rental_status_code`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
mysql foreign-key