SQL Missing left parenthesis? SQL?

Question

Hi,

Im having trouble inserting this small SQL statement:

alter table QUALIFICATION add
constraint ROLE_QUAL CHECK DRIVERID IN(SELECT DRIVERID FROM STAFF);

It just checks that the DRIVERID in the QUALIFICATION table exists in the STAFF table

but it keeps returning an error message saying

"Missing left parenthesis ORA-00906"

however if i enter the values that exists in the STAFF table it accepts fine

alter table QUALIFICATION add constraint ROLE_QUAL CHECK (CLASS IN ('D001','D002','D003','D004'));

PLEASE HELP ME OUT ITS MAKING ME GO MAD!!!

Answer 1

As the other responder mentions, it's probably more appropriate if you make this a foreign key constraint to begin with. However, that aside, the problem is that your check criteria should all be within parenthesis like so:



constraint ROLE_QUAL CHECK (DRIVERID IN (SELECT DRIVERID FROM STAFF));



(One problem with doing it like this rather than a proper foreign key constraint is that, while it handles things when the QUALIFICATION table is modified, it doesn't if the STAFF table is changed - think about what happens if you go into STAFF and delete an entry...a bunch of rows in QUALIFICATION might now be invalid, but you wouldn't know about it at the time. A foreign key can handle that thru what's called referencial integrity rules, by specifying just what to do in such cases.)

Answer 2

Why bother with that constraint? Just make your QUALIFICATION.dbo.DRIVERID a Foreign Key? If you set the DriverID in STAFF as a Primary Key, you can set the DriverID from Qualification as a foreign key. This Foreign Key/PK relationship will do all those checks for you automatically.

Answer 3

Ora-00906

Answer 4

I don't see a matching paren for the first line: CREATE TABLE CUSTOMER( You never close that off...