General discussion

  • Creator
    Topic
  • #2179073

    SQL constraint question

    Locked

    by bradq ·

    Hello, does anyone know how to do this? I’m supposed to for an assignment and dont even know how to begin. I was hoping someone might be able to explain it.

    Using the following CREATE TABLE statements, add AND EXPLICITLY NAME the listed constraints specified for each table. Use the naming convention TableName_ColumnName_ ConstraintTypeAbbreviation for each constraint name. Constraint abbreviations are:

    ? PK for Primary Key
    ? FK for Foreign Key
    ? NN for Not Null.

    To complete the exercise:
    ? All Primary Key constraints should be defined at the table level.
    ? All NOT NULL constraints should be defined at the column level.
    ? All Foreign Key constraints should be defined using the ALERT TABLE statement.

    STUDENT table:

    Add the following constraints to identified columns in the CREATE TABLE statement or the Student table:

    PRIMARY KEY – student_id
    NOT NULL – lname, fname, phone

    CREATE TABLE student
    (
    student_id NUMBER(5)
    lname VARCHAR2(15)
    fname VARCHAR2(15)
    street VARCHAR2(20)
    city VARCHAR2(20)
    state CHAR(2)
    zip NUMBER(9)
    phone NUMBER(10
    );

All Comments

  • Author
    Replies
    • #3118427

      Reply To: SQL constraint question

      by damokay ·

      In reply to SQL constraint question

      Not sure what DB Server you are using so the datatypes may vary but the SQL (MS SQL Server) to create the table with the PK and NN constraints would be as follows:

      CREATE TABLE STUDENT
      (
      student_id int NOT NULL,
      lname varchar(15) NOT NULL,
      fname varchar(15) NOT NULL,
      street varchar(50) NULL,
      city varchar(50) NULL,
      state varchar(50) NULL,
      zip varchar(50) NULL,
      phone numeric(18, 0) NOT NULL
      ) ON [PRIMARY]
      GO

      ALTER TABLE STUDENT ADD CONSTRAINT
      STUDENT_STUDENT_ID_PK PRIMARY KEY CLUSTERED
      (
      student_id
      ) ON [PRIMARY]
      GO

      • #3118314

        Reply To: SQL constraint question

        by bradq ·

        In reply to Reply To: SQL constraint question

        Hi, thanks for the response. I already turned the assignment in but I’ll give you the points anyway. Thats close to what I had except I didnt state NULL’s…just the NOT NULLS. I also didnt have any ON [PRIMARY]’s in mine.

    • #3118313

      Reply To: SQL constraint question

      by bradq ·

      In reply to SQL constraint question

      This question was closed by the author

Viewing 1 reply thread