Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2010
    Posts
    3

    Unanswered: Enhancement SQL Help,

    I am trying to Create tables, with Enhancements

    Code:
    CREATE TABLE Employee
                 (empNo  	VARCHAR(4)    NOT NULL,
                  fName	VARCHAR(25),
                  lName	VARCHAR(20),
    		    address VARCHAR(45),
    			DOB smallint,
                   Sex char NOT NULL
                       CHECK(sex IN('M','F')),
                   position VARCHAR NOT NULL
    CHECK(position IN('MAanger', 'Team Leader', 'Analyst', 'Software Developer')),
                   deptNo VARCHAR(10),
                
    	     
    PRIMARY KEY (EmpNo));
    
    CREATE TABLE Department
    
                 (deptNo   VARCHAR(10)	NOT NULL,
    	      deptName	VARCHAR(50),
    	      mgrEmpNo VARCHAR(35),
                  
                 
    PRIMARY KEY (deptNo));
    
    
    CREATE TABLE Project
    
                 (projNo  VARCHAR(24)	NOT NULL,
    	      projName	VARCHAR(40),
    	      deptNo	VARCHAR(10),
                            
    PRIMARY KEY (projNo));
    
    
    CREATE TABLE WorksOn
    
                 (empNo  		VARCHAR(4)	NOT NULL,
    	      projNo	VARCHAR(10),
    	      dateWorked	dateworked,
               hoursWorked VARCHAR(10),
                           
    PRIMARY KEY (empNo, projNo, dateWorked));
    
    Commit;
    Is this Right to only have the user Enter either M or F for Sex and for Work position only to have the only following

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1. you ~must~ supply a length for CHAR and VARCHAR columns
    2. mysql doesn't support CHECK constraints

    fix those and try again, and please show your error messages
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2010
    Posts
    3
    ok, if MySql does not support check constraints is their any other way to only allow users to enter M or F for Sex and Manager Team Leader etc. in SQL looking over google and cant seem to find anything,

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by cork24 View Post
    ...is their any other way to only allow ...
    absolutely, yes -- use a foreign key
    Code:
    CREATE TABLE Sexes
    ( Sex CHAR(1) NOT NULL PRIMARY KEY
    );
    INSERT INTO Sexes VALUES ('M'),('F')
    ;
    
    CREATE TABLE Positions
    ( Position VARCHAR(37) NOT NULL PRIMARY KEY
    );
    INSERT INTO Positions VALUES 
     ('Manager')
    ,('Team Leader')
    ,('Analyst')
    ,('Software Developer')
    ;
    
    CREATE TABLE Departments
    ( deptNo  VARCHAR(10) NOT NULL PRIMARY KEY
    , deptName VARCHAR(50)
    , mgrEmpNo VARCHAR(35)
    );
    
    CREATE TABLE Employee 
    ( empNo VARCHAR(4) NOT NULL PRIMARY KEY
    , fName VARCHAR(25)
    , lName VARCHAR(20)
    , address VARCHAR(45)
    , DOB DATE
    , Sex CHAR(1) NOT NULL 
          REFERENCES Sexes ( Sex )
    , position VARCHAR(37) NOT NULL
          REFERENCES Positions ( position )
    , deptNo VARCHAR(10)
          REFERENCES Departments ( deptNo )
    );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2010
    Posts
    3
    Thank you will set this up. and take out the CREATE IN class.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •