Join or sign in Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below. Use Your Email Use FacebookUse Linkedin

SQL constraint question

Locked

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

Topic

Reply To: SQL constraint question

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

Reply To: SQL constraint question

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.

Reply To: SQL constraint question

In reply to SQL constraint question

This question was closed by the author

[Author]

Replies