Web Development

General discussion

Gravatar
0 Votes
Locked

SQL constraint question

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

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

gravatar
Collapse -

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

gravatar
Collapse -

by bradq In reply to

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.

gravatar
Collapse -

by bradq In reply to SQL constraint question

This question was closed by the author

Back to Web Development Forum

Start or search

Related Discussions

Related Forums