Question

considering all the other tables exist in the database, which of the following sql statements that are used to create tables gives an error? select one: a. create table instructor( id integer not null, name varchar (40), surname varchar (40), primary key (id), unique (id) ); b. create table instructor( id integer not null, name varchar (40), surname varchar (40), constraint pk primary key (id) ); c. create table instructor( id integer not null, name varchar (40), surename varchar (40), primary key (id), foreign key (name) );

Answer 1

c. create table instructor( id integer not null, name varchar (40), surename varchar (40), primary key (id), foreign key (name) );

In the statement c, the instruction "foreign key (name)" is giving an error because the "name" column is not a primary key or unique key in any other table, so it cannot be used as a foreign key. A foreign key is used to create a link between two tables, it references the primary key of another table to establish a relationship. Thus, the column referred to by the foreign key must exist in the referenced table, and it must be a primary key or unique key.

The statement a and b will not give any error because the primary key is defined correctly and the unique key is also defined correctly. In statement a, the constraint is explicitly given a name "unique (id)", and in statement b, the constraint is given a name "pk" and defined as "primary key (id)".

It is important to note that primary key and unique key constraint can be defined in the column level or table level, and it can have one or multiple columns, but the foreign key constraint can only be defined in the column level and it refers to a single column in the referenced table.