Hi. I created this database to just to sharpen my skill. I have one Question - when I create I make the FOREIGN KEY personID in Addresses table but I changed it to be AddressID in Persons table because I can have a list of Addresses. When I create program is that fine? I create check constraint to check the canadian postal code is my constraint right ?

CREATE TABLE dbo.Addresses(
  AddressID int IDENTITY(1,1) PRIMARY KEY,
  Street varchar(255),
  City varchar(255),
  Province varChar(255),
  PostalCode varchar(10) CHECK(PostalCode>='t1w1v1' and PostalCode<='t9w9v9')  

CREATE TABLE dbo.Persons (
  FirstName varchar(255) NOT NULL,
  LastName varchar(255) NOT NULL,
  MiddleName varchar(255) null,
  DateOfBirth date NOT NULL,
  Gender char(1) NULL CHECK(Gender='M' OR Gender='F'),
  AddressID int FOREIGN KEY REFERENCES dbo.Addresses(AddressID)

1 Answer 1


Yes, that would be fine if your design goal is that Persons have only 1 Addresses. With this design, each Persons can have 1 Addresses but two or more Persons can have the same Addresses.

It's all a matter of your business needs. If the above is what you are trying to get, then yes, it is correct.

However, I think it's most common the other way around where the Addresses would have a foreign key to the Persons because a Persons could have more than one Addresses.

As for your constraint to check the postal code - well first off you are missing the space and it's lower case. Whether that will work will depend on which database system you are using. I tested it with PostgreSQL and it does not work.

I don't think you can really have such a simple constraint to fully validate a Canadian postal code. For example, there are some letters and some numbers which are never used. I'm a bit rusty on my Canadian postal office codes but I seem to recall that number 5 is never used as it's too similar to S, etc.

  • 2
    The best way to confirm postal codes is to compare the user input to a known-good list of valid codes. There are many sources on the Internet for Canadian postal codes that are update periodically and are not terribly cost prohibitive. The advantage here would be you can fill out the city and province automatically for the user (or at least validate them).
    – Hannah Vernon
    Feb 8, 2014 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.