Discussion Locked

SQL constraint question

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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
);

3 replies

by damokay

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

by bradq

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.

This discussion is closed to new replies.