Starting with MySQL 4.1.2, such an index will be created on the referencing table automatically if it does not exist. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. (or solidDB type if you have solidDB for MySQL). By using the foreign key toolbar, you can create new, edit and delete the selected foreign key field. In the Foreign Keys tab, just simply click a foreign key field for editing. The foreign key can be used to cross-reference tables. ('KBC355','KBC Computers','Glasgow','014') Īfter changing type insert will work without problems.A foreign key is a field in a relational table that matches the primary key column of another table. Supp_id varchar(30) NULL, - You can set length as you wish DROP TABLE suppliers - Will drop your old table I guess you are using InnoDB as table type, in this case query will run as transaction and it will rollback after first error(for this example it will be second row). With your table you can get the error like 'Incorrect Integer Value', but depending on MySQL server configuration it can do conversion(string->int) automatically for your query string must become '0' as result of this it makes 2 rows with 0 as supp_id and get error Duplicate entry '0' for key 'PRIMARY'.

MySQL (and some other databases) allow double quotes, but there is no reason to not use the standard.

Single quotes are ANSI standard for string constants.

I added a unique supplier name to the table and declared the id to be a auto_increment.

Usually you want varchar() rather than char(), unless you really like lots of spaces at the end of strings.

INSERT INTO Suppliers(supp_name, company_name, town, phone) SupplierId int NOT NULL auto_increment primary key, I suspect you want the following code: CREATE TABLE suppliers (

You have another id field that is the primary key, but given no value and not declared auto_increment. Because the leading characters are letters, the value is 0. In older versions of MySQL, I think the string values get silently converted to numbers.

Supp_id is the primary key and declared as a number. In your case, two possibilities come to mind: The insert itself is causing the duplication. This occurs when you have a primary key but do not give it an initialization value.

Navicat for mysql 11.0.3 key