Column types do not match in statement ошибка openoffice

Автор Syzygy, 10 мая 2010, 13:33

0 Пользователи и 1 гость просматривают эту тему.

I get this error when I try to generate data for my database:

Column name or number of supplied values does not match table definition

This is the structure of my database:

Create database Newsagents;
USE Newsagents;

CREATE TABLE Client (
ClientID int   NOT NULL, 
Name char(30) NOT NULL,
City char(20) DEFAULT NULL,
Type VARCHAR(15) NOT NULL CHECK (type IN('Individual', 'Company'))
PRIMARY KEY (ClientID)
) ;

CREATE TABLE Product (
ProductNumber char(10) NOT NULL,
ProductName char(20) NOT NULL,
Price float NOT NULL,
isAvailable tinyint NOT NULL,
PRIMARY KEY (ProductNumber)
) ;

CREATE TABLE Sales (
ID INT NOT NULL ,
ClientID INT REFERENCES Client(ClientID),
ProductNumber CHAR(10)  REFERENCES Product(ProductNumber),
Quantity INT NOT NULL,
Price FLOAT NOT NULL ,
Date TIMESTAMP NOT NULL,
PRIMARY KEY ( ID ) 
);

ALTER TABLE sales ADD CONSTRAINT d CHECK (Date > CURRENT_TIMESTAMP);
ALTER TABLE sales ADD CONSTRAINT i CHECK (Quantity > 0);

I than fill my database with some values for Client and Product and I want to generate Sales (using values from Client and Product). This is how I do it:

DECLARE @counter INT
DECLARE @quantity int
DECLARE @prodNum varchar(20)
SET @counter = 0 
WHILE @counter < 10  
BEGIN
SET @quantity = (select FLOOR(RAND()*100))
SET @prodNum = (select TOP 1 ProductNumber from Product Order by NEWID())
insert into Sales values(
   (select TOP 1 ClientID from Client Order by NEWID()),
   (select @prodNum),
   (select @quantity),
   ((select @quantity)*(select TOP 1 Price from Product where ProductNumber = @prodNum)),
  DEFAULT
  )
 SET @counter = @counter + 1 
 END 

However I get the Column name or number of supplied values does not match table definition.
What am I doing wrong?

I get this error when I try to generate data for my database:

Column name or number of supplied values does not match table definition

This is the structure of my database:

Create database Newsagents;
USE Newsagents;

CREATE TABLE Client (
ClientID int   NOT NULL, 
Name char(30) NOT NULL,
City char(20) DEFAULT NULL,
Type VARCHAR(15) NOT NULL CHECK (type IN('Individual', 'Company'))
PRIMARY KEY (ClientID)
) ;

CREATE TABLE Product (
ProductNumber char(10) NOT NULL,
ProductName char(20) NOT NULL,
Price float NOT NULL,
isAvailable tinyint NOT NULL,
PRIMARY KEY (ProductNumber)
) ;

CREATE TABLE Sales (
ID INT NOT NULL ,
ClientID INT REFERENCES Client(ClientID),
ProductNumber CHAR(10)  REFERENCES Product(ProductNumber),
Quantity INT NOT NULL,
Price FLOAT NOT NULL ,
Date TIMESTAMP NOT NULL,
PRIMARY KEY ( ID ) 
);

ALTER TABLE sales ADD CONSTRAINT d CHECK (Date > CURRENT_TIMESTAMP);
ALTER TABLE sales ADD CONSTRAINT i CHECK (Quantity > 0);

I than fill my database with some values for Client and Product and I want to generate Sales (using values from Client and Product). This is how I do it:

DECLARE @counter INT
DECLARE @quantity int
DECLARE @prodNum varchar(20)
SET @counter = 0 
WHILE @counter < 10  
BEGIN
SET @quantity = (select FLOOR(RAND()*100))
SET @prodNum = (select TOP 1 ProductNumber from Product Order by NEWID())
insert into Sales values(
   (select TOP 1 ClientID from Client Order by NEWID()),
   (select @prodNum),
   (select @quantity),
   ((select @quantity)*(select TOP 1 Price from Product where ProductNumber = @prodNum)),
  DEFAULT
  )
 SET @counter = @counter + 1 
 END 

However I get the Column name or number of supplied values does not match table definition.
What am I doing wrong?

