Creating a table

• joshphillips7145

  Right there with Babe

  Points: 720

  More actions

  August 9, 2012 at 2:06 pm

  #264953

  Ok I need to create Called customers That contain a persons firstName, LastName,

  and social security number. The first and last name are required. The SSN should be in the form 999-99-9999. the ssn should be the primary key.

  I think I am very close here can someone please check to see if this is right?

  /*Dode

  EXEC sp_addtype SSN, 'VARCHAR(11)', 'NOT NULL'

  GO

  Create Table Customers

  (FirstName VarChar(20),

  LastName VarChar(20),

  CustomerSSN SSN Primary key,

  CONSTRAINT CheckSSN CHECK ( CustomerSSN LIKE

  '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]' )

  )

  Go

  */

• Lowell

  SSC Guru

  Points: 323518

  More actions

  August 9, 2012 at 2:11 pm

  #1524090

  looks pretty good; if first and last name are required, i think they need to be NOT NULL, right?

  Create Table Customers

  (FirstName VarChar(20) NOT NULL ,

  LastName VarChar(20)NOT NULL,

  CustomerSSN SSN Primary key,

  CONSTRAINT CheckSSN CHECK ( CustomerSSN LIKE

  '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]' )

  )

  the check contraint worked fien for me, prevented bad input, allowed good data.

  insert into Customers SELECT null,null,1

  insert into Customers SELECT 'bob','mays','111-11-1111'

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• drew.allen

  SSC Guru

  Points: 76988

  More actions

  August 10, 2012 at 7:15 am

  #1524413

  STOP!!!! You're opening yourself up to a very serious security risk. Identity theft is a very big problem and the primary source is SSNs from hacked databases.

  You should not be using SSN to identify your records unless required by law. For one thing, some religious groups have asked to be exempted from the SSN system, so they do not have an SSN. Second, people have the right to refuse to provide their SSN unless required by law for tax purposes. Finally, there are some instances of duplicate SSNs, so they are not guaranteed to be unique.

  If you do store SSNs, they should be encrypted.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA