How to create a table in SQL?

Question

I'm creating a table where i have columns: name, address, state, etc
in order to create the zip column i wrote:

create table "people" (bla, bla, bla, ZIP INTEGER )

I want to restrict the zip column to be only 5 characters. How do i do it?

Answer 1

Try executing this command:



Create table people

(bla, bla, bla, zip number(5))

/

Answer 2

Make the ZIP column a Character data type, rather than Integer. With the Character data type, you can specifcy an exact field length, where with Integer it gets a set number of bytes (depending on the specific integer type).



Since you're not going to use the ZIP code field in any math, having it be a Character field is fine.



EDIT - in repsonse to your additional details: Joe said what I was trying to think of, but couldn't quite put words to. The interface where people will actually be entering the data is where you can control what goes into the field. You can't do it at the database table level.



The exact means you use to control the data entry depends on what interface you'll be using. For example, in an Access form (which I've worked with a lot) it's called an input mask, and there's a built-in one for ZIP codes which restricts the user to entering only 0 through 9 (numeric characters only). In order to have the field be a specific length, you need to use the Varchar data type, and then have the entry interface prevent users from entering garbage data.

Answer 3

You can't do it the way you're suggesting without using a varchar. Integer has a pre-defined length and you cannot change its size.

The proper way to limit the size and restrict the value of the data being input is to handle it in the user interface (application layer), not in the database layer. The control that handles the zip code input is where this needs to happen.

Answer 4

Besides what other responders have mentioned, by making the zip integer you cannot store non-US zips. Of course if you know for absolute certainty you're dealing just with US addresses (and don't want to allow zip+4), that's fine. If so, you could go ahead with making it integer and add a CHECK contraint to limit values between 00001 and 99999.

Answer 5

In MS-sq. Server Integer's don't have sizes as such. it incredibly is labored out from the information form. in case you prefer to alter the dimensions use Bigint, smallint or tinyint. you will need the no longer NULL in case you haven't any longer have been given a default fee...

Answer 6

mysql_query("CREATE TABLE `dbname`.`tablename` ("

. " `name` varchar(30) NOT NULL ,"

. " `address` varchar(30) NOT NULL ,"

. " `state` varchar(50) NOT NULL ,"

. " `zip` varchar(5) NOT NULL ,"

. " PRIMARY KEY (`name`) "

. ") ENGINE = MYISAM DEFAULT CHARSET = latin1;") or die('Error, query failed. ' . mysql_error());