Here's a couple of tips about the code you posted... PLEASE don't take it personally... just thinking that we should get started with some fairly normalized code...
1. You cannot use CHAR as the datatype for an IDENTITY column. It must be BigInt, Int, SmallInt, TinyInt or Decimal with zero decimal places defined.
2. You should always name your keys and constraints on permanent tables. They mean something and so should their names. You really will appreciate the names down the road when you're trying to debug something on a more complicated table.
3. "Type" is not a column name. Province is.
4. "FOR" is part of trigger code, not table creation code.
5. Although I've included the check for Province names as requested, it is very wrong to do that here. You should have a table of Provinces to check against so that when you add another country, you can include some of their geographical boundaries without having to recreate the constraint (and many other reasons, as well. Google "3rd normal form").
6. Although I've included the phone numbers in the table creation as requested, these really belong in a phone number table using the CustomerID as an FK to the Customer table. A secondary lookup table for phone number type is also in order.
7. Although I've included the address info in the table creation as requested, these really belong in an address table using the CustomerID as an FK to the Customer table. A secondary lookup table for address type is also in order.
8. Although I've included the contact info in the table creation as requested, these really belong in an contact table using the CustomerID as an FK to the Customer table. In addition, contacts should also have an address column pointing to the address table because a contact could have a different address than the company. Not sure whether you'll need a contact type table for this but I'd plan on it anyway.
9. Although I've included the Country table as a 10 character column in the table creation as requested, it really belongs in an Country table with the various different ways a country can be named including 2 and 3 character ISO names as well as the "long" name. The Customer table should point to one of those countries.
10. There is absolutely no good reason to prefix a table name with "tbl" although I'm certain some will argue the point to death.
11. Some folks will also take exception to the table being called Customers instead of just Customer... guess it's whichever camp you're from on that one.
And, here's the code you requested... it works, but, as you can see from above, should never be allowed to exist in a production environment.
Code:
CREATE TABLE dbo.tblCustomers
(
CustomerID INT IDENTITY(1,1) NOT NULL ,
CompanyName VARCHAR(40) NOT NULL ,
ContactName VARCHAR(30) NOT NULL ,
ContactTitle VARCHAR(30) NOT NULL ,
Address VARCHAR(60) NOT NULL ,
City VARCHAR(15) NOT NULL ,
Province CHAR(2) NOT NULL ,
PostalCode VARCHAR(10) NOT NULL ,
Country VARCHAR(15) NOT NULL CONSTRAINT DF_tblCustomers_Country DEFAULT ('Canada'),
Phone VARCHAR(24) NOT NULL
Fax VARCHAR(24) NOT NULL ,
CONSTRAINT PK_tblCustomers_CustomerID
PRIMARY KEY CLUSTERED (CustomerID),
CONSTRAINT CK_tblCustomers_Province
CHECK (Province IN ('AB','BC','MB','NB','NL','NT','NS','NU','ON','PE','QC','SK','YT'))
)
GO
--Jeff Moden
Can anyone point out my error please....
