I have a table:  CREATE TABLE methods (     method_id serial PRIMARY KEY,     method_name varchar(100) );   I now want to create a table with the following columns:  CREATE TABLE experiments  (     method integer[] REFERENCES methods(method_id),     trials integer );   I get an error:     Key columns "method" and "method_id" are of incompatible types: integer[] and integer.   I understand that the columns have to be the same type and I also saw that some tried to tackle this foreign key on array issue already:     My interest is that 'method' should be an array of integers referencing 'method_id' from table 'methods' but I can't figure it out. I thought that the link above might be a solution, but seems that was not implemented (?)  Some posts propose using junction/join tables:     I am an absolute beginner and I could not figure it out yet. For me, it is a matter of understanding first how to tackle this issue of a foreign key on an array. Multiple methods can be considered as forming a class of methods. Each class can have a certain number of trials.
121398
Suppose I have a table containing job roles:  CREATE TABLE roles (   "role" character varying(80) NOT NULL,   CONSTRAINT "role" PRIMARY KEY (role) );   Suppose I further have a table, users, and each row (a specific user) can have an arbitrary number of job roles:  CREATE TABLE users (   username character varying(12) NOT NULL,   roles character varying(80)[] NOT NULL,   CONSTRAINT username PRIMARY KEY (username) );   I should probably make sure that each member of users.roles[] exists in roles.role.  It seems to me that what I want is a foreign key constraint on each member of users.roles[] such that if references roles.role.  This doesn't seem possible with postgres.  Am I looking at this the wrong way?  What is the suggested "right" way to handle this?
68396
I want a way to establish which columns in a given database are joined via PK/FK relationships. I can return the PK/FK information for a given table via   SELECT *   FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cu  WHERE EXISTS (     SELECT tc.*      FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc      WHERE tc.CONSTRAINT_CATALOG = 'MyDatabase'           AND tc.TABLE_NAME = 'MyTable'           /*AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'*/         AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME); GO   but for a PK returned from such a query how do I establish the associated FK (assuming there is one)?  I know you can also get the referenced tables via:  SELECT CONSTRAINT_NAME = name,         FOREIGN_SCHEMA = OBJECT_SCHEMA_NAME(parent_object_id),         FOREIGN_TABLE = OBJECT_NAME(parent_object_id),         REFERENCED_SCHEMA = OBJECT_SCHEMA_NAME(referenced_object_id),         REFERENCED_TABLE = OBJECT_NAME(referenced_object_id)  FROM sys.foreign_keys WHERE OBJECT_NAME(referenced_object_id) = 'MyTable'; GO   but I am struggling now to get the explicit column references.  I am creating a script generator for QlikView. To generate the script I need the constraints and the associated links. I need all of the constraint information for any given column (if any).  I want to construct a database class that holds all the information for a given database. This class structure database.table.column.constraints will then be used to get the matches between different columns on PK/FKs.  Clearly some columns will have FKs only and in this case I also want to retrieve the PK information of the corresponding key; some will have only PKs and then I want the reverse. Some of course can have both.
10493
Do you know a good source to learn how to design SQL solutions?  Beyond the basic language syntax, I'm looking for something to help me understand:   What tables to build and how to link them How to design for different scales (small client APP to a huge distributed website) How to write effective / efficient  / elegant SQL queries
59811
Here is the script to create my tables:   CREATE TABLE clients (    client_i INT(11),    PRIMARY KEY (client_id) ); CREATE TABLE projects (    project_id INT(11) UNSIGNED,    client_id INT(11) UNSIGNED,    PRIMARY KEY (project_id) ); CREATE TABLE posts (    post_id INT(11) UNSIGNED,    project_id INT(11) UNSIGNED,    PRIMARY KEY (post_id) );   In my PHP code, when deleting a client, I want to delete all projects posts:  DELETE  FROM posts INNER JOIN projects ON projects.project_id = posts.project_id WHERE projects.client_id = :client_id;   The posts table does not have a foreign key client_id, only project_id. I want to delete the posts in projects that have the passed client_id.  This is not working right now because no posts are deleted.
1458
I tried running the following statement:  INSERT INTO VOUCHER (VOUCHER_NUMBER, BOOK_ID, DENOMINATION) SELECT (a.number, b.ID, b.DENOMINATION)  FROM temp_cheques a, BOOK b WHERE a.number BETWEEN b.START_NUMBER AND b.START_NUMBER+b.UNITS-1;   which, as I understand it, should insert into VOUCHER each record from temp_cheques with the ID and DENOMINATION fields corresponding to entries in the BOOK table (temp_cheques comes from a database backup, which I'm trying to recreate in a different format). However, when I run it, I get an error:  Error: Operand should contain 1 column(s) SQLState:  21000 ErrorCode: 1241   I'm running this in SQuirrel and have not had issues with any other queries. Is there something wrong with the syntax of my query?  EDIT:  The structure of BOOK is:  ID  int(11) START_NUMBER    int(11) UNITS   int(11) DENOMINATION    double(5,2)   The structure of temp_cheques is:  ID  int(11) number  varchar(20)
12713
I ran into the problem that my primary key sequence is not in sync with my table rows.   That is, when I insert a new row I get a duplicate key error because the sequence implied in the serial datatype returns a number that already exists.  It seems to be caused by import/restores not maintaining the sequence properly.
28828
I am pretty well versed with SQL Server, MySQL, Oracle etc but putting these Database products aside, is there a resource that will help me design relational databases well? Is there something like patterns or best practices for database design?  I have seen a few times that database is often not scalable; people have personal preferences with keeping columns like isChecked column which is boolean in nature but stored as Char(1) with values like 'Y' and 'N' instead of 0 and 1 which to me sounds better. Ways not to commit common mistakes while doing database design?  Links to books or articles will be highly appreciated.  Thanks in advance.
40421
What are the best practices for modeling inheritance in databases?  What are the trade-offs (e.g. queriability)?  (I'm most interested in SQL Server and .NET, but I also want to understand how other platforms address this issue.)
25040
I have to create a report on some student completions. The students each belong to one client. Here are the tables (simplified for this question).  CREATE TABLE  `clients` (   `clientId` int(10) unsigned NOT NULL auto_increment,   `clientName` varchar(100) NOT NULL default '',   `courseNames` varchar(255) NOT NULL default '' )   The courseNames field holds a comma-delimited string of course names, eg "AB01,AB02,AB03"  CREATE TABLE  `clientenrols` (   `clientEnrolId` int(10) unsigned NOT NULL auto_increment,   `studentId` int(10) unsigned NOT NULL default '0',   `courseId` tinyint(3) unsigned NOT NULL default '0' )   The courseId field here is the index of the course name in the clients.courseNames field. So, if the client's courseNames are "AB01,AB02,AB03", and the courseId of the enrolment is 2, then the student is in AB03.  Is there a way that I can do a single select on these tables that includes the course name? Keep in mind that there will be students from different clients (and hence have different course names, not all of which are sequential,eg: "NW01,NW03")  Basically, if I could split that field and return a single element from the resulting array, that would be what I'm looking for. Here's what I mean in magical pseudocode:  SELECT e.`studentId`, SPLIT(",", c.`courseNames`)[e.`courseId`] FROM ...
50706
I am trying to make a table with 10 columns.  For some reason, when I add an 11th column, the table no longer compiles.    Below is, I believe, a minimum working example.  Specifically, the table works with    \begin{tabular}{l*9{c}}   but it does not work with   \begin{tabular}{l*10{c}}   Note that the following ONLY compiles because it is the version with the 9 rather than the 10.  I would like for it to have the 10 so I can insert an extra column.  My tables begin as:  \documentclass{article} \usepackage{array} \begin{document}  \begin{tabular}{l*10{c}}  &amp; (1) &amp; (2) &amp; (3) &amp; (4) &amp; (5) &amp; (6) &amp; (7) &amp; (8) &amp; (9) &amp; (10) \\   \end{tabular}  \end{document}
5970
Well here's my problem I have three tables; regions, countries, states.  Countries can be inside of regions, states can be inside of regions.  Regions are the top of the food chain.  Now I'm adding a popular_areas table with two columns; region_id and popular_place_id.  Is it possible to make popular_place_id be a foreign key to either countries OR states.  I'm probably going to have to add a popular_place_type column to determine whether the id is describing a country or state either way.
101615
I have a table like the following:  create table my_table (     id   int8 not null,     id_A int8 not null,     id_B int8 not null,     id_C int8 null,     constraint pk_my_table primary key (id),     constraint u_constrainte unique (id_A, id_B, id_C) );   And I want (id_A, id_B, id_C) to be distinct in any situation. So the following two inserts must result in an error:  INSERT INTO my_table VALUES (1, 1, 2, NULL); INSERT INTO my_table VALUES (2, 1, 2, NULL);   But it doesn't behave as expected because according to the documentation, two NULL values are not compared to each other, so both inserts pass without error.  How can I guarantee my unique constraint even if id_C can be NULL in this case? Actually, the real question is: can I guarantee this kind of uniqueness in "pure sql" or do I have to implement it on a higher level (java in my case)?
85405
I’m having some trouble in recreating a database with a one-to-many (1:M) relationship between Users and Items. This is pretty straightforward, yes; however, each Item belongs to a certain Category (e.g., a Car, a Boat or a Plane), and each Category has a particular number of attributes, e.g.: Car structure: +----+--------------+--------------+ | PK | Attribute #1 | Attribute #2 | +----+--------------+--------------+  Boat structure: +----+--------------+--------------+--------------+ | PK | Attribute #1 | Attribute #2 | Attribute #3 | +----+--------------+--------------+--------------+  Plane structure: +----+--------------+--------------+--------------+--------------+ | PK | Attribute #1 | Attribute #2 | Attribute #3 | Attribute #4 | +----+--------------+--------------+--------------+--------------+  Because of this diversity in the number of attributes (columns), I initially thought it would be a good idea to create one separate table for each Category, so I would avoid several NULLs and thus making a better use of the indexing. Although it looked great at first, I couldn’t find a way to create the relationship between the Items and the Categories through the database because, at least in my modest experience as a database administrator, when creating Foreign Keys, I inform explicitly a database the table name and column. In the end, I would like a solid structure to store all data, while having all the means to list all attributes of all Items a User may have with one query. I could hardcode dynamic queries with the server-side language, but I feel this is wrong and not very optimal. Additional information These are my responses to MDCCL comments:  1. How many Item Categories of interest are there in your business context, three (i.e., Cars, Boats and Planes) or more?  In fact, it’s very simple: There are only five Categories in total.  2. Will the same Item always belong to the same User (that is, once a given Item has been “assigned” to a certain User it cannot be changed)?  No, they could change. In the fictional scenario of the question, it would be like User A sells Item #1 for User B, so the ownership must be reflected.  3. Are there attributes that are shared by some or all of the Categories?  Not shared but, from memory, I can tell that at least three attributes are present in all Categories.  4. Is there a chance that the cardinality of the relationship between User and Item is many-to-many (M:N) instead of one-to-many (1:M)? For example, in the case of following business rules: A User owns zero-one-or-many Items and An Item is owned by one-to-many Users  No, because Items would describe a physical object. Users will have a virtual copy of them, each one identified by a unique   5. Regarding your following response to one of the question comments:  “In the fictional scenario of the question, it would be like User A sells Item #1 for User B, so the ownership must be reflected.”  It seems that you are planning to keep track of the item ownership evolution, so to speak. In this way, which attributes would you like to store about such phenomenon? Only the modification of the attribute that indicates the specific User who is the Owner of a specific Item?  No, not really. The ownership may change, but I don’t need to keep track of the previous Owner.
28397
I have these two CREATE TABLE statements:   CREATE TABLE GUEST (   id int(15) not null auto_increment PRIMARY KEY,   GuestName char(25) not null );  CREATE TABLE PAYMENT (   id int(15) not null auto_increment   Foreign Key(id) references GUEST(id),   BillNr int(15) not null );   What is the problem in the second statement? It did not create a new table.
91339
I am running the below query in ideone   CREATE TABLE AUTHOR ( AUTHOR_ID NUMBER(5) PRIMARY KEY, AUTHOR_NAME VARCHAR2(18) );  INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1234, 'MC GRAW HILL'); INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1235, 'J K ROLLING');     INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1236, 'WILLAM'); INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1237, 'SHAKESPEAR');  --------------------------------- CREATE TABLE BOOK  ( BOOK_ID NUMBER(5) PRIMARY KEY, BOOK_NAME VARCHAR2(15), AUTHOR_ID NUMBER(5), CONSTRAINT author_fk FOREIGN KEY (AUTHOR_ID) REFERENCES AUTHOR(AUTHOR_ID) ON DELETE CASCADE );  INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)      VALUES (10001, 'SOFTWARE DEVELOPEMENT', 1234); INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)      VALUES (10002, 'HARRY POTTER', 1235); INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)      VALUES (10003, 'TO BE OR NOT TO BE', 1237); INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)      VALUES (10004, 'PROJECT MANAGEMENT', 1234);   After creating these entries, when I try to delete the entries in AUTHOR Table by using following command:  DELETE FROM AUTHOR WHERE AUTHOR_ID = 1234;   Select * from book ;  10001|SOFTWARE DEVELOPEMENT|1234 10002|HARRY POTTER         |1235 10003|TO BE OR NOT TO BE   |1237 10004|PROJECT MANAGEMENT   |1234   and  Select * from author ;  1235|J K ROLLING 1236|WILLAM 1237|SHAKESPEAR   the entry from parent table is deleted but their is no delete cascade effect in child table (BOOK table). Can someone help me what I am doing wrong.  Thanks,
75313
I have database named "demo_dev" and added a table named "countries".  I wanted to create a bitmap index on a column, say, region as it has fewer unique values, using following query.   CREATE BITMAP INDEX ON countries (region);   It threw an error as shown below:     You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BITMAP INDEX ON countries (region)'   What shoud be done for it to run successfully?  I just refered the following link :   mysql version : 5.5
8186
is there an alternative for mysql_insert_id() php function for PostgreSQL? Most of the frameworks are solving the problem partially by finding the current value of the sequence used in the ID. However, there are times that the primary key is not a serial column....