Не добавляется foreing key.
Проверил столбцы на null,таблицы пустые,типы подходят,длина тоже
Уже не знаю что делать,завтра курсовая.
Пытаюсь связать prepodName(child) с pr_Fam(parent)
Вот дочерняя таблица

 SHOW CREATE TABLE catec.group
CREATE TABLE `group` (
   `idGroup` int(11) NOT NULL AUTO_INCREMENT,
   `Group_Name` varchar(50) DEFAULT NULL,
   `qualif_Kod` int(11) DEFAULT NULL,
   `prepodName` varchar(100) DEFAULT NULL COMMENT 'Имя препода-куратораn',
   PRIMARY KEY (`idGroup`),
   KEY `fk_qKod_idx` (`qualif_Kod`),
   CONSTRAINT `fk_qKod` FOREIGN KEY (`qualif_Kod`) REFERENCES `qualif` (`qualif_Kod`) ON DELETE NO ACTION ON UPDATE CASCADE
 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

Вот родительская таблица

SHOW CREATE TABLE catec.prepod
CREATE TABLE `prepod` (
   `id_prepod` int(11) NOT NULL AUTO_INCREMENT,
   `pr_fam` varchar(100) NOT NULL DEFAULT '',
   `pr_name` varchar(100) DEFAULT NULL,
   `pr_otch` varchar(100) DEFAULT NULL,
   `pr_IIN` bigint(12) DEFAULT NULL,
   `pr_gender` char(1) DEFAULT NULL,
   `pr_DR` date DEFAULT NULL,
   `pr_Email` varchar(100) DEFAULT NULL,
   `pr_YazObuch` varchar(100) DEFAULT NULL,
   `pr_Grajd` varchar(100) DEFAULT NULL,
   `pr_Kateg` varchar(100) DEFAULT NULL,
   `pr_Natsiya` varchar(100) DEFAULT NULL,
   `pr_Tel` varchar(20) DEFAULT NULL,
   `pr_SubName` varchar(100) DEFAULT NULL,
   `pr_Kurat` char(3) DEFAULT NULL COMMENT 'Кураторство:Да,Нетn',
   PRIMARY KEY (`id_prepod`,`pr_fam`)
 ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='для преподавателей'

Вот и результат

Executing:

ALTER TABLE `catec`.`group` 
ADD INDEX `fk_prepod_Fam_idx` (`prepodName` ASC);
ALTER TABLE `catec`.`group` 
ADD CONSTRAINT `fk_prepod_Fam`
  FOREIGN KEY (`prepodName`)
  REFERENCES `catec`.`prepod` (`pr_fam`)
  ON DELETE SET NULL
  ON UPDATE CASCADE;

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1215: Cannot add foreign key constraint
SQL Statement:
ALTER TABLE `catec`.`group` 
ADD CONSTRAINT `fk_prepod_Fam`
  FOREIGN KEY (`prepodName`)
  REFERENCES `catec`.`prepod` (`pr_fam`)
  ON DELETE SET NULL
  ON UPDATE CASCADE

Также,при команде SHOW engine InnoDB status
выдает следующее:

Cannot find an index in the referenced table where the
 referenced columns appear as the first columns, or column types
 in the table and the referenced table do not match for constraint.
 Note that the internal storage type of ENUM and SET changed in
 tables created with >= InnoDB-4.1.12, and such columns in old tables
 cannot be referenced by such columns in new tables.

I’m trying to create a foreign key in mysql workbench, but keep getting this error:

I’ve checked the columns on both tables, and they match up as well as I can make them. One is CHAR(21) not null primary key, and the other is CHAR(21) not null.

Edit (again): (both tables)

First, the original table that I’m trying to link to:

    CREATE TABLE IF NOT EXISTS `db`.`Employee` (
      `employeeId` CHAR(21) NOT NULL,
      `departmentId` CHAR(21) NULL DEFAULT NULL,
      `chatStatusId` CHAR(21) NOT NULL DEFAULT 'd15a558946afbbd4a6046',
      `employeeNum` INT(4) NOT NULL,
      `payrollNum` INT(11) UNSIGNED NULL DEFAULT '0',
      `title` VARCHAR(50) NULL DEFAULT NULL,
      `dateHired` DATE NOT NULL,
      `terminationDate` DATE NULL DEFAULT NULL,
      `LastLogin` DATETIME NULL DEFAULT NULL,
      `rightMask` INT(11) NOT NULL COMMENT 'we should be using the right table instead',
      `teamId` INT(11) UNSIGNED NULL DEFAULT NULL COMMENT 'this is no longer really used',
      `IsTeamLead` TINYINT(4) NOT NULL DEFAULT '0',
      `bioId` INT(11) UNSIGNED NULL DEFAULT NULL,
      `emailSignature` TEXT NULL DEFAULT NULL,
      `notes` TEXT NULL DEFAULT NULL,
      PRIMARY KEY (`employeeId`),
      UNIQUE INDEX `employeeNum` (`employeeNum` ASC),
      INDEX `departmentId` (`departmentId` ASC),
      INDEX `chatStatusId` (`chatStatusId` ASC),
      INDEX `teamId` (`teamId` ASC),
      CONSTRAINT `Employee_ibfk_1`
        FOREIGN KEY (`employeeId`)
        REFERENCES `db`.`Person` (`personId`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
      CONSTRAINT `Employee_ibfk_10`
        FOREIGN KEY (`teamId`)
        REFERENCES `db`.`HaloTeam` (`teamId`)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT,
      CONSTRAINT `Employee_ibfk_8`
        FOREIGN KEY (`departmentId`)
        REFERENCES `db`.`Department` (`departmentId`),
      CONSTRAINT `Employee_ibfk_9`
        FOREIGN KEY (`chatStatusId`)
        REFERENCES `db`.`ChatStatus` (`chatStatusId`)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_general_ci

And this is the new table I’m trying to create a foreign key on to the original table:

CREATE TABLE IF NOT EXISTS `db`.`ArticleNote` (
  `ArticleNoteID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ArticleID` INT(10) UNSIGNED NOT NULL,
  `Created` TIMESTAMP NOT NULL,
  `employeeId` CHAR(21) NOT NULL,
  `Note` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`ArticleNoteID`),
  INDEX `fk_ANote_Article_idx` (`ArticleID` ASC),
  INDEX `fk_ANote_Employee_idx` (`employeeId` ASC),
  CONSTRAINT `fk_ANote_Article`
    FOREIGN KEY (`ArticleID`)
    REFERENCES `db`.`Article` (`ArticleID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ANote_Employee`
    FOREIGN KEY ()
    REFERENCES `db`.`Employee` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_general_ci

And this is an example of a table that already has a foreign key (using the same column) as the one I’m trying to create in my new table:

CREATE TABLE IF NOT EXISTS `db`.`Article` (
  `ArticleID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ArticleCategoryID` INT(10) UNSIGNED NOT NULL DEFAULT 1,
  `Url` VARCHAR(120) NOT NULL,
  `Title` VARCHAR(60) NOT NULL,
  `Summary` VARCHAR(255) NOT NULL,
  `Image` VARCHAR(200) NULL,
  `StatusIDE` INT(11) NOT NULL,
  `AreaIDE` INT(11) NULL,
  `CreatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CreatedBy` CHAR(21) NULL DEFAULT NULL,
  `ExpiresAt` TIMESTAMP NULL DEFAULT NULL,
  `PayoutAmount` DECIMAL(11,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`ArticleID`),
  INDEX `fk_Aritcle_Category_idx` (`ArticleCategoryID` ASC),
  INDEX `fk_Article_Status_idx` (`StatusIDE` ASC),
  INDEX `fk_Article_Area_idx` (`AreaIDE` ASC),
  INDEX `fk_Article_Employee1_idx` (`CreatedBy` ASC),
  CONSTRAINT `fk_Article_Employee1`
    FOREIGN KEY (`CreatedBy`)
    REFERENCES `db`.`Employee` (`employeeId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Article_ArtCat1`
    FOREIGN KEY (`ArticleCategoryID`)
    REFERENCES `db`.`ArticleCategory` (`ArticleCategoryID`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Article_Status`
    FOREIGN KEY (`StatusIDE`)
    REFERENCES `db`.`DatabaseEnum` (`DatabaseEnumID`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Article_Area`
    FOREIGN KEY (`AreaIDE`)
    REFERENCES `db`.`DatabaseEnum` (`DatabaseEnumID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_general_ci