Database Design – Check Constraint for Postal Code

constraintdatabase-design

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 (
  PersonID int IDENTITY(1,1) PRIMARY KEY,
  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)
)

Best Answer

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.

Related Question