UNSIGNED NOT NULL mySql

1 Views

I was just wondering, do you have to put UNSIGNED NOT NULL after your foreign key(s) in the table? And what does UNSIGNED actually mean?

// Product table

Read More
id INT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
order_id INT(10) UNSIGNED NOT NULL,
customer_id INT(10) UNSIGNED NOT NULL

// Customer table 

id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(25) NOT NULL,
surname VARCHAR(25) NOT NULL

Do I have to have UNSIGNED after my foreign key or can I leave it out?

Also, does my primary key (in the customer table) have to be named the SAME as my foreign key (in the Product table) to be link, or how do I link them? I know I can use joins, but where do I do this in my php code or sql code? phpmyadmin or php code?

This is for a wordpress plugin

Any help appreciated, don’t really understand this fully yet.

Post Views: 1

Related posts

Leave a Reply

1 comment

  1. Given a 16bit int:

    signed: -32,768 -> +32,767
unsigned: 0 -> 65,535

    If you don’t specify signedness or nullity, then the DB is free to choose its defaults for both, which is generally “signed” and allows nulls.

    key names do NOT have to be the same in the parent and child records. But the definitions of the fields DO have to match exactly. If the definitions don’t match, then the DB will forbid creation of the foreign key.

    e.g.

    create table parent (
   id int unsigned
);

create table child
   bad_fk int signed,       // bad FK. cannot have mixed signed/unsigned
   ok_fk  int unsigned,     // ok, data type definitions match exactly.
   horrible_fk  varchar(16) // total mismatch on type definitions
);